Pervasive logo

Prev Advanced Operations Guide Next

Security Tasks

This section explains the basic concepts of Pervasive.SQL security. It is divided into the following topics:

General Tasks

Securing a Database Tasks

Btrieve Security Policy Tasks

User and Group Tasks

Assigning Permissions Tasks

For encryption tasks, see Data Encryption .

General Tasks

To start Pervasive Control Center

  1. Click Start and select Programs 4 Pervasive.SQL 4 Control Center.
  2. In the left-hand pane of PCC, double-click Pervasive.SQL Engines. In the list of database engines that appears, locate the database engine you wish to access.

Note
If you do not see the database engine you wish to access, you must register it with PCC. To do so, click the Action menu and choose Register New Engine. In the dialog that appears, specify or choose the computer where the desired database engine is located. Click OK when you are finished.

  1. Double-click the icon representing the database engine you wish to access. Continue by selecting one of the procedures detailed below.

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. Click on the machine name in the PCC namespace
  2. Click the F5 button (Refresh). This clears all open connections.
  3. Select the database again and you will be prompted to log in.

Securing a Database Tasks

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

A database can be secured in one of two ways

To secure a database using PCC

For information about how Btrieve owner names interact with relational security, see Owner Names and Security .

To secure a database using SQL statements

Note
You cannot use SQL Data Manager in PCC to issue a SET SECURITY statement. Attempting to do so generates an error because both SQL Data Manager and PCC keep an open connection to the database.

To turn security on for a Database

This procedure explains how to turn security on for a given database. You must be logged into the computer as an administrator or as a member of the Pervasive_Admin operating system security group.

Caution
Turning on security will prevent all users from accessing the database unless they login to it using a valid database username and password. Usernames 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, right-click on the database you wish to secure, and choose Properties.
  2. In the dialog that appears, click on the Security tab. Enter the password you wish to use for the Master user, and enter it again to confirm. Click OK.
  3. The database is now secure, and you are logged in as the Master user.

    For instructions on creating database user accounts, see User and Group Tasks .

To turn security off for a Database

This procedure explains how to turn security off for a given database. To turn security off, you must be logged into the database as the Master user.

Caution
Turning off security will allow all operating system users to access the database. Turning off security also erases all database user accounts and privileges. If you turn security back on, you must re-create all users and privileges.

  1. In PCC, right-click on the database you wish to make unsecure, and choose Properties.
  2. In the dialog that appears, click on the Security tab. Click the check box labeled Disable security. Click OK. The database no longer enforces security.

Btrieve Security Policy Tasks

To change the security policy for a database

This section describes how to change the security policy for a given 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. Within PCC, locate the database engine you wish to access. If you do not see an icon named Configuration under your selected database engine, double-click on that database engine to explose the Configuration icon.
  2. Right-click on Configuration and select Maintain Named Databases. Optionally, you may click on Configuration and then click the Action menu and select Maintain Named Databases. You will see the dialog shown in Figure 7-1.

    Click on the database for which you wish to change the security policy, such as DefaultDB.

     
    Figure 7-1 Maintain Named Databases - Changing Security Policy

  1. In the Security Policy box, choose which security policy you wish to use (Mixed or Database). Then 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.

  1. If you are prompted to confirm your changes, click OK again.
  2. Restart the database engine for these changes to take effect.

To create a new database for use with your Btrieve files

  1. From the Action menu of PCC, choose New Database.
  2. The Create Database Wizard appears, as shown in Figure 7-2. Enter a name for the database, specify a location for the database definitions, and check Use Advanced Settings. Click Next.

     
    Figure 7-2 Create Database Wizard

  1. In the next screen, shown in Figure 7-3, keep the default values shown unless you are certain you require different values. Click the Create button.

     
    Figure 7-3 Specify DSN Attributes

  1. In the next screen, shown in Figure 7-4, click the plus button . In the dialog that appears, enter or select a directory containing your Btrieve files. If all your files are in sub-directories under one particular directory, you need only add that one top-level directory. Otherwise, repeat this step for each directory you need to add until you have added all the directories containing Btrieve files.

Caution
You may be tempted to choose your new Security Policy now, but do not-you will lock out all your users until you define their database rights. You must come back and choose your security policy in a later step, after you have granted database rights to the users.

 
Figure 7-4 Data File Locations

  1. You should now be back in the screen shown in Figure 7-3. Click Next to display the confirmation screen.
  2. In the confirmation screen, review the information and click Finish, or click Back to change your inputs.

    Now you are ready to turn on security, as detailed in To turn security on for a Database .

To use an existing database, including the pre-defined DefaultDB, with your Btrieve files

  1. Within PCC, locate the database engine you wish to access. If you do not see an icon named Configuration under your selected database engine, double-click on that database engine to explose the Configuration icon.
  2. Right-click on Configuration and select Maintain Named Databases. Optionally, you may click on Configuration and then click the Action menu and select Maintain Named Databases. You will see the dialog shown in Figure 7-5.

    Click on the database you wish to use with your Btrieve files, such as DefaultDB.

     
    Figure 7-5 Maintain Named Databases

  1. Click the Add Data File Location button, circled in Figure 7-5. Enter or select a directory containing your Btrieve files. If all your files are in sub-directories under one particular directory, you need only add that one top-level directory. Otherwise, repeat this step for each directory you need to add until you have added all the directories containing Btrieve files.

