Subject Re: [Firebird-general] pro&contra
Author Anderson Farias
Hi Ann,

> How are keys protected?

Well, as far as I can see, everything is kept inside the database and
protected by a password ether kept encrypted in the database or set by user
on attachment. Here's the process to use encryption:

1) the database, *must* use 'embeded authentication' (where users/passwords
are contained into your database, not on a separete/server wide admin.ib

2) database onwer *must* create a especial user SYSDSO (create user sysdso
set password '****') -- only the sysdso can manage database system password,
database encryption keys and grant users encryption rights

3) You have to connect using SYSDSO and create system encryption password
for the database (alter database ...) -- this step has an option called
'external' so that the password can be required on db attachement givem more
security (as explained in detail on datadef.pdf documentation.. some notes
at the end of this message)

4) Than, SYSDSO is able to create as many keys (using DES or AES) - -- those
are kept into system table rdb$encryptions

5) The SYSDSO grants permissions for db owner and other users on the key(s)
created for encryption

6) The db owner and/or users who have encryption rigths (given by SYSDSO)
than encrypts the entire database or just table colums as desired. And only
the user who encrypts has the power to give decrypt permission to other
users (only needed for column level encryption)

BTW, it uses OpenSSL 0.9.8g to handle all this encryption feature

-------- some notes from datadef.pdf guide ---------

"The string (SEP) can be up to 255 characters long and can include spaces.
The system encryption
password is encrypted with a key derived from machine specific information
and stored in
the database. This effectively node locks the database to the machine but
allows the
database to be attached without a user having to pass the system encryption
password in
plaintext. Thus, subsequent connections on the same machine need not provide
the SEP.

However, if the database file is copied and installed on a different
machine, the node-lock
feature disallows direct loading of the database without the user providing
the SEP. After
moving a database with a node-locked SEP to another machine, you must login
SYSDSO with the current SEP set via the SEP environment variable or DPB. The
a new SEP.
Just "setting" the SEP to connect to the database does not redefine or
re-node-lock the SEP.
Users can continue to provide the SEP externally though you may want to
alter the sep
command to re-node-lock it to the new machine."

"Using the external option when creating a SEP
Though an unauthorized person would not have decrypt permission for any
columns, he or she might be able to bit edit the database file to
artificially grant decrypt
permission. The password attribute of an encryption key can mitigate this
risk because the
user needs the passwords as well as decrypt permission. For database-level
encryption, the
data would be visible immediately because only the SEP is needed to see it.

Adding the external setting to a SEP statement can make it more difficult
for unauthorized
users to access an encrypted database on a mobile device such as a laptop
computer, or on
an a poorly secured desktop computer.

alter database set system encryption password <255-character string>

The external form of setting the SEP requires the first database attach to
pass the
isc_dbp_system_encrypt_password parameter with the value of the password, or
to set
the environment variable isc_system_encrypt_password. Subsequent database
attachments are not required to pass the SEP as the database server already
has it in
memory. The external form should only be used on remote connections when
Wire encryption has been configured for the network.

For security reasons, programs should not hardcode the SEP with
isc_dbp_system_encrypt_password but query the user, then generate this
attachment parameter dynamically. The ISC_SYSTEM_ENCRYPT_PASSWORD
variable should never be hardcoded in scripts and if entered at the console
should be unset
as soon as possible."

"Setting a user-defined password for an encryption key
As noted in Table 13.2, you can assign each column encryption key a custom
which adds an additional level of protection for your data. When you
associate a password
with a column encryption key, you must give it to the database owner or the
table owner so
that s/he can use the key to encrypt the column. You must also give it to
any end users who
need to change or view the values in the encrypted column.

If an encryption key was defined with a user-defined password, then users
must set the
password during a database session before accessing columns that have been
with the key:

set password 'user-password' for {encryption | column} encryption-ortable.

Assuming the same user also has decrypt and access permissions on the
column, he or she
can now access all columns encrypted by that key."