Database Security

By default, database security is turned off when you create a new database with Pervasive.SQL 9 SP1 (9.1). You turn it on by suppling a password for the "Master" user. Passwords are case sensitive and limited to a maximum of 8 bytes. You may use any displayable character in a password except for the semicolon (;) and the question mark (?).

Master User

Database security is based on the existence of a default user named "Master" who has full access to the database when security is turned on. By default, no password is set for the Master user. Security is enabled, or turned on, once you specify a password for the Master user.

The Master user can create groups and other users and define sets of data access permissions for these groups and users. You can add users and groups by executing GRANT and REVOKE SQL statements or by using Pervasive.SQL Control Center (PCC).

The PUBLIC Special Group

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.

PUBLIC is a special group because it provides default permissions for all users and groups. You cannot explicitly assign or remove a user from PUBLIC. The database engine always checks permissions assigned to PUBLIC first. A couple of examples help clarify how PUBLIC permissions apply.

Suppose in PCC that you assigns 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.

Users and Groups

After you turn on database security, you can then define groups and users. Nodes for Groups and Users appear in Pervasive.SQL Explorer in PCC.

Restrictions

A given user cannot be a member of more than one group.

All users in a group have exactly the permissions defined for that group. You cannot grant or revoke individual permissions for a user who is a member of a group.

If you wish to use groups, you must set up the groups before creating users. You cannot add a user to a group after you have already created the user. You cannot add an existing user to a group. To do so, you must delete the user and re-create the user as a member of the group.

You cannot add a group to another group.