Security Models and Concepts

---

This section details the available security models for Btrieve and SQL. Both the Btrieve and SQL interfaces share the same level of granularity in choosing how rights are assigned to users. Both interfaces support database and column-level security. To support Btrieve applications that existed before the SQL security model was applied to Btrieve, the Btrieve interface has additional security policies that can determine how access is granted.

Available Models for Btrieve

This section describes the authentication and authorization models that are available in this release. The term credentials, login credentials, or user credentials refers to a valid username and password pair.

This release of the product supports OS-independent database authentication and authorization capabilities for the transactional (Btrieve) database engine. The original (operating system) authentication model is still available in this release, but now you can instead choose a model in which Btrieve users and privileges are not derived from filesystem users and privileges. One major advantage of the new security model is that now you can allow users access to the database without allowing them operating system access to the data files.

This topic applies only to Btrieve applications and users. Applications that use the SQL Relational Database Engine (SRDE) already have available an implementation of this complete and OS-independent database security model.

Current Btrieve applications can take advantage of the new security models without requiring any changes to the application code.

Classic

Classic security is the Btrieve security model that was provided in previous releases of the product. For Btrieve users, authentication is performed by the operating system, and data access privileges are determined by file system rights for the given user. The only authorization capability provided by the database engine independent of the operating system is Btrieve owner names, which are essentially file access passwords.

Under this security model, any user who is authenticated by the operating system has the same rights to access the data through Btrieve as he or she has to read and/or write the data files through the operating system. Btrieve owner names are an exception to this rule, allowing an additional level of authorization. However, this level of authorization is not related to the user's identity. It is related only to whether the application or the user can supply the owner name for a given file.

For more information on Btrieve owner names, see Advanced Operations Guide.

Mixed

In the Mixed security model, when a database login occurs, the database engine passes the user name and password entered by the user to the operating system authentication service. If the operating system authenticates the user name and password, then the database engine uses the users and rights table for the specified database (the current database if no database is specified) to determine the specific access rights of the given user. Thus, each user's data access privileges must be defined within the database. In turn, the database engine enforces the defined privileges regardless of the given user's filesystem privileges on the data files.

Database authorization for Btrieve applications is provided by extending the SRDE security model so that it also can be used for Btrieve applications. The ability to define users and set permissions is provided through the Groups and Users functionality in PCC, and through SQL statements such as GRANT and REVOKE.

Under the mixed security model, any user names defined in the database must correspond exactly with the same user names defined in the operating system. During a database login, the database engine simply passes the user name and password entered by the user to the operating system authentication module. If the operating system authenticates the credentials, then the database uses its own users and rights table to determine the specific access rights of the given user. You do not need to set up rights for every user. You can define the rights once using the default group PUBLIC, and every valid set of operating system credentials will inherit the rights granted to the PUBLIC group.

For detailed procedures on how to set up a Mixed security environment, see Setting up Mixed or Database Security .

Database

Under the Database security model, the database engine authenticates and authorizes users of Btrieve data files. A user's ability to connect to and access data is unrelated to the user's operating system account identification and filesystem privileges, as long as the user can successfully login to the computer on which his/her application runs.

Database authentication and authorization for Btrieve applications is provided by extending the SRDE security model so that it also can be used for Btrieve applications. The ability to define users and set permissions is provided through the Groups and Users functionality in PCC, and through SQL statements such as GRANT and REVOKE.

---

Note
To create new databases, a user is still required to have administrator-level privileges in the operating system.

---

For detailed procedures on how to set up a Database security environment, see Setting up Mixed or Database Security .

Notes on the Mixed and Database Security Models

For each database, a set of users must be defined, and for each user, a set of access privileges must be defined. The simplest case is defining access rights for the user PUBLIC, thus creating a set of rights for all users who are successfully authenticated. In addition, you must specify the filesystem directory or directories that contain the data files that should be considered as members of the given database. The database engine (or operating system in the case of Mixed security) performs user authentication and authorization for each attempt to access any data file within the specified directories. Without this association between databases and directories, when a Btrieve application attempts to open a specific data file, the database engine has no database context from which to determine the applicable set of defined users and permissions.

You can use the Mixed or Database security models only with Btrieve data files that reside in directories that have been defined as belonging to a given database, including the default database DefaultDB described in The Default Database and the Current Database . Data files residing in directories that have not been associated with a database can be accessed only with the Classic security model.

One of the primary advantages of these models is the ability to restrict operating system users' access to the data files, while still allowing full access to the data through the database engine. In contrast, according to the Classic model, any user permitted to add records to the data file must necessarily also have the ability to copy or delete the data file from the operating system.

---

Note
Since the Workgroup engine performs no operating system authentication, the behavior of the Classic and Mixed security policies using the Workgroup engine are essentially the same. If you with to secure a Btrieve database using the Workgroup engine, you must use the Database security policy.

---

Setting up Classic Security

Under Classic security, you set up database users and access permissions simply by creating users and assigning file permissions in the operating system. There are no separate actions to take to configure the database engine.

Refer to your operating system documentation for instructions on how to set up user accounts and assign permissions.

Setting up Mixed or Database Security

Migrating to mixed or database security requires that you make a number of choices and plan carefully. In a well-established environment, you may have to plan how your Btrieve files will be grouped together into databases, and schedule the migration so that you do not disrupt your production environment.

For complete information on transitioning from Classic to Mixed or Database security, see Setting Up Btrieve Security . The next section provides a brief overview of the material contained in the Setting Up Btrieve Security .

Summary of Tasks for Security Models

