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>  

Where: 

<userid> is the account to be added  

<password> is the password for this account

DROP <userid> [ CASCADE|RESTRICT ]  

Where: 

<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>"; 

Where: 

<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. 

Where:  

<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:

• STARTUP confers the ability to open a database that is shut down. The user's password is required to add STARTUP rights to a user account. You can also specify STARTUP rights at the time the user account is created.
• ADMINISTRATOR confers the ability to add, remove, and change rights of users, and the ability to encrypt the database. Also includes the four stream rights: WRITE, CREATE, DROP, RENAME. By default, STARTUP rights are granted to an Administrator account when the account is created, but you can remove STARTUP rights from the account. You cannot remove WRITE, CREATE, DROP, or RENAME privileges from an Administrator account; attempts to remove these rights are ignored.
• WRITE confers the ability to write to file or table streams in the Blackfish SQL database.
• CREATE confers the ability to create new file or table streams in the Blackfish SQL database.
• DROP confers the ability to remove file or table streams from the Blackfish SQL database.
• RENAME confers the ability to rename file or table streams in the Blackfish SQL database.

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: 

CALL DB_ADMIN.ENCRYPT(<AdminPassword>,<EncryptionSeed>) 

Where: 

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:

1. Use RAD Studio DataExplorer to create a new database. For instructions, see the online help for DataExplorer.
2. Copy the existing users to the new database. DB_ADMIN.COPY_USERS(<OtherFilename>, <AdminUser>, <AdminPass>,<DoCopyEncryption>, <ReplaceExistingUsers>) Where: <OtherFilename> is the filename of the destination database. <AdminUser> is a user with ADMIN privileges in destination database. <AdminPass> is a password of the specified adminUser in destination database. If <DoCopyEncryption> is TRUE and the current database is encrypted, then encrypt the target database with the same key. If <ReplaceExistingUsers> is TRUE, then the existing users in the target database is replaced with the users in the source database.
3. Encrypt the new database. DB_ADMIN.ENCRYPT(<password>,<EncryptionSeed>)
4. Copy the contents of the old database into the newly encrypted database. DB_ADMIN.COPY_STREAMS(<OtherFilename>, <AdminUser>, <AdminPass>, <DoOverwrite>, <DoIgnoreErrors>) Where: <OtherFilename> is the filename of the destination database. <AdminUser> is a user with ADMIN privileges in destination database. <AdminPass> is a password of the specified adminUser in destination database. If <DoOverwrite> is TRUE, it allows tables to be overwritten. If FALSE, this would be an error. If <IgnoreErrors> is TRUE, then this method can be used to repair a corrupted database.

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:

• If a Blackfish SQL database file is not password protected, and it is possible for an opponent to write to it with a separate file editing utility or program, the authentication and authorization support can be disabled.
• If it is possible for an opponent to read a Blackfish SQL database file that is not encrypted with a separate file-editing program, the opponent might be able to reverse-engineer the file format and view its contents.

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.

Preface 

Overview 

System Architecture 

Establishing Connections 

Administering Blackfish SQL 

Using Stored Procedures and User Defined Functions 

Using Triggers in Blackfish SQL Tables 

Stored Procedures Reference 

SQL Reference 

Optimizing Blackfish SQL Applications 

Deploying Blackfish SQL Database Applications 

Troubleshooting