PSQL provides security both at the relational database level for SQL-based applications and at the file level for Btrieve applications that call the MicroKernel engine. Data access can be secured by authenticating users through the operating system, through an Active Directory account, or as a PSQL database user. Once user identity is verified, user rights are authorized by operating system or Active Directory rights groups or by PSQL database rights defined at the user or group level.

The following topics explain these security models and how to work with them, followed by a topic covering PSQL data file encryption capabilities.

PSQL offers SQL applications the authentication and authorization configurations shown in the following table.

By default, database security is disabled when you create a new database. You enable it by entering a password for its Master user, which is created when security is turned on. For information about allowed passwords, see Identifier Restrictions by Identifier Type.

After enabling security for a database, you must create any users or groups needed to configure the permissions for various database activities. For local database security, you can assign permissions directly to users, or you can assign permissions at the group level and then assign users to a group. For Windows domain security, you create only groups, using the same names as rights groups in which network users are members. You can manage users and groups for each database using PCC or SQL scripting.

You can also configure permissions in the built-in PUBLIC group, which applies to all users connected to the database. Permissions for any user are a combination of either user or group permissions and those in the PUBLIC group. The PUBLIC group in a new database has no permissions set and does not need any settings if all permissions are managed at the group or individual user level.

The following topics explain these concepts in more detail:

Enabling PSQL database security creates a user named Master, who has full access to the database. The Master user requires a password, which you set when you enable security. Master passwords can differ among databases and are not connected. To make changes to the security configuration for a database, you must provide the Master password, so be sure to remember it.

After you authenticate yourself as Master for a database, you can use PCC to create users for that database, create groups to assign them to, and manage data access permissions at both the group and user level. You can also execute SQL statements to create groups and then users within those groups. A user can be a member of only one of the groups you create. All users automatically become members of the PUBLIC special group in each database.

If you want to grant the same permissions to all users, you can grant them to a special group named PUBLIC. The database engine automatically creates the special group PUBLIC when you turn on security. Initially, no permissions are assigned to PUBLIC. Until you assign PUBLIC permissions or create users and groups with their own permissions, access to PSQL data is blocked.

PUBLIC is a special group that provides default permissions for all users. Even if you assign a user to another PSQL group, that user remains a member of PUBLIC. A couple of examples help to clarify how PUBLIC permissions apply. Suppose in PCC that you assign the CREATE TABLE permission to PUBLIC. You then create a user named myuser whose permissions in PCC do not include individual rights to create a table. Myuser can create a table because the database engine first checks default permissions in PUBLIC, and PUBLIC, in this case, grants rights to create a table.

Conversely, if a permission is not granted to PUBLIC, then the permission granted to the individual user or group applies. For example, suppose in PCC that you do not assign the CREATE TABLE permission to PUBLIC. No user can create a table unless the permissions for the user, or the group to which the user belongs, allow creating a table.

After you enable local database authentication, you can manage permissions for users and groups for the database. For Windows domain authentication, you can manage only group permissions, since user membership is assigned in Active Directory. PSQL Explorer in PCC displays the users and groups you can work with in either case. The following table lists the general rules that apply to users and groups.

For more information, see User and Group Tasks in PSQL User's Guide.

If you manage Relational Engine security through SQL scripting, the following topics in SQL Engine Reference provide useful information:

A SQL application may access data from more than one database in a single connection if the databases are on the same machine. However, since you can be logged in to only one database at a time, your access to another database where you are not logged in depends on the security settings of both databases.

PSQL offers Btrieve applications the authentication and authorization configurations shown in the following table.

The following topics explain these configurations in more detail:

These topics apply only to applications that directly call the MicroKernel Engine. The terms credentials, login credentials, or user credentials refer to a valid user name and password.

PSQL database authentication and authorization for the MicroKernel Engine can be configured to be dependent on or independent of the operating system. You can allow Btrieve users access to the database without allowing them operating system access to the data files.

Classic security is the Btrieve security model provided in all releases of the database. For Btrieve users, authentication is performed by the operating system, and data access privileges are determined by file system rights for the given user. Users have the same kinds of rights to access a file through Btrieve that they have for other types of files controlled by the operating system.

Btrieve owner names can add another layer of security by restricting access to individual files. For more information, see Owner Names.

Under Classic security, you set up application users and access permissions simply by creating operating system users and assigning permissions to files and directories. You do not need to take other actions.

