Security Models and Concepts

Security Models and Concepts

This section details the available security models for the SQL (relational) and Btrieve (transactional) interfaces. Both interfaces share the same level of granularity in choosing how rights are assigned to users. Both interfaces support database security. The transactional interface has additional security policies that can determine how access is granted. The relational interface supports column-level security.

Note: Specifying a security policy for the transactional interface has no effect on relational interface security. For purposes of discussion, however, you can think of the Database policy that is discussed under transactional interface security as the same type of security for the relational interface. A key difference between security for the two interfaces is that you cannot change security policies for the relational interface. Security is either on or off. If on, security is analogous to that of the Database policy type for the transactional interface.

Available Model for the Relational Interface

For the relational interface, security is either turned on or off (see Note above). By default, security is turned off. Security is turned on by suppling a password for the Master user.

With security on, you need to define, at a minimum, users through PCC who are authorized to log in to the database. For each user, you may set permissions for certain objects. In addition, you may define groups of users and set object permissions for each group.

In SQL Engine Reference, the following content applies to security for the relational interface:

•

Permissions on Views and Stored Procedures

•

•

•

•

•

•

•

•

•

•

•

•

psp_procedure_rights

•

psp_table_rights

•

psp_view_rights

•

You may access data from more than one database provided the databases are on the same machine. However, you can be logged in to only one database at a time. The following situations apply to database access based on the security of each database.


Security for Logged-in Database	Security for Database Not Logged In	Discussion
On	Off	Access granted for all rights.
On	On	User name and password must be identical in both databases.
Off	On	Access denied.

The security discussion in the rest of this chapter applies only to the transactional interface. If you are interested only in the relational interface, you can skip to Data Encryption.

Available Models for the Transactional Interface

The authentication and authorization models that are available for transactional interface include the following:

•

•

•

This topic, and the security discussion in the rest of this chapter, applies to applications that use only the transactional interface. The term credentials, login credentials, or user credentials refers to a valid user name and password pair.

Pervasive PSQL v11 SP3 supports OS-independent database authentication and authorization capabilities for the transactional interface. 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 file system users and privileges. You can allow users access to the database without allowing them operating system access to the data files.

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

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

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.

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 database 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 file system privileges on the data files.

Database authorization for Btrieve applications is provided by extending the relational interface security model so that it also can be used for Btrieve applications. The ability to create and define users and set permissions is provided through PCC and through SQL statements such as GRANT, REVOKE, ALTER GROUP, CREATE USER, ALTER USER, DROP USER.

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 permissions of the given user. Each user must be added to the database. Instead of defining individual permissions for each user, you can define the permissions once using the default group PUBLIC. Valid users automatically inherit the permissions granted to the PUBLIC group.

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

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 file system 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 relational interface security model so that it also can be used for Btrieve applications. The ability to define users and set permissions is provided through 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 steps 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 permissions must be defined. The simplest case for assigning permissions is to assign them to the special group PUBLIC. All users inherit the default privileges from PUBLIC. In addition, you must specify the file system 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 directory tree. 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 the same. If you with to secure a Btrieve database using the Workgroup engine, you must use the Database security policy.

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 making a transition from Classic to Mixed or Database security, see Security Tasks. The next section provides a brief overview of the material contained in the Security Tasks.

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

A “short” owner name can be up to 8 bytes. A “long” owner name can be up to 24 bytes. Note, however, that once a long owner name is specified, the data file cannot be read by a database engine prior to Pervasive PSQL v10.10. Also, a data file with a long owner name cannot be rebuilt to a file format prior to 9.5 unless the owner name is first removed. An owner name, long or short, with less than the maximum allowed bytes is padded with spaces to the maximum length (8 or 24 bytes).

PCC currently does not provide a way to specify an owner name through the security properties of a file. However, you can use a GRANT statement in PCC SQL Editor to supply an owner name. See Owner Name in SQL Engine Reference. You can also set or clear an owner name on a file with the Maintenance Utility or the Function Executor utility. See Manipulating Btrieve Data Files with Maintenance.

An owner name can have several attributes, as shown in Table 29.

Table 29

Owner Name Options

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.

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.

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.

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: Remember and keep track of a file’s owner name, especially with encryption turned on. There is no way to find out the owner name, and no way to access to the data without it.

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.

For examples of granting access to files with Btrieve owner names, see GRANT in SQL Engine Reference.

Accessing Data in More Than One Database

You may access data from more than one database provided the databases are on the same machine. However, you can be logged in to only one database at a time. The following situations apply to database access based on the security of each database.

Table 30

Access Rights to Databases Based on Security Settings

Security for Logged-in Database

Security for Database Not Logged In

Enabled (either mixed or database security model)

Access granted for all rights.

Enabled (either mixed or database security model)

Enabled (either mixed or database security model)

User name and password must be identical in both databases.

Enabled (either mixed or database security model)