Planning Security for the Transactional Interface

After you install Pervasive PSQL v9 Service Pack 2, the default behavior for security is the same as the previous release. That is, the database engine uses Classic or OS-based authentication and authorization. Any user with permission to access a given data file through the operating system will have the same level of permission to access the data records contained within the file, unless you are using Btrieve owner names to restrict access to the data files.

This section describes the steps you must follow to set up the default database, authorized users, and other aspects of the Btrieve security policies.

Available Options

There are three security options available to you. The features of these options are described below to help you choose which is best for you. Encryption is optional in every configuration.

Table 7-3 Feature Comparison of Security Configurations
Feature	Classic	Mixed	Database
Administrator must set up separate OS and database user accounts for each user
Database user accounts are derived directly from OS user accounts
Users' data access rights are unrelated to users' file system rights; administrator must assign data access privileges through the database to each user
Users' data access rights are derived directly from OS users' file system rights
Supports automatic login dialog for entering database user name and password from any Pervasive-based Windows application
Database accepts successful OS login as valid database user
User must log into database separately from logging into computer
(1) The login dialog may appear if the requester cannot establish an identity through the operating system.

Under Database security, database user accounts are completely unrelated to OS user accounts.

In contrast, under Classic security, a user who successfully logs into the computer has access to the database contents, at whatever level of file system rights that the user has been assigned to the file that contains the data.

Lastly, the Mixed security policy has aspects of both of the other policies. Under this scheme, users log in using their OS user names and passwords, but then the users access rights to the data are governed by user permissions set up in the database.

Choosing Your Policy

This section describes some of the major reasons you might choose one security policy over another.

Reasons to Choose Classic

You are comfortable with your users having file system access to your data files. For example, any user with rights to delete records from the data file can also delete the entire file from your operating system.

You want the minimum administrative hassle; you don't want to set up both OS user accounts for each user and at least one database account.

You do not need to have a variety of data access rights that vary from each user's file system rights.

You don't want your users to have a separate login for the database.

Reasons to Choose Mixed

You don't want your users to have a separate login for the database.

You want to prevent valid database users from having any rights to the data files on the operating system. For example, you can prevent users who have all rights in the database from having rights to delete data files from the operating system.

You are willing to set up database user accounts that have the same user names as OS user accounts, and you are willing to assign permissions to each database user. If you choose, all of your users can have the same level of permissions by inheriting them from the special group PUBLIC.

Reasons to Choose Database

You want to have a separate login for the database. That is, after logging into the operating system, users must login again to the database. This behavior is useful when some authorized computer users are permitted access to the database and some are not.

You want database user accounts that use different names than the operating system accounts. For example, operating system user "jsmith" might be required to login to the database as "john."

The users and their permissions stay with the database, not with the server or machine. This allows you to move a database from one machine to another without having to re-create the users and their permissions for the database.

Preparing to Set Up Security

Setting up security for the transactional interface is a simple process, but it affords enough flexibility that some preparation is necessary. This section describes the information you should know before you begin to set up Btrieve security.

How Many Databases?

For Mixed or Database security, you must either assign all users the same level of permissions, or create a set of defined users for each database.

In some cases where your Btrieve data files encompass two or more completely unrelated bodies of data, you may want to set up two or more separate databases, each with its own set of authorized users. Generally speaking, however, you want to minimize the number of separate databases so that you do not have to create and maintain multiple sets of defined users. Often, a single database is sufficient. User permissions within the database will allow you to regulate each user's access to the database, so you do not need to create separate databases just to limit certain users' access.

If you determine that you need only one database, you may use the pre-existing database, DefaultDB, as the database associated with your Btrieve files. You may also set up your own named database instead.

Where are the Data Files?

You associate a Btrieve data file with a database by specifying the directory containing the data file as a Data Directory for the given named database. Thus, you need to know the directories containing all the data files that you want to associate with the database. If all the data files reside in a sub-directory tree within a specific directory, all you need to know is the top-level directory path name. You can even use "C:\" if you wish to include all data files on the hard drive.

What are the User Names?

If you plan to use Mixed security, you must either assign all users the same permissions, or set up user accounts for the users whose rights differ. If you are going to set up individual users, you must have a list of the operating system user names that you want to make into database user names. The database user names that you set up must match the operating system user names exactly. You can always add additional user names later, but it is more efficient to create several users at once.

What Security Policy?

Before you set up security, you must know what policy you plan to use. The setup process varies somewhat for each policy. Considerations in choosing a policy are presented in Choosing Your Policy .

Process Overview

This section outlines the high-level procedure used to set up security for a database. Detailed, step-by-step instructions are provided in the section that follows.

Preparation. As specified above in Preparing to Set Up Security, gather the information you need and make the decisions necessary to get started. How many databases? Where are the Btrieve files located? What are the user names? What security policy will you use?

Select a database to use with your Btrieve files, and populate the database with the data directory specifying the location of your data files. This step is only necessary for Mixed or Database security.

For details on this step, see To use an existing database, including the pre-defined DefaultDB, with your Pervasive PSQL files in Pervasive PSQL User's Guide.

Turn on security.

For details on this step, see To turn on security using Pervasive PSQL Explorer in Pervasive PSQL User's Guide.

Create users and permissions. Using SQL statements or PCC, create your user accounts and/or relevant user privileges. This step is only necessary for Mixed or Database security.

For the fastest, easiest way to grant users access, see To assign permissions to all users using Pervasive PSQL Explorer in Pervasive PSQL User's Guide.

Set the Btrieve Security for your database to Mixed or Database.

For details on this step, see To set or change the security policy for a database in Pervasive PSQL User's Guide.

Secure the data files in the operating system. For Mixed or Database security, users now can access the data without having any rights to access the data files in the operating system. Refer to your operating system documentation for information on securing access to files.

Summary of Tasks for Transactional Interface Security

The following table illustrates the basic level of effort required using the different security models. The tasks required to implement the security models, see Security Tasks .

Table 7-4 Summary of Security Set-up Tasks
Security Model	Authentication/Authorization	Summary of Behavior and High-Level Setup Tasks
Classic	Operating system/Operating system	Give users file permission access to all database files. Add an owner name to Btrieve files to further limit access (optional)
Mixed	Operating system/Database	Note that this security model behaves the same as Classic when using the Workgroup engine. Set up users in operating system. Users will be authenticated against this user name and password. If you want individual user security, set up like-named users in the database security using the Pervasive Control Center. Although authentication occurred at OS level, database permissions are stored in the database, so the operating system user name and database user name must match. Define each user's database permissions using Pervasive Control Center or SQL statements. Alternatively, define a set of rights for the group PUBLIC. Each authenticated OS user inheriting from the group PUBLIC will have the same rights as PUBLIC. No user can have rights defined that are lower than that of PUBLIC.
Database	Database/Database	Operating system user names and passwords are not relevant to the Pervasive PSQL database security. Set up users using the Pervasive Control Center utility or SQL statements. Define the database permissions using Pervasive Control Center or SQL statements. Using the Pervasive Control Center Configuration tool, specify how authentication credentials are passed. This step refers to the new configuration parameters Prompt for Client Credentials and Allow Client-stored Credentials .