Mixed Btrieve Security provides access to Btrieve data files without giving the user operating system file permissions. You can use this option in environments where you are strengthening security and are unable to modify your Btrieve application to use API login or a URI to specify a file.

In the Mixed security model, when a Btrieve open request occurs, the database engine authenticates the operating system user against the users in a special database called DefaultDB. If the operating system finds the user, then the database engine uses the rights table for the database to determine the user’s access to the file to be opened. Thus, permissions for users must be defined within the database they need to use. The database engine enforces its permissions regardless of operating system permissions.

Database authorization for Btrieve applications is provided by extending the Relational Engine security model so that it also can be used for Btrieve applications. The ability to create and define users and groups and set their permissions is provided in PCC and through SQL statements such as GRANT, REVOKE, ALTER GROUP, CREATE USER, ALTER USER, and DROP USER.

Under the Mixed security model, local database authentication and Windows domain authentication differ slightly. For local database authentication, any user names defined in the DefaultDB database must be the same as those defined in the operating system. During a Btrieve file create or open operation, the database engine passes the entered user name and password to the operating system for authentication. If the operating system authenticates the credentials, then the database uses the user’s individual permissions or the permissions of his or her assigned group. Instead of defining permissions for each user or group, you can define them once using the PUBLIC group.

Under Mixed security with Windows domain authentication, group names in Active Directory must be the same as groups defined in the database, but user membership is handled only in Active Directory. During a Btrieve file create or open operation, the database engine passes the user name and password entered by the user to the network for authentication. If the network authenticates the credentials, then the database uses the user’s group assignment to determine permissions. You do not need to add users to the database, and in fact, the Users node in PCC is no longer displayed. As an alternative to defining permissions for various groups, you can define them once using the PUBLIC group.

For Mixed security setup procedures, see Setting Up Mixed or Database Btrieve Security.

Since the Workgroup engine performs no operating system authentication, the behavior of the Classic and Mixed security policies using the Workgroup engine are the same. If you wish to secure a Btrieve database using the Workgroup engine, you must choose the Database security policy and set up any needed users and groups.

Database security for Btrieve files requires applications to issue a Btrieve login operation or use a Btrieve URI to specify the file in an Open operation. In either case, a database is referenced in the login or URI. This database provides the permissions for the specified user. Under Btrieve database security, the PSQL database authenticates and authorizes users of Btrieve data files either based on users defined locally in the PSQL database or based on network logins defined in Windows Active Directory where users are members of rights groups with the same names as groups in the PSQL database. With the one exception noted below, the ability of users to connect to and access data is unrelated to file system permissions as long as they can successfully log in to the system where the needed application runs. The ability to define database users and groups and set permissions is provided in PCC and through SQL statements such as GRANT and REVOKE.

You can use the Mixed or Database security models only with Btrieve data files that reside in the directories defined to belong to a given database, including the default database DefaultDB described in The Default Database and the Current Database. Data files residing in directories that have not been associated with a database cannot be accessed.

One of the primary advantages of these security models is the ability to restrict direct user access to the data files while still allowing full access to the data through the database engine. In contrast, under the Classic model, any user permitted to add records to the data file must necessarily also have the ability to copy or delete the data file from the operating system.

Migrating to mixed or database security requires that you make a number of choices and plan carefully. In a well-established environment, you may have to plan how your Btrieve files will be grouped together into databases, and schedule the migration so that you do not disrupt your production environment.

For the procedures for moving from the default Classic to Mixed or Database security, see Security Tasks in PSQL User's Guide.

An owner name is a string of bytes used to restrict access to a Btrieve data file. You can think of an owner name as a password used to unlock access to an individual file. In addition, if PSQL encrypts a file, the owner name serves as the private encryption key. Owner names reside in the file header and are always encrypted, whether or not the file is encrypted. As with most passwords, owner names are case-sensitive.

The owner name string must meet the following requirements:

If a data file has a long owner name, the following things apply:

The following topics provide more information:

You can set or clear an owner name on a file in the Maintenance and Function Executor tools. You cannot use SQL scripts to set or manage an owner name for a table, but SET OWNER and GRANT statements can be used to provide an owner name when it is required.

Depending on the options selected when an owner name is assigned, a user can access the file in the ways shown in the following table.

