Library > Products > Pervasive PSQL > Pervasive PSQL v10 > User's Guide > Using Pervasive PSQL Control Center > Groups, Users, and Security

PreviousUser's Guide (v10) 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 Permissions Tasks
Apply to assigning permissions to users and groups
Encryption Tasks
Apply to data encryption
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 the database name.
  3. Click Login.
  4. Type the user name and password, then 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 Pervasive PSQL Explorer, expand the Engines node, then the Databases 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 toolbar).

    3. 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 4 Execute in Text (or click in the toolbar).

    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 Pervasive PSQL Explorer, expand the Engines node, then the Databases 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 clear 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 PCC File menu, click New 4 SQL Document (or click in the toolbar).

    3. 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 4 Execute in Text (or click in the toolbar).

    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 Pervasive 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 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 Pervasive PSQL Explorer, expand the Engines node, then the Databases 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

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 4 Group.
  4. Type the name that you want for the group.
  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.
  3. Right-click on the Users node then click New 4 User.
  4. Type the name that you want for the user.
  5. Type a password for Password and re-type it for Confirm Password.

    6. 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.

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

  7. Click Finish.

To assign a user to a group using Pervasive 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 Pervasive PSQL Explorer .
  3. Right-click on 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 Pervasive 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 4 SQL Document (or click in the toolbar).

    3. 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.

    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 toolbar).

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.
  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.

    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.
  2. Right-click on 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.

    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.
  2. Right-click on 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.

    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 PCC File menu, click New 4 SQL Document (or click in the toolbar).

    2. 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.

    5. In SQL Engine Reference, see the following:

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

Chapter contents
Book contents

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