This chapter provides a brief overview of basic Blackfish SQL security features and the SQL commands you can use to implement them. For a complete description of the syntax, use, and examples for a specific command, see the SQL Reference or the Stored Procedures Reference.
Blackfish SQL provides the following built-in security features:
User authentication restricts access to a Blackfish SQL database to authorized users only. Users must log into the database using an authorized user account and password. Permissions can be granted to or revoked from an account to fine tune access. In general, full access is reserved for the Administrator account(s), and a more restricted account or accounts are provided for general users.
By default, Blackfish SQL has one built-in Administrator account, sysdba/masterkey. You can secure a database by changing the password for the sysdba account and restricting use of that account to database administrators only. You can then create a user account with limited access rights to be used for general access. You can also create additional Administrator accounts, which may or may not be granted database startup privileges.
The following section describes how to create and modify user accounts.
You can use the following SQL statements to add, delete, and modify user accounts:
CREATE <userid> PASSWORD <password>
<userid> is the account to be added
<password> is the password for this account
DROP <userid> [ CASCADE|RESTRICT ]
<userid> is the account to be removed.
CASCADE deletes the user and all objects that the user owns.
RESTRICT causes the statement to fail if the user owns any objects, such as tables, views, or methods.
(no option) causes the statement to fail if the user owns any objects, such as tables, views, or methods.
ALTER USER <userid> SET PASSWORD "<password>";
<userid> is the account for which the password should be changed.
<password> is the new password.
There are several database access privileges which you can grant to or revoke from an account The following section describes the set of access privileges, and how to grant and revoke privileges for an account.
You can use the GRANT and REVOKE statements to change the access privileges for one or more user accounts. You can grant or revoke access to specific database resources or specific objects in the database. In addition, you can grant specific privileges to named roles, and you can then grant or revoke these roles for specific users.
To grant or revoke a privilege for an account, use the following SQL commands:
GRANT <role>|<privilege> TO <userid>
Grants the specified privilege or role to the specified user account.
REVOKE <role>|<privilege> FROM <userid>
Revokes the specified privilege or role from the specified user account.
<userid> is the account to be modified.
<role> is the user role to be granted or revoked, such as ADMIN. This can be a single role, or a comma-separated list of roles.
<privilege> is the privilege to be granted or revoked. This can be a single privilege or a comma-separated list of privileges, and can be one or more of the following:
Only a user with Administrator privileges can encrypt a database. When a database is encrypted, the STARTUP privilege is automatically revoked for all users (including Administrators) other than the Administrator issuing the encryption command. Consequently, after encrypting you must use the same Administrator account to restart the database. You can reassign STARTUP privileges to other users after the database has been encrypted and restarted.
You can use the built-in stored procedure DB_ADMIN.ENCRYPT() to encrypt a new or empty Blackfish SQL database. For instructions on encrypting a non-empty database, see Encrypting a Database with Existing Content
To encrypt a new database, log in from an Administrator account, and issue the following SQL command:
DB_ADMIN.ENCRYPT() is the built-in stored procedure for encrypting a database.
<AdminPassword> is the password for the user issuing the encryption command.
<EncryptionSeed> is a 16 character seed value.
For Blackfish SQL for Java:
To encrypt a Blackfish SQL for Java database that has existing tables, use the JBuilder utility, JdsExplorer. For instructions, see the JBuilder online help for JdsExplorer.
For Blackfish SQL for Windows:
To encrypt a database with existing content, do the following:
For additional information, see the Stored Procedures Reference.
In this discussion, an opponent is someone who is trying to break the Blackfish SQL security system.
The authentication and authorization support is secure for server-side applications where opponents do not have access to the physical Blackfish SQL database files. The SYS.USERS table stores passwords, user IDs, and rights in encrypted form. The table also stores the user ID and rights in an unencrypted column, but this is for display purposes only. The encrypted values for user ID and rights are used for security enforcement.
The stored passwords are encrypted using a strong TwoFish block cipher. A pseudo-random number generator is used to salt the encryption of the password. This makes traditional password dictionary attacks much more difficult. In a dictionary attack, the opponent makes guesses until the password is guessed. This process is easier if the the opponent has personal information about the user, and the user has chosen an obvious password. There is no substitution for a well chosen (obscure) password as a defense against password dictionary attacks. When an incorrect password is entered, the current thread sleeps for 500 milliseconds.
If a Blackfish SQL database is unencrypted, it is important to restrict physical access to the file, for the following reasons:
For environments where a dangerous opponent may gain access to physical copies of a Blackfish SQL database, the database and log files should be encrypted, in addition to being password protected. WARNING: The cryptographic techniques that Blackfish SQL uses to encrypt data blocks are state-of-the-art. The TwoFish block cipher used by Blackfish SQL has never been defeated. This means that if you forget your password for an encrypted Blackfish SQL database, you will not be able to access the database. The best chance of recovering the data would be to have someone guess the password.
There are measures that can be used to guard against forgetting a password for an encrypted database. It is important to note that there is a master password used internally to encrypt data blocks. Any user that has STARTUP rights has the master password encrypted using their password in the SYS.USERS table. This allows one or more users to open a database that has been shut down, because their password can be used to decrypt a copy of the master password. This feature can be used to create a new database that has one secret user who has Administrator privileges (which includes STARTUP rights). If you use this virgin database whenever a new empty database is needed, you will always have one secret user who can unlock the encryption.
Encrypting a database has some effect on performance. Data blocks are encrypted when they are written from the Blackfish SQL cache to the Blackfish SQL database and are decrypted when they are read from the Blackfish SQL database into the Blackfish SQL cache. So the cost of encryption is only incurred when file I/O is performed.
Blackfish SQL encrypts all but the first 16 bytes of .jds file data blocks. There is no user data in the first 16 bytes of a data block. Some blocks are not encrypted. This includes allocation bitmap blocks, the header block, log anchor blocks and the SYS.USERS table blocks. Note that the sensitive fields in the SYS.USERS table are encrypted using the user's password. Log file blocks are completely encrypted. Log anchor and status log files are not encrypted. The temporary database used by the query engine is encrypted. Sort files used by large merge sorts are not encrypted, but they are deleted after the sort completes.
NOTE: The remote client for Blackfish SQL currently uses sockets to communicate with a Blackfish SQL Server. This communication is not secure. Since the local client for Blackfish SQL is in-process, it is secure.
Copyright(C) 2009 Embarcadero Technologies, Inc. All Rights Reserved.
What do you think about this topic? Send feedback!