If you have a Btrieve owner name set on a file serving as a table in a SQL database, then you can access the table in two ways, depending on whether security is enabled for the database. With no security, the SET OWNER statement provides the owner name before any other attempt to access the table. With security, the Master user of the database must provide the owner name in a GRANT statement to issue permissions for the table to any user, including the Master user itself.

If a file with an owner name is read-only in the operating system, the Master user automatically has SELECT rights on this table without specifically granting itself SELECT rights with the owner name. In this case, other users do not have this automatic access, but the Master user can grant SELECT permission without providing the owner name.

When you first set an owner name with encryption on a file, the database engine immediately begins to encrypt the entire file. The larger the file, the longer encryption takes.

Data access operations on an encrypted file are slower than for an unencrypted file. The database engine must decrypt each page as it reads it from disk, and then encrypt it again before writing it back to disk.

For examples of granting access to files with Btrieve owner names, see GRANT in SQL Engine Reference.

In every new database, by default the Btrieve security setting is Classic. That is, the database engine authenticates through the operating system and authorization is based on directory and file permissions. Any user who can access a data file through the operating system will have the same level of permission to access the data records contained within the file, unless you are using Btrieve owner names to restrict access.

The following topics describe the steps to set up the default database, authorized users, and other aspects of the Btrieve security policies:

There are three security options available to you. The following table describes the features of these options to help you choose the best one. Encryption is optional in every configuration.

Under Btrieve Database security plus Relational local database security, database user accounts are completely unrelated to operating system user accounts.

In contrast, under Btrieve Classic security, a user who successfully logs into the computer has access to the database contents, at whatever level of file system rights that the user has been assigned to the file that contains the data.

Lastly, the Btrieve Mixed security policy has aspects of both of the other policies. Under this scheme, users log in using their operating system user names and passwords, but then the users access rights to the data are governed by user or group permissions set up in the secured database.

The following topics capture some of the major reasons you might choose one security policy over another.

Setting up security for the MicroKernel Engine is a simple process, but it affords enough flexibility that some preparation is necessary. This section describes the information you should know before you begin to set up Btrieve security.

If your applications have been or can be changed to use Btrieve login operations or open files with URIs, then you can choose either Database or Mixed Btrieve security and in both cases the database to be secured is registered in PCC. If your applications do not use Btrieve logins or URIs, then you can manage access by choosing Mixed and configuring the DefaultDB database.

For Mixed or Database security, you must either assign all users the same level of permissions, or create a set of defined users for each database.

In some cases where your Btrieve data files encompass two or more completely unrelated bodies of data, you may want to set up two or more separate databases, each with its own set of authorized users. Generally speaking, however, you want to minimize the number of separate databases so that you do not have to create and maintain multiple sets of defined users. Often, a single database is sufficient. User permissions within the database will allow you to regulate each user’s access to the database, so you do not need to create separate databases just to limit certain users’ access.

If you determine that you need only one database, you may use the preexisting database, DefaultDB, as the database associated with your Btrieve files. You may also set up your own named database instead.

You associate a Btrieve data file with a database by specifying the directory containing the data file as a Data Directory for the given named database. Thus, you need to know the directories containing all the data files that you want to associate with the database. If all the data files reside in a subdirectory of a specific directory, all you need to know is the top-level directory path name. You can even use "C:\" if you wish to include all data files on the hard drive.

If you plan to use Mixed security with local database authentication, you must either assign all users the same permissions, or set up user accounts for the users whose rights differ. If you are going to set up individual users, you must have a list of the operating system user names that you want to make into database user names. The database user names must match their operating system counterparts exactly.

If you plan to use Mixed security with Windows domain authentication, you must either assign all users the same permissions in the PUBLIC group or set up different PSQL groups for users whose rights differ. In both cases, you must have a list of the network group names that you want to duplicate as database group names. The database group names must match their network counterparts.

Before you set up security, you must know what policy you plan to use. The setup process varies somewhat for each policy. Considerations in choosing a policy are presented in Choosing Your Policy.

The following high-level steps outline how to set up security for a database. More detailed instructions are provided in MicroKernel Engine Security Quick Start.

The following table illustrates the basic level of effort required using the different security models. The tasks required to implement the security models, see Security Tasks in PSQL User's Guide.

This section provides step-by-step instructions on the fastest, easiest way to secure your Btrieve data files in the operating system while still allowing database users to access the data.

When this procedure is complete, you can revoke operating system user rights to the data files without affecting database user rights to access the data through an application.

