Groups, Users, and Security
Security is a database property that requires a user to provide a user name and password to access the database. By default, database security is turned off.
Database security can be turned on through PCC or by executing an SQL statement. Once enabled, you may create groups and users and assign permissions to them. Permissions can include database rights, table rights, and column rights within tables.
When you turn security on or off, the Master user must have only one connection open and must be the only user connected.
As soon as you turn security on for the first time, only the Master user can access the database. The Master user password, as with all PSQL passwords, is case sensitive.
*Caution: If you turn on security, be sure to specify a password with a significant length. Do not leave the password field blank because doing so creates a major security risk for your database.
See PSQL Security chapter in Advanced Operations Guide for additional information about security.
Security Tasks
This section contains step-by-step tasks pertaining to security. The tasks are divided into the following categories:
Category
Description
General Tasks
Orient you to the overall use of security
•
To log out from and log in to a database
•
To turn on security using PSQL Explorer
•
To turn on security using SQL
•
To turn off security using PSQL Explorer
•
To turn off security using SQL
Btrieve Security Policy Tasks
Apply to security policies for the MicroKernel Engine
•
To set or change the security policy for a database
•
To use an existing database, including the predefined DefaultDB, with your PSQL files
User and Group Tasks
Apply to creating users and groups
•
To create a new group using PSQL Explorer
•
To create a new user using PSQL Explorer
•
To assign a user to a group using PSQL Explorer
•
To delete a group or user using PSQL Explorer
•
To work with groups and users using SQL
Assigning Permissions Tasks
Apply to assigning permissions to users and groups
•
To assign permissions for a group using PSQL Explorer
•
To assign permissions for a user using PSQL Explorer
•
To assign permissions to all users using PSQL Explorer
•
To assign permissions for a group or user using SQL
Encryption Tasks
Apply to data encryption
See Data Encryption in Advanced Operations Guide.
General Tasks
►
To log out from and log in to a database
1
Right-click the database name in PCC PSQL Explorer, then click Logout (name).
Name reflects the name of the user currently logged in to the database. If the database does not have security enabled, name is Master. Name may also be Master if the current user is logged in as Master.
Any nodes expanded for the database are collapsed.
2
Right-click the database name.
3
Click Login.
4
Enter the user name and password, then click OK.
*Note: If you are the Master user, logging in as another user can help in testing the more restrictive permissions that you have assigned this user.
►
To turn on security using PSQL Explorer
If the database resides on a remote machine, you must provide a user name and password of an administrator or of a member of the Pervasive_Admin group for the remote machine. The user name and password is not required if the database resides on the local machine to which you are logged in (and the local machine is not running Terminal Services).
Turning on security prevents all users from accessing the database unless they log in to it using a valid database user name and password. User names and passwords cannot be set up until security is turned on, so the database will be inaccessible to each user for the period of time until you have set up a user account for that user.
1
In PSQL Explorer, expand the Engines node, then the Databases node.
2
Right-click the desired database, then click Properties.
3
Click Security in the Properties tree.
4
Click the Security tab.
5
Click Enable Security to check mark the option.
6
Type the password you want for Master Password, then re-type it for Confirm Password.
7
Click OK.
Database security is now on and you are logged in as the Master user. For instructions on creating database user accounts, see User and Group Tasks.
►
To turn on security using SQL
You must be logged into the computer as an administrator or as a member of the Pervasive_Admin operating system security group.
Turning on security prevents all users from accessing the database unless they log in to it using a valid database user name and password. User names and passwords cannot be set up until security is turned on, so the database will be inaccessible to each user for the period of time until you have set up a user account for that user.
1
Turn security on for the database as explained in General Tasks.
2
In the File menu of PCC, click New > SQL Document (or click in the toolbar).
The Select Database dialog box appears.
3
Click the database in the list for which you want to create a group or user.
4
Click OK.
5
In SQL Editor, issue the SQL statement SET SECURITY= ‘password’ where password is the text string you want to use as the password for the Master user.
6
Click SQL > Execute in Text (or click in the toolbar).
See also SET SECURITY in SQL Engine Reference.
►
To turn off security using PSQL Explorer
You must be logged into the computer as an administrator or as a member of the Pervasive_Admin operating system security group.
*Caution: Turning off security allows all operating system users to access the database if database security is Mixed or Database mode.

Database user names, passwords, and permissions are retained but not used if security is turn off. If security is reenabled, the previous user names, passwords, and permissions take effect again. (An exception is the Master user. The Master password is not retained or reapplied.)
1
In PSQL Explorer, expand the Engines node, then the Databases node.
2
Right-click the desired database then click Properties.
3
Click Security in the Properties tree.
4
Click the Security tab.
5
Click Enable Security to clear the option.
6
Click OK.
Database security is now off.
►
To turn off security using SQL
*Caution: Turning off security allows all operating system users to access the database if database security is Mixed or Database mode.