Caution
You may be tempted to choose your new Security Policy now, but do not-you will lock out all your users until you define their database rights. You must come back and choose your security policy in a later step, after you have granted database rights to the users.

  1. If you are prompted to confirm your changes, click OK again.

    Now you are ready to turn on security, as detailed in To turn security on for a Database .

User and Group Tasks

To create a new user with SQL statements

  1. In PCC, right-click on the database you wish to work with. From the pop-up menu, select Tasks 4 Execute SQL Query to open SQL Data Manager (SQL DM).
  2. In SQL DM (or your selected SQL interface, such as ODBC Test), enter the following query and run it:

GRANT LOGIN TO myuser mypass

where myuser is the user name you wish to create, and mypass is the password for that user. For example, to create the user 'john' with password 'jb456', you would enter the following syntax: 

GRANT LOGIN TO john jb456 


   You have now created a new database user account. To assign privileges for this user, see Assigning Permissions Tasks .

To create a new user with PCC and assign permissions

  1. Log into your secure database as the Master user.
  2. Right-click on the Users node for a database.
  3. Select New User

     
    Figure 7-6 Creating a New Database User With PCC

  1. If you want this new user to inherit the permissions from a group instead of having individual permissions, you should first define a group. You cannot add a user to a group other than at the time of the user's creation. If you do not assign a group, this new user still inherits the permissions of the PUBLIC group. No user can have permissions that are lower than PUBLIC. Select the checkbox that corresponds to the group and click OK.

     
    Figure 7-7 Entering a New User

  1. If you want the user to have individual permissions, click the Permissions tab when you see Figure 7-7 and specify individual permissions by selecting the checkbox next to the database or table right you want to add.

     
    Figure 7-8 Assigning a User's Permissions

The Database Rights section grants that right to all tables in the database. The lower part of the dialog allows you to specify rights specifically by table. In the example shown in Figure 7-8, the user Joe has Update rights to all the tables, and Insert rights to only the Customer and Employees tables.

The following SQL statements would produce the same results as the selections in Figure 7-8:

GRANT Update on * to Joe#

GRANT Insert on Customers to Joe#

GRANT Insert on Employees to Joe#

  1. It is also important to note that Joe could also inherit rights from the default group PUBLIC that you could not see in Figure 7-8. To see all of a user's potential rights, you need to look at both the user's properties and that of PUBLIC.

     
    Figure 7-9 Rights Assigned to PUBLIC Group

In this case, PUBLIC also has Select rights to all tables, so user 'Joe' has Select and Update rights on all tables, based on his combined individual and PUBLIC rights.

Assigning Permissions Tasks

To assign a user table privilege using SQL statements

  1. In SQL DM or your selected SQL interface, enter the following statement to assign privileges for an existing user:
  2. GRANT privilege ON table to user

To assign default permissions for a specific user

  1. Create a user as described in To create a new user with PCC and assign permissions but do not assign any specific permissions.
  2. The permissions assigned to the group PUBLIC will be enforced for the user.

To assign default permissions for all users

  1. Using PCC, Click the Users node for the secure database.
  2. Right-click PUBLIC and select Properties.
  3. Click the Permissions tab.
  4. Assign the database or table permissions for PUBLIC using the checkboxes as shown in Figure 7-9 . Any right that is not checked is not granted to default new users.

To grant the same rights to all users

To avoid setting up and maintaining individual user accounts, one approach to configuring Btrieve security is to grant the same rights to all authorized users. You must be logged in as Master to perform this procedure.

  1. In PCC, right-click on the database you want to work with (usually DefaultDB). From the pop-up menu, choose Tasks 4 Execute SQL Query to start up SQL Data Manager.

    If prompted to login, enter the user name Master and the master password you created when you turned on security.

  2. In the SQL Data Manager window that appears, enter the following SQL statement:
    To grant this right to all users....
    ... use this syntax:
    read-only
    GRANT SELECT ON * TO PUBLIC
    update-only
    GRANT UPDATE ON * TO PUBLIC
    delete-only
    GRANT DELETE ON * TO PUBLIC
    insert-only
    GRANT INSERT ON * TO PUBLIC
    all of the above
    GRANT ALL ON * TO PUBLIC

    Rights are additive, so you can specify a combination of rights by issuing more than one statement. For example, if you want all users to be able to read and update records only, then you can issue two statements: GRANT SELECT ON * TO PUBLIC and GRANT UPDATE ON * TO PUBLIC.

    These rights are effective immediately upon execution of the statement.

Note
If you are in the process of securing your Btrieve files, you must now change your Btrieve Security policy. For instructions on how to perform this task, see To change the security policy for a database .

Prev
Btrieve Security Quick Start		 Contents
Up
Check for Revisions 		Next
Data Encryption