PSQL supports the encrypting of network traffic between PSQL and the applications that call it. This type of encryption is often called wire encryption because it protects the data when it is traveling on the network wire, or on any network infrastructure, including wireless. While the use of wire encryption is not required, it provides additional deterrence against unauthorized access to the data transmitted by your application.

PSQL wire encryption is not tied to any particular security model. All PSQL security configurations can be used with or without turning on wire encryption. The rest of this topic covers the following:

Two configuration settings are associated with wire encryption. The settings must be configured for each client machine as well as for the server. For more information on these settings, see the following:

To perform data encryption before data passes over the network, PSQL uses Blowfish, a well-known and time-tested public domain algorithm, implementing its 40-, 56-, and 128-bit keys. Encryption using a 40-bit key provides the least amount of protection for the data. Encryption using a 56- or a 128-bit key is progressively more difficult to compromise.

As with all security using encryption, the greater the deterrence, the slower the performance, since some amount of processor time is needed to perform encryption and decryption.

Earlier versions of PSQL that did not support wire encryption are unable to communicate with a client or server from a later release that provides encryption. Any client or server that does not support encryption will return an error if it attempts to connect to a client or server that is using encryption.

Before turning on the encryption settings in your environment, first think about your encryption needs. You can select from four possible schemes for your encryption environment, depending on your situation:

First of all, consider whether your data has characteristics that would favor encryption. Is your data confidential or proprietary? Is it valuable in the hands of unauthorized users? Can it be used to harm your organization? If you answer no to these question and others like them, then your data may not need to be encrypted at all. Under these circumstances, there may be no reason to incur the performance trade-off that encryption requires. If you aren’t sure, talk to a data security expert.

Assuming your data does need to be protected, you still may not need encryption. If your applications run solely on a LAN, and you are comfortable with the existing security of your network, encryption may not provide any additional benefit.

Now suppose that you have one major customer at a remote site that has a connection to your database. You may wish to use encryption only for the communications that go to/from that remote client. You can achieve this affect by setting Wire Encryption at the remote client to Always and setting the server values accessed by that remote client to If Needed. All your internal clients would be set to Never. Thus, the servers will only use encryption when communicating with the remote client that requires encryption.

Now, suppose the situation is reversed and your environment includes one or more remote servers that are accessed by network infrastructure that you do not trust 100%. In this case, you can set those server values to Always, and set the local client values to If Needed. The result is encrypted communications only to those remote servers that require it.

Finally, if your PSQL applications often run over WAN, VPN, or other external networks that you do not trust 100%, then you may wish to encrypt 100% of your database communications. In this scenario, you would set Wire Encryption to Always at all clients and servers.

Once you have decided which clients and servers require encrypted communications, you must decide what level of deterrence is appropriate for your needs.

While Actian Corporation cannot offer advice regarding the encryption level that meets your specific needs, we can provide some guidelines to help inform your discussions with an appropriate data security expert. These guidelines do not represent a guarantee or warranty from Actian Corporation that no third party will be able to intercept and/or decode your encrypted data. As with any encryption scheme, there is no such thing as an “unbreakable” code, only varying levels of difficulty to compromise different types of encryption. The 128-bit encryption used by PSQL would be considered very difficult to decode using techniques and equipment available to a highly sophisticated individual hacker.

Consider using this level of encryption in cases where your data has limited ability to harm your organization or your customers if it falls into the wrong hands. Another reason to consider a Low level of encryption is if you wish simply to prevent a casual observer on your network from being able to read your data as it travels over the wires.

Consider using this level of encryption in situations where you believe you need somewhat more protection than against just a casual observer, but you do not believe you require the strongest level of security.

Consider using this level of encryption in situations where your data contains very sensitive information such as credit card numbers, social security numbers, financial account numbers, or other information protected by law. Especially consider this level of encryption if your database is associated with an entity on the network that is well-known to contain sensitive data, such as an Internet shopping web site or an online securities brokerage web site. Consider this level of encryption if your organization has previously suffered attempts to compromise its data security.

Using encryption reduces client-server performance. With encryption turned on, each piece of data must be encoded at the source and decoded at the destination. This process requires additional CPU cycles when compared to the same operations performed without encryption. The level of encryption should not affect the performance. The performance drop in using encryption is roughly the same no matter which of the three encryption levels you choose.

PSQL also offers encryption of a data file when it is written to disk. To use this feature, you must set an owner name for the file and choose the encryption option. For more information, see Owner Names.