Administering Database Security
The Pervasive PSQL security option allows you to protect your data by limiting operations on some data columns to particular users. These limits may range from allowing a user to see only certain columns in a table, to allowing them to see all the columns in a table, but not update them. Pervasive PSQL makes no assumptions about database authorization based on the operating system’s file and directory rights. By default, all users accessing a database through Pervasive PSQL have complete read-write access to the data. You must enable and define database security to limit this access and protect the database from unauthorized update or access through Pervasive PSQL.
Pervasive PSQL security statements allow you to perform the following actions to limit access to your database:
•
Enable security for the database.
•
Identify users and groups of users and assign passwords to them.
•
Grant rights to users and user groups.
•
Revoke rights from users and user groups.
•
Disable security for the database.
•
Retrieve information about security defined for a database.
Understanding Database Rights
Table 47 shows the rights you can grant to users and user groups.
Table 47
Database Rights 
Right
Description
Login
Allows a user to log in to a database. You assign this right when you create a user and a password. The Login right does not give users access to data, however. You must assign other rights to users before they can access data. You cannot assign the Login right to a user group.
Create Table
Enables a user to create new table definitions. The user automatically has full access rights to the tables he or she creates at the time of creation, but the Master User can later revoke read, write, and alter rights for the table. The Create Table right is also referred to as a global right, because it applies to the entire data dictionary.
Select
Allows a user to query tables for information. You can grant the Select right for specific columns or for a whole table.
Update
Gives a user the right to update information in specified columns or tables. You can grant the Update right for specific columns or for a whole table.
Insert
Allows a user to add new rows to tables. You can grant the Insert right only at the table level.
Delete
Allows a user to delete information from tables. You can grant the Delete right only at the table level.
Alter
Allows a user to change the definition of a table. You can grant the Alter right only at the table level.
References
Allows a user to create foreign key references that refer to a table. The References right is necessary for defining referential constraints.
All
Includes Select, Update, Insert, Delete, Alter, and References rights.
You can assign certain types of rights over the whole database or for a particular database element. For example, when you assign the Update right to a user or user group, you can limit it to certain tables or to certain columns in tables. In contrast, when you assign the Create Table right to a user or user group, that user or user group has the Create Table right for the entire database. You cannot apply the Create Table right to a single table or column.
While the Create Table and Login rights apply to the entire database, all other rights apply to tables. In addition, you can apply Select and Update rights to individual columns in tables.
Establishing Database Security
The following nine steps describe the general procedure for establishing security for a database.
1
Log in to the database for which you want to establish security.
For more information about logging in to a database, refer to the Pervasive PSQL User's Guide.
2
Enable security for the database by creating the master user and specifying the master password with the SET SECURITY statement.
After you have enabled security as Master, the name of the master user is Master (case-sensitive), the password you specified when you enabled security becomes the master password (also case-sensitive). For more information, refer to Enabling Security.
3
Optional: Define a minimal set of rights for the PUBLIC group.
All users automatically belong to the PUBLIC group. For more information, refer to Granting Rights to the PUBLIC Group.
4
Optional: Create user groups with the CREATE GROUP statement.
You can create as many groups as you need for your system. However, a user can belong to only one group other than PUBLIC. For more information, refer to Creating User Groups.
5
Optional: Grant rights to each user group with the GRANT CREATETAB and GRANT (access rights) statements. For more information, refer to Granting Rights to User Groups.
6
Grant login privileges to users by specifying the users’ names and passwords with the GRANT LOGIN statement, and if you choose, assign each user to a user group. For more information, refer to Creating Users.
7
Grant rights to the users you have created who are not members of a user group using the GRANT CREATETAB and GRANT (access rights) statements. For more information, refer to Granting Rights to Users.
8
Optional: To protect your files from unauthorized Btrieve access, make the database a bound database. For more information about bound databases, refer to Understanding Database Rights.
Enabling Security
You can use a SET SECURITY statement to enable security. In response, Pervasive PSQL creates the master user, who has complete read-write access to the database. The password you specify with a SET SECURITY statement becomes the master password for the database.
The following example enables security for a database and specifies the password for the master user as Secure:
SET SECURITY = Secure;
Passwords are case-sensitive.
When you enable security, Pervasive PSQL creates the system tables X$User and X$Rights. Enabling security excludes all users except the master user from accessing the database until you explicitly create other users and grant them login rights.
Creating User Groups and Users
After you enable security, your database has one user (Master) and one user group (PUBLIC). To provide other users access to the database, log in to the database as the master user and create users by name and password. You can also organize the users in user groups.
User names are case-sensitive in Pervasive PSQL. Therefore, when you log in as the master user, you must specify the user name as Master.
Creating User Groups
To simplify security administration, you can organize users in user groups. You can create as many user groups as you need for your system. A user, however, can belong to only one group in addition to PUBLIC. Once the user is in an additional group, the user inherits the rights of that group, and you cannot grant individual rights to that user. The rights of a user in a group cannot differ from the rights defined for the entire group. To give a user unique rights, create a special group just for that user.
To create a user group, use a CREATE GROUP statement.
CREATE GROUP Accounting;
You can also create multiple user groups at once.
CREATE GROUP Accounting, Registrar, Payroll;
User group names are case-sensitive, cannot exceed 30 characters, and must be unique to the database. For more information about rules for naming user groups, refer to the Btrieve API Guide.
Creating Users
When you create a user for a database, Pervasive PSQL enters the corresponding user name and password into the database’s security tables. To create a user, use a GRANT LOGIN TO statement. The following example creates the user Cathy and assigns Passwd as her password.
GRANT LOGIN TO Cathy:Passwd;
*Note: Pervasive PSQL stores passwords in encrypted form. Therefore, you cannot query the X$User table to view user passwords.
You can also assign a user to a user group when you create the user. For example, to assign the user Cathy to the Accounting group, use the following statement:
GRANT LOGIN TO Cathy : Passwd
IN GROUP Accounting;
User names and passwords are case-sensitive. See Identifier Restrictions by Identifier Type in Advanced Operations Guide for permissible lengths and characters pertaining to user names and passwords.
Granting Rights
This section explains how to grant rights to user groups and individual users.
Granting Rights to the PUBLIC Group
All users automatically belong to the PUBLIC group, a special user group used to define the minimum set of rights for all users of a particular database. No user can have fewer rights than those assigned to the PUBLIC group. You cannot drop a user from the PUBLIC group, and you cannot revoke rights from a user if those rights are granted to the PUBLIC group.
By default, the PUBLIC group has no rights. To change the rights of the PUBLIC group, use a GRANT (access rights) statement. For example, the following statement allows all users of the sample database to query the Department, Course, and Class tables in the database:
GRANT SELECT ON Department, Course, Class
TO PUBLIC;
After granting rights to the PUBLIC group, you can create other groups to define higher levels of access. You can also give individual users additional rights that differ from any other user or group, provided the user is not part of a group.
Granting Rights to User Groups
You can assign rights to a user group and add user names and passwords to the group. Doing so eliminates assigning each user’s rights individually. Also, security is easier to maintain if you assign security rights to groups, since you can change the rights of many users by granting new rights or revoking existing rights for an entire group at once.
To grant rights to a user group, use a GRANT (access rights) statement. For example, the following statement allows all users in the Accounting group to alter the Billing table definition in the sample database.
GRANT ALTER ON Billing TO Accounting;
*Note: Remember that granting the Alter right implicitly grants the rights Select, Update, Insert, and Delete.
Granting Rights to Users
After you create a user, that user can log in to the database. However, the user cannot access data until you either place the user in a user group with rights or grant rights to the user.
To grant rights to a user, use a GRANT (access rights) statement. The following example allows the user John to insert rows into the billing table in the sample database.
GRANT INSERT ON Billing
TO John;
*Note: Granting the Insert right implicitly grants the rights Select, Update, and Delete.
Dropping Users and User Groups
To drop (delete) a user, use a REVOKE LOGIN statement.
REVOKE LOGIN FROM Bill;
This statement removes the user Bill from the data dictionary. After you drop a user, the user cannot access any tables in the database unless you disable security for the database.
You can also drop multiple users at once, as in the following example.
REVOKE LOGIN FROM Bill, Cathy, Susan;
To drop a user group, follow these steps:
1
Drop all users from the group, as in the following example:
REVOKE LOGIN FROM Cathy, John, Susan;
2
Use a DROP GROUP statement to drop the group. The following example drops the Accounting group:
DROP GROUP Accounting;
Revoking Rights
To revoke a user’s rights, use the REVOKE statement. The following example revokes the user Ron’s Select rights from the Billing table of the sample database.
REVOKE SELECT
ON Billing
FROM Ron;
Disabling Security
To disable security for a database, follow these steps:
1
Log in to the database as the master user.
2
Issue a SET SECURITY statement, specifying the NULL keyword, as follows:
SET SECURITY = NULL;
When you disable security for a database, Pervasive PSQL removes the X$User and X$Rights system tables from the database and deletes the associated .DDF files.
*Note: You cannot disable security simply by deleting the USER.DDF and RIGHTS.DDF data dictionary files. If you delete these and try to access the database, Pervasive PSQL returns an error and denies access to the database.
Retrieving Information about Database Security
When you set up database security, Pervasive PSQL creates the system tables X$User and X$Rights. Because the system tables are part of the database, you can query them if you have the appropriate rights.
For a complete reference to the contents of each system table, refer to the following topic in SQL Engine Reference: System Tables.