Database user names, passwords, and permissions are retained but not used if security is turn off. If security is reenabled, the previous user names, passwords, and permissions take effect again. (An exception is the Master user. The Master password is not retained or reapplied.)
1
Turn security on for the database as explained in General Tasks.
2
In the PCC File menu, click New > SQL Document (or click in the toolbar).
The Select Database dialog appears.
3
Click the database in the list for which you want to create a group or user.
4
Click OK.
5
In SQL Editor, issue the SQL statement SET SECURITY= NULL.
6
Click SQL > Execute in Text (or click in the toolbar).
See also SET SECURITY in SQL Engine Reference.
Btrieve Security Policy Tasks
►
To set or change the security policy for a database
*Caution: Changing security policy for a database may prevent current users from accessing the database, if security is turned on and the given users do not have equivalent user accounts and rights under the new security policy.
1
Turn security on for the database as explained in General Tasks.
2
In PSQL Explorer, expand the Engines node, then the Databases node.
3
Right-click the desired database then click Properties.
4
Click Security in the Properties tree.
5
Click the Btrieve Security tab.
6
Click the desired policy: Classic, Mixed, or Database.
7
Click OK.
See also the chapter PSQL Security in Advanced Operations Guide.
*Caution: If your database has security turned on and you change from Classic security policy to Mixed or Database, all users are prevented from accessing the database until you create database user accounts and privileges for them.
►
To use an existing database, including the predefined DefaultDB, with your PSQL files
1
In PSQL Explorer, expand the Engines node, then the Databases node.
2
Right-click the desired database then click Properties.
3
Click Directories then click New.
4
Type a path for the PSQL files then click OK.
If your files are spread over many directories, specify a high-level directory that they all have in common. You can specify a root level if necessary, but doing so includes in the database all PSQL+ files at the root level and its subordinate directories.
You do not need to enter every directory, just the lowest level directory that is common to all Btrieve files you want to include in the database.
5
Turn security on for the database as explained in General Tasks.
6
Set permissions for groups and users and explained in User and Group Tasks.
User and Group Tasks
►
To create a new group using PSQL Explorer
Note that you cannot add a group to another group.
1
Turn security on for the database as explained in General Tasks.
2
Expand the nodes for the database.
3
Right-click the Groups node then click New > Group.
4
Type the name that you want for the group.
5
Click Finish.
►
To create a new user using PSQL Explorer
1
Turn security on for the database as explained in General Tasks.
2
Expand the nodes for the database.
3
Right-click the Users node then click New > User.
4
Type the name that you want for the user.
5
Type a password for Password and re-type it for Confirm Password.
Passwords are case sensitive. For a list of database object lengths and invalid characters, see Identifier Restrictions by Identifier Type in Advanced Operations Guide..
6
Optionally, assign the user to a group.
Click for Group, then click the desired group in the list.
7
Click Finish.
►
To assign a user to a group using PSQL Explorer
Note that 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.
1
Turn security on for the database as explained in General Tasks.
2
If the desired group does not exist, create the group as explained in To create a new group using PSQL Explorer.
3
Right-click a user name under the Users node then click Properties.
4
Click General in the Properties tree.
5
Click for Group, then click the desired group in the list.
6
Click OK.
►
To delete a group or user using PSQL Explorer
Note that a group can be deleted only if no users are assigned to it.
1
Expand the nodes for the database.
2
Expand the Groups node or Users node.
3
Right-click the desired group or user name.
4
Click Delete.
5
Click Yes.
►
To work with groups and users using SQL
1
Turn security on for the database as explained in General Tasks.
2
In the File menu of PCC, click New > SQL Document (or click in the toolbar).
The Select Database dialog box appears.
3
Click the database in the list for which you want to create a group or user.
4
Click OK.
5
In SQL Editor, create the desired statement for the group or user.
Refer to the following statements in SQL Engine Reference:
•
CREATE GROUP
•
ALTER GROUP
•
DROP GROUP
•
CREATE USER
•
ALTER USER
•
DROP USER
•
GRANT
•
REVOKE
•
SET PASSWORD
6
To execute the statement, click SQL > Execute in Text (or click in the toolbar).
Assigning Permissions Tasks
►
To assign permissions for a group using PSQL Explorer
*Note: Permissions on the Database tab override permissions on the Table tab.
1
Expand the nodes for the desired database.
2
Right-click the group name under the Groups node then click Properties.
3
Click Permissions in the Properties tree.
4
Click the tab to access permissions for the desired object: database, tables (and columns), stored procedures, or views. See also Permissions on Views and Stored Procedures in SQL Engine Reference.
5
On the tab, click the option for the desired permission.
A check mark indicates that the permission applies.
6
Click OK.
►
To assign permissions for a user using PSQL Explorer
*Note: You cannot assign specific permissions to a user if the user is a member of a group. The permissions of the group apply to the user.

Permissions on the Database tab override permissions on the Table tab.
1
Expand the nodes for the desired database.
2
Right-click the user name under the Users node then click Properties.
3
Click Permissions in the Properties tree.
4
Click the tab to access permissions for the desired object: database, tables (and columns), stored procedures, or views. See also Permissions on Views and Stored Procedures in SQL Engine Reference.
5
On the tab, click the option for the desired permission.
A check mark indicates that the permission applies.
6
Click OK.
►
To assign permissions to all users using PSQL Explorer
*Note: Permissions on the Database tab override permissions on the Table tab.
1
Expand the nodes for the desired database.
2
Right-click the group PUBLIC under the Groups node then click Properties.
3
Click Permissions in the Properties tree.
4
Click the tab to access permissions for the desired object: database, tables (and columns), stored procedures, or views. See also Permissions on Views and Stored Procedures in SQL Engine Reference.
5
On the tab, click the option for the desired permission.
A check mark indicates that the permission applies.
6
Click OK.
►
To assign permissions for a group or user using SQL
1
In the PCC File menu, click New > SQL Document (or click in the toolbar).
The Select Database dialog box appears.
2
Expand the nodes for the desired database.
3
Click OK.
4
In SQL Editor, create the desired statement for the group or user.
In SQL Engine Reference, see the following:
•
GRANT
•
REVOKE
•
SET PASSWORD
5
Click SQL > Execute in Text (or click in the toolbar).