The following table illustrates the basic level of effort required using the different security models. For a more in depth view of the tasks required to migrate to the new security models, see Setting Up Btrieve Security .

 
Table 7-1 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 username 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. Alternatively, define a set of rights for the group PUBLIC. Each authenticated OS user will have the same rights as PUBLIC. No user can have rights defined that are lower than that of PUBLIC. Define each user's database permissions using Pervasive Control Center or SQL statements.
Database	Database/Database	Operating system usernames and passwords are not relevant to the Pervasive.SQL database security, with the exception that the user must be able to log in to the computer. 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 .

For complete information on transitioning from Classic to Mixed or Database security, see Setting Up Btrieve Security .

Available Models for SQL

SQL has always had only one model, which corresponds to the Database model for Btrieve.

Users and Groups

Pervasive security is based on the existence of a default user named "Master" who has full access to the database when security is first turned on. Initially, no password is required for the Master user.

---

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.

---

The Master user can create groups and other users and define sets of data access privileges for these groups and users.

If you want to grant the same level of access to all users and avoid having to set up individual groups and users, you can grant the desired level of access to PUBLIC. The default user PUBLIC represents any user connecting with or without a password.

---

Note
If you wish to use groups, you must set up the groups before creating users. You cannot add a user to a group after you have already created the user.

---

You can use the Users node in PCC to perform these tasks. You can also use GRANT and REVOKE statements in SQL to perform these tasks. Keep in mind that passwords are case sensitive.

See Pervasive.SQL User's Guide for information on performing these tasks within PCC.

Restrictions

• 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.
• Assigning a user to a group overrides and erases any individual permissions that were granted to the user beforehand.
• You cannot add an existing user to a group. To do so, you must delete the user and re-create the user as a member of the group.
• You cannot add a group to another group.

Owner Names

An owner name is a password required to gain access to a Btrieve file. There is no relation between an owner name and any system user name or relational database user name. You should think of an owner name as a simple file password.

For information on how to set or clear an owner name, see Setting or Clearing an Owner Name

You can use Maintenance Utility to set or clear an owner name on a file. An owner name can have several attributes, as shown in Table 7-2.

 
Table 7-2 Owner Name Options

Option	Description
Read-only	Without specifying the password, users can perform data access operations that do not modify the data file.
Read-only encrypted	Without specifying the password, users can perform data access operations that do not modify the data file. When you set this option, the database engine encrypts every record in the file using the owner name as a key. Records added later are also encrypted.
Normal	Without specifying the password, users cannot perform any file access operations.
Normal encrypted	Without specifying the password, users cannot perform any file access operations. Any records inserted or updated are encrypted using the password. When you set this option, the database engine encrypts every record in the file using the owner name as a key. Records added later are also encrypted.

Remarks

When you first set an owner name with encryption on a file, the database engine encrypts the entire file. The larger the file is, the longer this procedure takes.

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

Data access operations to an encrypted file are slower than to a normal file. The database engine must decrypt each page as it reads it from disk, and encrypt it again before writing it back to disk.

---

Caution
Do not forget or lose a file's owner name, especially with encryption turned on. There is no way to find out the owner name, and no way to get access to the data without it.

---

If you attempt to access a file that has an owner name declared without supplying the owner name, you receive the message, "You are not authorized to perform the operation."

Owner Names and Security

If you have a Btrieve owner name set on a file that is a table in a secure database, the Master user of the database must use the owner name in any GRANT statement to grant privileges on the given table to any user, including the Master user.

After the GRANT statement containing the owner name has been issued for a given user, that user can access the specified table by logging into the database, without specifying the owner name each time.

If a user tries to access a table through ODBC that has a Btrieve owner name, the access will not be allowed unless the Master user has granted privileges on the table to the user, with the correct owner name in the GRANT statement.

If a table has an owner name with the Read-Only attribute, the Master user automatically has SELECT rights on this table without specifically granting himself/herself the SELECT rights with the owner name.

If no owner name is set on a data file, when relational security is enabled on that file, Btrieve access to the file is no longer permitted. You must set an owner name on that file in order to restore Btrieve access for those users who can supply the owner name when accessing the file. This behavior prevents default Btrieve users from circumventing relational security.

Examples

To grant himself/herself full access to a table with an owner name, the Master user must enter the following SQL statement:

GRANT ALL ON table-name owner-name TO Master 

To grant SELECT rights on a table named mytable with an owner name of jim56, the Master user must enter the following SQL statement:

GRANT SELECT ON mytable jim56 to bob_smith 

Extracting Security Information using SQL

While security information is displayed in PCC using the Users node shown under each named database, you can also obtain security information programmatically using SQL. The statements below show some examples of SQL statements that extract security information from a database. Note that you must login as the Master user or a user with rights to view the system tables.

SELECT "Xu$Name", "Xu$Id" FROM "X$User" 
 
SELECT "X$File"."Xf$Id", MIN("X$File"."Xf$Name"), MIN("X$Rights"."Xr$Column")  
    FROM "X$Rights", "X$File" WHERE "X$Rights"."Xr$User"=2 AND "X$Rights"."Xr$Table"="X$File"."Xf$Id"  
    GROUP BY "X$File"."Xf$Id" 
 
SELECT "Xr$Rights" FROM "X$Rights" WHERE "Xr$User"=2 AND "Xr$Table"=4 AND "Xr$Column"=0 

The first statement lists all the user IDs and the user names associated with them.

The second statement lists the tables where the rights have been set for userID = 2. This restriction is shown in the WHERE clause.

The last statement shows you the table rights for a specific user ID. The segment, "Xr$User"=2, specifies the userID. The segment, "Xr$Table"=4, restricts the table ID to a valid value that was returned by the previous query. The restriction for "Xr$Column" is always set to 0.