Library > Products > Pervasive.SQL > Pervasive.SQL 9.1 > Advanced Operations Guide > Pervasive.SQL Security > Security Tasks

PreviousAdvanced Operations Guide (9.1 revision 1) Next

Security Tasks

Show this topic in Library frames

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

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 Permissions Tasks

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

Encryption Tasks

See Data Encryption .

General Tasks

To start Pervasive.SQL Control Center

  1. Click Control Center from the Pervasive program on the Start menu.
  2. In Pervasive.SQL Explorer of PCC, expand the list of Engines (click the expand icon to the left of the node). In the list, locate the database engine that you wish to access.

    3. If you do not see the database engine you wish to access, you must register it with PCC. To do so, right-click Engines then click New 4 Server. Type the name of the database engine then click OK.

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

Securing a Database

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.SQL passwords, is case sensitive.

Caution
If you turn on security, be sure to specify a password with a significant length, at least five characters but no more than eight. Do not leave the password field blank because doing so creates a major security risk for your database. Note that passwords are case sensitive.

To turn on security using database properties

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

Note
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 a SQL statement

Note
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. Use an ODBC or JDBC application to connect to the desired database.

    2. You can also use SQL Editor in PCC to issue the SQL statement.

  2. Issue the SQL statement SET SECURITY= `password' where password is the text string you want to use as the password for the Master user.

    3. See also SET SECURITY in SQL Engine Reference.

To turn off security using database properties

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 a SQL statement

Note
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. Use an ODBC or JDBC application to connect to the desired database as Master.

    2. You can also use SQL Editor in PCC to issue the SQL statement.

  2. Issue the SQL statement SET SECURITY= NULL.

    3. 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.
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 Btrieve 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 Btrieve 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 Btrieve 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 with PCC

  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 with PCC

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

    For the Mixed security polity, the database engine passes the user name and password entered by the user to the operating system authentication service. See Mixed .

  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

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 with PCC .
  3. Create a new user as explained in To create a new user with PCC .
  4. Click for Group, then click the desired group in the list.
  5. Click Finish.

To create a new group or user with SQL statements

  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.
    Group Statement
    User Statement
    CREATE GROUP group-name
    where group-name is name of the group that you want to create.
    For example, to create the group `AcctGroup', type the following syntax:
    CREATE GROUP AcctGroup
    Note that group names are limited to a maximum of 30 bytes.
    See also CREATE GROUP in SQL Engine Reference.
    GRANT LOGIN TO myuser mypass
    where myuser is the name of the user that you wish to create, and mypass is the password for that user.
    For example, to create the user `john' with password `jb456', you would type the following syntax:
    GRANT LOGIN TO john jb456
    Note that user names are limited to a maximum of 30 bytes and passwords to a maximum of 8 bytes.
    See also GRANT in SQL Engine Reference.
  6. Click SQL 4 Execute in Text (or click in the icon bar).

Assigning Permissions Tasks

To assign permissions for a group

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 specific permissions for a user

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 applicable to all users

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 a user table privileges using SQL statements

  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 database in the list for which you want to create a group or user (click the plus (+) signs to expand the tree nodes).
  3. Click OK.
  4. In SQL Editor, create the desired statement for the user.

    5. See GRANT in SQL Engine Reference for the syntax and examples.

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

Chapter contents
Publication contents

Prev topic: Transactional Interface Security Quick Start
Next topic: Data Encryption