Library > Products > Pervasive PSQL > Pervasive PSQL v9 SP2 (9.5) > User's Guide > Using Pervasive PSQL Control Center > Groups, Users, and Security

PreviousUser's Guide (v9 SP2 (9.5) revision 1) Next

Groups, Users, and Security

Chapter contents

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 Pervasive 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 Pervasive 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
Btrieve Security Policy Tasks
Apply to security policies for the transactional interface
User and Group Tasks
Apply to creating users and groups
Assigning Permission Tasks
Apply to assigning permissions to users and groups
Encryption Tasks
Apply to data encryption

General Tasks

General tasks apply to the overall use of security.

Btrieve Security Policy Tasks

Btrieve security policy tasks apply to the transactional interface.

User and Group Tasks

User and group tasks apply to creating users and groups.

Assigning Permission Tasks

Assigning permissions tasks pertain to granting permissions for users and groups.

Encryption Tasks

See Data Encryption in Advanced Operations Guide.

General Tasks

To log into a database using PCC when you are already logged into that database as another user

Note
As the Master user, logging in as another user can aid you in testing the more restrictive permissions you have assigned this user.
  1. Right-click on the database name in the PCC Pervasive PSQL Explorer then click Logout (name).

    2. 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 on the database name.
  3. Click Login.
  4. Type the user name and password.
  5. Click OK.

To turn on security using Pervasive 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 login 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 PCC, expand the "Engines" node then the "Databases" node (click the expand icons to the left of each node).
  2. Right-click on 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.

    8. 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 login 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 4 SQL Document (or click in the icon bar).

    3. The Select Database dialog appears.

  3. Click the database in the list for which you want to create a group or user (click the expand icons to expand the tree nodes).
  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 4 Execute in Text (or click in the icon bar).

    7. See also SET SECURITY in SQL Engine Reference.

To turn off security using Pervasive 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 allow all operating system users to access the database through the relational and transactional interfaces 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 re-enabled, the previous user names, passwords, and permissions take effect again. (An exception is the Master user. The Master password is not retained nor re-applied.)
  1. In PCC, expand the "Engines" node then the "Databases" (click the expand icon to the left of the node).
  2. Right-click on the desired database then click Properties.
  3. Click Security in the Properties tree.
  4. Click the "Security" tab.
  5. Click Enable Security to remove the check mark on the option.
  6. Click OK.

    7. Database security is now off.

To turn off security using SQL

Caution
Turning off security allow all operating system users to access the database through the relational and transactional interfaces 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 re-enabled, the previous user names, passwords, and permissions take effect again. (An exception is the Master user. The Master password is not retained nor re-applied.)
  1. Turn security on for the database as explained in General Tasks .
  2. In the File menu of PCC, click New 4 SQL Document (or click in the icon bar).

    3. The Select Database dialog appears.

  3. Click the database in the list for which you want to create a group or user (click the expand icons to expand the tree nodes).
  4. Click OK.
  5. In SQL Editor, issue the SQL statement SET SECURITY= NULL.
  6. Click SQL 4 Execute in Text (or click in the icon bar).

    7. 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 PCC, expand the "Engines" node then the "Databases" (click the expand icon to the left of the node).
  3. Right-click on 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 Pervasive 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 pre-defined DefaultDB, with your Pervasive PSQL files

  1. In PCC, expand the "Engines" node then the "Databases" (click the expand icon to the left of the node).
  2. Right-click on the desired database then click Properties.
  3. Click Directories then click New.
  4. Type a path for the Pervasive PSQL files then click OK.

    5. 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 Pervasive 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 Pervasive PSQL Explorer

  1. Turn security on for the database as explained in General Tasks .
  2. Expand the nodes for the database (click the expand icon to the left of the node).
  3. Right-click on the Groups node then click New 4 Group.
  4. Type the name that you want for the group.

    5. A group name is limited to a maximum of 30 bytes.

  5. Click Finish.

To create a new user using Pervasive PSQL Explorer

  1. Turn security on for the database as explained in General Tasks .
  2. Expand the nodes for the database (click the expand icon to the left of the node).
  3. Right-click on the Users node then click New 4 User.
  4. Type the name that you want for the user.

    5. A user name is limited to a maximum of 30 bytes.

  5. Type a password for Password and re-type it for Confirm Password.

    6. 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 (?).

  6. Optionally, assign the user to a group.

    7. Click for Group, then click the desired group in the list.

    Note
    You can add a user to a group only when creating the new user. You cannot add an existing user to a group.
  7. Click Finish.

To assign a user to a group using Pervasive PSQL Explorer

Note
You can add a user to a group only when creating the new user. You cannot add an existing user to 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 Pervasive PSQL Explorer .
  3. Create a new user as explained in To create a new user using Pervasive PSQL Explorer .
  4. Click for Group, then click the desired group in the list.
  5. Click Finish.

To delete a group or user using Pervasive PSQL Explorer

  1. Expand the nodes for the database (click the expand icon to the left of the node).
  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 4 SQL Document (or click in the icon bar).

    3. The Select Database dialog appears.

  3. Click the database in the list for which you want to create a group or user (click the expand icons to expand the tree nodes).
  4. Click OK.
  5. In SQL Editor, create the desired statement for the group or user.

    6. Refer to the following statements in SQL Engine Reference:

  6. To execute the statement, click SQL 4 Execute in Text (or click in the icon bar).

Assigning Permissions Tasks

To assign permissions for a group using Pervasive PSQL Explorer

Note
Permissions on the "Database" tab override permissions on the "Table" tab.
  1. Expand the nodes for the desired database (click the expand icon to the left of each node to expand the node).
  2. Right-click on the group name under the Groups node then click Properties.
  3. Click Permissions in the Properties tree.
  4. Click the "Database" tab to access permissions that apply to all tables within the entire database. Click the "Table" tab to access permissions that apply to specific tables and specific columns within tables.
  5. On either tab, click the option for the desired permission.

    6. A check mark indicates that the permission applies.

  6. Click OK.

To assign permissions for a user using Pervasive 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 (click the expand icon to the left of each node to expand the node).
  2. Right-click on the user name under the Users node then click Properties.
  3. Click Permissions in the Properties tree.
  4. Click the "Database" tab to access permissions that apply to all tables within the entire database. Click the "Table" tab to access permissions that apply to specific tables and specific columns within tables.
  5. On either tab, click the option for the desired permission.

    6. A check mark indicates that the permission applies.

  6. Click OK.

To assign permissions to all users using Pervasive PSQL Explorer

Note
Permissions on the "Database" tab override permissions on the "Table" tab.
  1. Expand the nodes for the desired database (click the expand icon to the left of each node to expand the node).
  2. Right-click on the group PUBLIC under the Groups node then click Properties.
  3. Click Permissions in the Properties tree.
  4. Click the "Database" tab to access permissions that apply to all tables within the entire database. Click the "Table" tab to access permissions that apply to specific tables and specific columns within tables.
  5. On either tab, click the option for the desired permission.

    6. A check mark indicates that the permission applies.

  6. Click OK.

To assign permissions for a group or user using SQL

  1. In the File menu of PCC, click New 4 SQL Document (or click in the icon bar).

    2. The Select Database dialog appears.

  2. Click the desired database in the list (click the plus (+) signs to expand the tree nodes).
  3. Click OK.
  4. In SQL Editor, create the desired statement for the group or user.

    5. In SQL Engine Reference, see the following:

  5. Click SQL 4 Execute in Text (or click in the icon bar).

Chapter contents
Book contents

Prev topic: Triggers, Stored Procedures, User-defined Functions, and Views
Next topic: Configuration