About This Manual

This manual contains information about the features and enhancements that are new in this release of Pervasive.SQL. This release is referred to as Pervasive.SQL V8.5. The internal version number is 8.50.

This manual describes the new and changed behaviors of the product relative to the most recent previous release, which is Pervasive.SQL 8.10 (V8 SP1).

Prev
Check for Revisions 		Next
Who Should Read This Manual


Who Should Read This Manual

This document is designed for any user who is familiar with Pervasive.SQL and wants to know what has changed in this release of the software.

This manual does not provide comprehensive usage instructions for the software. Its purpose is to explain what is new and different in this particular release of the product.

Pervasive Software Inc. would appreciate your comments and suggestions about this manual. As a user of our documentation, you are in a unique position to provide ideas that can have a direct impact on future releases of this and other manuals. If you have comments or suggestions for the product documentation, post your request at http://www.pervasive.com/devtalk.

Prev
About This Manual 		Up
Check for Revisions 		Next
Manual Organization


Manual Organization

This manual begins with an overview of the new features, then provides chapters containing additional details where appropriate. What's New in Pervasive.SQL V8 is divided into the following sections:

  • Chapter 1—What's New in Pervasive.SQL V8.5

    This chapter provides an overview of the changes in this release of the software.

  • Chapter 2—Utility Updates for Security

    This chapter covers the improvements made to the utilities.

  • Chapter 3—Changes to Programming Interfaces for Security

    This chapter covers the enhancements made to the Btrieve API to support the new security features.

  • Chapter 4—Setting Up Btrieve Security

    This chapter provides a list of tasks for several migration paths.

This manual also contains an index.

Prev
Who Should Read This Manual 		Up
Check for Revisions 		Next
Conventions


Conventions

Unless otherwise noted, command syntax, code, and examples use the following conventions:
CASE | Commands and reserved words typically appear in uppercase letters. Unless the manual states otherwise, you can enter these items using uppercase, lowercase, or both. For example, you can type MYPROG, myprog, or MYprog.
Bold | Words appearing in bold include the following: menu names, dialog box names, commands, options, buttons, statements, etc.
Monospaced font | Monospaced font is reserved for words you enter, such as command syntax.
[ ] | Square brackets enclose optional information, as in [log_name]. If information is not enclosed in square brackets, it is required.
| | A vertical bar indicates a choice of information to enter, as in [file_name | @file_name].
< > | Angle brackets enclose multiple choices for a required item, as in /D=<5|6|7>.
variable | Words appearing in italics are variables that you must replace with appropriate values, as in file_name.
. . . | An ellipsis following information indicates you can repeat the information more than one time, as in [parameter . . . ].
::= | The symbol ::= means one item is defined in terms of another. For example, a::=b means the item a is defined in terms of b.

Prev
Manual Organization 		Up
Check for Revisions 		Next
What's New in Pervasive.SQL V8.5


What's New in Pervasive.SQL V8.5

An Overview of New Features in Pervasive.SQL

The purpose of this chapter is to summarize and explain the major new features and differences in behavior between this product and the previous release. If further information is available for a given change or feature, a hot-link or cross-reference to that information is provided.

  • List of New Features and Improvements
  • New Security Models for Btrieve Applications
  • Encrypted Network Communications
  • Improved SQL Support
  • Utilities Support for New Security Models
  • Updates to Programming Interfaces
  • Improved Installation Programs
  • Documentation Updates
Prev
Conventions
Check for Revisions 		Next
List of New Features and Improvements


List of New Features and Improvements

This release offers the following new features and improvements over the last release of the product:

  • New Security Models for Btrieve Applications
  • Encrypted Network Communications
  • Improved SQL Support
  • Utilities Support for New Security Models
  • Updates to Programming Interfaces
  • Improved Installation Programs
  • Documentation Updates

These features are described in the sections that follow.

Prev
What's New in Pervasive.SQL V8.5 		Up
Check for Revisions 		Next
New Security Models for Btrieve Applications


New Security Models for Btrieve Applications

This release of the product extends OS-independent database authentication and authorization capabilities to 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.

Available Models

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.

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.

Under the mixed security model, any user names and passwords defined in the database must correspond exactly with the same user names and passwords defined in the operating system.

Note
If the database password is changed and you are using the Mixed security model, remember to change the OS password as well, Otherwise, you will receive a permission error such as status 94.

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 wish to secure a Btrieve database using the Workgroup engine, you must use the Database security policy.

The Default Database and the Current Database

In order to support existing applications that do not specify a database name when creating or opening Btrieve files, this release introduces the concept of a default database for each transactional database engine. The default database is a pre-defined database named "DefaultDB." To make use of the new security models without having to modify your application code, you can associate your Btrieve data directories with the default database, then set up users and privileges in the default database to control access to the data files in those directories.

This release also introduces the concept of a current database for each client connection. If no database name is specified in a Btrieve Login (78), Create (14), or Open (0) operation, the transactional engine assumes the operation is associated with the current database. For each client, the current database is the database to which the most recent Login (78) operation occurred (explicit login). If the client computer has requested no explicit login operations, the current database is the database to which the most recent Create (14) or Open (0) operation occurred (implicit login). If no explicit or implicit logins have occurred, then the current database is the default database, described in the preceding paragraph. Note that the current database may change at any time when the given client performs an implicit or explicit login, or closes the last file handle, making "DefaultDB" the current database. The current database for each client is independent of other clients' activities.

The simplest way to configure the new security model for existing applications is to associate all Btrieve data directories with the default database, and set up rights for the group PUBLIC within this database. The group PUBLIC is automatically created along with the Master user when you enable security for a database.

New or Changed Configuration Parameters

Several new configuration parameters have been added to support this new feature. This section specifies the additions. All of the following parameters can be found within Configuration under Server 4 Access.

Prompt for Client Credentials
Name | Type | Range | Default | Units
Prompt for Client Credentials | Boolean | On, Off | Off | N/A

This setting determines whether the Win32 Pervasive.SQL client prompts the user for login credentials if no other credentials are available during a database operation that requires user authentication.

When this setting is On, in the absence of other authentication credentials, the engine requires the Win32 client to present a login dialog to the user. This setting only applies when Mixed or Database security is in effect, and does not apply to the Linux client under any circumstances. If valid credentials are supplied via another method (for example, explicit Btrieve Login (78) operation or credentials stored on the client), the the login dialog does not appear.

If no database context is specified to the engine within the operation requiring user credentials, the engine assumes the user is attempting to login to the current database.

When this setting is Off and one of the new security models is in use, user credentials must be provided programmatically (credentials stored on the client or provided with a Btrieve Login (78), Open (0), or Create (14) operation), or else the login attempt fails with an authentication error.

See Also

Allow Client-stored Credentials, immediately below

Allow Client-stored Credentials
Name | Type | Range | Default | Units
Allow Client-stored Credentials | Boolean | On, Off | On | N/A

When this setting is On, the database engine accepts user credentials stored on the client. The method and location of storage depends on the operating system of the client:

  • Windows clients: these credentials are stored in the Windows registry. When the Prompt for Client Credentials setting is set to On, then a pop-up dialag allows you to save the credentials by selecting the Save User name and Password checkbox. Alternatively, you can use the pvnetpass command-line utility to manage stored credentials.
  • Linux clients: credentials are stored in the Pervasive registry by the pvnetpass utility.

When this setting is Off, the database engine forces the client to omit stored credentials from any database operation that requires credentials. Such credentials must be supplied by the application or (on Win32 clients only) through the login dialog.

When client-stored credentials are allowed, anyone can sit at that particular client computer and login to the database using the stored credentials without knowing those credentials. This behavior can be convenient for environments in which strict authentication of individual users is not a concern, such as a physically secured work area where all users have the same level of access permissions. On the other hand, in environments where unauthorized personnel are present or authorized users have varying levels of access permissions, this setting must be Off.

See also: Prompt for Client Credentials, immediately above.

Summary Chart of Login Behavior

Table 1-1 Summary of Login Configuration Behavior
Prompt for Credentials | Allow Client-Stored Credentials | Behavior
Off | Off | Pervasive.SQL client does not prompt the user or use stored credentials, thus credentials must be supplied by the client application during a Btrieve operation.
Off | On | If credentials are not supplied by the client application during the Btrieve operation, the client uses credentials stored by Runtime Server Support setting or by pvnetpass, if such credentials are available. If no credentials are supplied by either method, the connection attempt fails. No login dialog is displayed.
On | Off | If credentials are not supplied by the client application during the Btrieve operation, the Win32 client displays a login dialog to the user, and the Linux client returns a status code for permissions error. Credentials stored by Runtime Server Support setting or by pvnetpass are not used.
On | On | If credentials are not supplied by the client application during the Btrieve operation, stored credentials are used. If no stored credentials are available, then the Win32 client displays a login dialog to the user, and the Linux client returns a status code for permissions error.

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 1-2 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 client 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 .

Prev
List of New Features and Improvements 		Up
Check for Revisions 		Next
Encrypted Network Communications


Encrypted Network Communications

This release adds support for encrypting the database-related network traffic that occurs when using Pervasive.SQL. This type of encryption is often called wire encryption because it protects the data when it is traveling on the network wire, or on any intervening network infrastructure, including wireless. While the use of wire encryption is not required, it provides additional deterrence against unauthorized access to the data transmitted by your application over a network.

This encryption feature is not directly related to the new security models available in this release. Any of the security models can be used with or without wire encryption turned on.

New Configuration Parameters

This section describes the new Configuration parameters associated with this feature. These parameters must be set at each client machine as well as the server. Within Configuration, they can be located under Client 4 Access or Server 4 Access, as appropriate.

Note
The Pervasive.SQL client and server both share a single encyption setting which is found in the Server settings.

Wire Encryption Level
Name | Type | Range | Default | Units
Encryption Level | SelectOne | Low, Medium, High | Medium | N/A

This new setting specifies the strength of the encryption key that should be used for encrypted communications. The following levels are available:
Table 1-3 Meaning of Wire Encryption Level Values
Value | Meaning
Low | 40-bit encryption key used
Medium | 56-bit encryption key used
High | 128-bit encryption key used

Encryption using a key 128 bits long is generally accepted as "strong" encryption. The other settings provide progressively less protection but higher performance, in the event that you require some level of encryption but are willing to accept a lower level of deterrence to gain better performance.

When a client and a server both require encryption and one specifies a stronger encryption level than the other, the two entities use the stronger level to communicate.

This setting is ignored if the setting Wire Encryption is set to Never.

Wire Encryption
Name | Type | Range | Default | Units
Wire Encryption | SelectOne | Never, If Needed, Always | If Needed | N/A

This parameter specifies whether the given client or server should use encryption for its network communications. The default value of If Needed means that the client or server only uses encryption if the other end of the communication stream requires it. For example, assume that Server A has its Wire Encryption value set to Always. Server B has its value set to Never. Your client has its value set to If Needed. In this case, the client will use encryption when communicating with Server A, but it will not use encryption when communicating with Server B.

The following chart summarizes the behavior given each possible combination of client and server values:
Table 1-4 Client/Server Results of Wire Encryption Combinations
Client Setting | Server Setting "Never" | Server Setting "Always" | Server Setting "If Needed"
Never | Encryption not used | Status Code 5001 | Encryption not used
Always | Status Code 5000 | Encryption used; level determined by highest Wire Encryption Level setting between client and server | Encryption used; level determined by client's Wire Encryption Level setting.
If Needed | Encryption not used | Encryption used; level determined by server's Wire Encryption Level setting | Encryption not used

Remarks

This release of the product uses a well-known and time-tested public domain encryption algorithm called "Blowfish" to perform the encryption before data passes over the network.

Encryption using a 40-bit key provides the least amount of protection for the data. Encryption using a 56-bit key is more difficult to compromise. Finally, encryption using the 128-bit key is the generally considered very difficult to compromise.

Note
Using encryption slows the network throughput of your data.

Backward Compatibility

Because previous versions of Pervasive.SQL did not support wire encryption, they will be unable to communicate with a client or server from this release that requires encryption. Any client or server that does not support encryption will return an error if it attempts to connect to a client or server that requires encryption.

Setting Up Encryption

Before turning on the encryption settings in your environment, think about your encryption needs first. You can set up your encryption environment in a variety of ways, depending on your situation. There are four general schemes possible:

  • no encryption
  • all communications encrypted
  • encryption to/from specific clients
  • encryption to/from specific servers

No Encryption

First of all, consider whether your data has characteristics that would favor encryption. Is your data confidential or proprietary? Is it protected from the public by federal, state, or local laws? Is it valuable in the hands of unauthorized users? Can it be used to harm your organization? If you answer no to these question and others like them, then your data may not need to be encrypted at all. Under these circumstances, there may be no reason to incur the performance trade-off that encryption entails. If you aren't sure, consult a data security expert.

Assuming your data does need to be protected, you still may not need encryption. If your applications run solely on a LAN, and you are comfortable with the physical security of your network infrastructure, encryption may not be necessary.

Encryption to/from Specific Clients

Now suppose that you have one major customer at a remote site that has a connection to your database. You may wish to use encryption only for the communications that go to/from that remote client. You can achieve this affect by setting Wire Encryption at the remote client to Always and setting the server values accessed by that remote client to If Needed. All your internal clients would be set to Never. Thus, the servers will only use encryption when communicating with the remote client that requires encryption.

Encryption to/from Specific Servers

Now, suppose the situation is reversed and your environment includes one or more remote servers that are accessed by network infrastructure that you do not trust 100%. In this case, you can set those server values to Always, and set the local client values to If Needed. The result is encrypted communications only to those remote servers that require it.

All Communications Encrypted

Finally, if your Pervasive-based applications often run over WAN, VPN, or other external networks that you do not trust 100%, then you may wish to encrypt 100% of your database communications. In this scenario, you would set Wire Encryption to Always at all clients and servers.

Choosing an Encryption Level

Once you have decided which clients and servers require encrypted communications, you must decide what level of deterrence is appropriate for your needs.

While Pervasive Software Inc. cannot offer advice regarding the encryption level that meets your specific needs, we can provide some guidelines to help inform your discussions with an appropriate data security expert. These guidelines do not represent a guarantee or warranty from Pervasive that no third party will be able to intercept and/or decode your encrypted data. As with any encryption scheme, there is no such thing as an "unbreakable" code, only varying levels of difficulty to compromise different types of encryption. Pervasive.SQL's 128-bit encryption would be considered "very difficult" to decode using techniques and equipment available to a highly sophisticated individual hacker.

Low (40-bit) Encryption

Consider using this level of encryption in cases where your data has limited ability to harm your organization or your customers if it falls into the wrong hands. Another reason to consider a Low level of encryption is if you wish simply to prevent a casual observer on your network from being able to read your data as it travels over the wires.

Medium (56-bit) Encryption

Consider using this level of encryption in situations where you believe you need somewhat more protection than against just a casual observer, but you do not believe you require the strongest level of security.

High (128-bit) Encryption

Consider using this level of encryption in situations where your data contains very sensitive information such as credit card numbers, social security numbers, financial account numbers, or other information protected by law. Especially consider this level of encryption if your database is associated with an entity on the network that is well-known to contain sensitive data, such as an Internet shopping web site or an online securities brokerage web site. Consider this level of encryption if your organization has previously suffered attempts to compromise its data security.

Effects

Using encryption reduces client/server performance. With encryption turned on, each piece of data must be encoded at the source and decoded at the destination. This process requires additional CPU cycles when compared to the same operations performed without encryption. The level of encryption should not affect the performance. The performance drop in using encryption is roughly the same no matter which of the three encryption levels you choose.

Examples

This section describes the set-up procedures for some common encryption scenarios.

To set up maximum encryption for all clients and servers

  1. Using PCC, connect to each database server that you wish to require encrypted communications. If you need instructions on how to connect to a server in PCC, see Pervasive.SQL User's Guide.
  2. For the selected server within PCC, double-click Configuration; double-click Server; double-click Access.
  3. Double-click Wire Encryption, and set the value to Always. Click OK.
  4. Double-click Encryption Level, and set the value to High (128-bit). Click OK.
  5. Exit from PCC and restart the database engine. Repeat steps 1-5 for each server computer that you wish to require encrypted communications.
  6. Using PCC at each client, double-click the computer icon representing the client computer. Double-click Configuration; double-click Client; double-click Access.
  7. Double-click Wire Encryption, and set the value to Always. Click OK.
  8. Double-click Encryption Level, and set the value to High (128-bit). Click OK.
  9. Exit from PCC and restart the client applications.
  10. Repeat steps 6-9 for each client computer that you want to use encryption.

To set up minimum encryption for certain servers

  1. Using PCC, connect to each database server that you wish to require encrypted communications. If you need instructions on how to connect to a server in PCC, see Pervasive.SQL User's Guide.
  2. For the selected server within PCC, double-click Configuration; double-click Server; double-click Access.
  3. Double-click Wire Encryption, and set the value to Always. Click OK.
  4. Double-click Encryption Level, and set the value to Low (40-bit). Click OK.
  5. Exit from PCC and restart the database engine. Repeat steps 1-5 for each server computer that you wish to require encrypted communications.
  6. Using PCC at each client, double-click the computer icon representing the client computer. Double-click Configuration; double-click Client; double-click Access.
  7. Double-click Wire Encryption, and set the value to If Needed. Click OK.
  8. [Optional] Double-click Encryption Level, and set the value to Low (40-bit). Click OK.

    This step is optional. When the client's Wire Encryption value is If Needed, the level of encryption used is determined solely by the server setting for Encryption Level.

  9. Exit from PCC and restart the client applications.
  10. Repeat steps 6-9 for each client computer that you want to use encryption.
Prev
New Security Models for Btrieve Applications 		Up
Check for Revisions 		Next
Improved SQL Support


Improved SQL Support

This section describes the new SQL syntax that has been added to support security features in this release.

Database-wide Permissions

User permissions can now be assigned at the database level, by using the asterisk instead of a table name in a GRANT statement. This feature allows user privileges to be assigned to all tables in a given database.

Syntax

GRANT table-privilege ON [ TABLE ] < * | table-name [ owner-name ] >
TO user-or-group-name [ , user-or-group-name ]...

REVOKE table-privilege ON [ TABLE ] < * | table-name [ owner-name ] >
FROM user-or-group-name [ , user-or-group-name ]...

Remarks

When you grant database-wide privileges using the *, they apply to all tables in a given database except the system tables. You must have table-level rights for system tables.

Since privileges are additive, database-level privileges take precedence over the lack of any table-level rights. If you want to deny a user with database-level rights access to a file, assign a Btrieve owner name to the file. You must have specific table rights granted with an owner name in your permissions or you cannot open files that are protected with owner names.

Only the Master user can perform GRANT commands. You cannot use the database-wide (*) syntax when specifying an owner name.

As the Master database user, you can specify the owner names for the user when you define their permissions. For the Master user, you must use SQL to grant yourself access to the file because the Master user has no Permissions tab in Pervasive Control Center.

GRANT ALL ON table ownername TO Master

When users have database rights and the Create Table right, they are implicitly granted these same rights for any new table that is created by any user. Users are also implicitly granted the complete set of table-level rights for any tables they create. These table-level rights granted to that user are separate from the database rights. If later you revoke database privileges from the user, the user still has rights to the tables they created. The table rights must be revoked separately.

For more information on the default database and how to secure Btrieve files, see Chapter 4, Setting Up Btrieve Security.

Examples

After the Master user performs the following set of SQL statements, the user "jsmith" has SELECT access to all tables in the current database. The user also has DELETE access to tab1 and UPDATE access to tab2.

GRANT DELETE ON tab1 TO jsmith
GRANT SELECT ON * TO jsmith
GRANT UPDATE ON tab2 TO jsmith

If the following statement is performed later by any user with CREATE TABLE privileges, the user "jsmith" will have SELECT access to the newly created table.

CREATE TABLE tab3 (col1 INT)
Prev
Encrypted Network Communications 		Up
Check for Revisions 		Next
Utilities Support for New Security Models


Utilities Support for New Security Models

Some utilities included with Pervasive.SQL have been modified so that they can assist you in migrating to the new security features.

Defining Btrieve Databases and Security Policies

In the graphical user interfaces (GUIs) that support creation and maintenance of databases, a new Security Policy combination box has been added to allow you to specify the Btrieve security model the database engine should enforce for each database.

In addition, the Data File Locations text box now supports an additional purpose. For each folder pathname listed in the text box, the database engine assumes that the contents of the folder are associated with the given named database. Any user attempting to open or create a file in one of the specified directories will be authenticated against the specified database.

Create Database Wizard

The Create Database Wizard itself is not updated for this release. However, it is important to understand how to define security when creating new databases. You need to click Advanced settings in order to specify the Mixed or Database security models using the Maintain Named Databases dialog.

Maintain Named Databases Dialog

As mentioned earlier, this dialog controls both the security policy for your databases as well as the set of locations that define your Btrieve database.

Linux dbmaint Utility

The dbmaint utility in Linux allows you to define the security policy with the new -s switch. See Pervasive.SQL User's Guide for more information about dbmaint utility.

Figure 1-1 Maintain Named Databases Dialog - Added Security Selection

To display this dialog, right-click on the Configuration node in PCC and select Maintain Named Databases.

Defining Permissions for Databases

When setting up users and user permissions for Btrieve databases for the first time, or when extending your previous SQL security model, you use the improved Users and Groups security dialog box.

This dialog box is available through PCC when you click the Permissions tab on a user or group as shown in the following figure:

Figure 1-2 Users Permissions Dialog

See Users and Groups Permissions Dialogs for information on using this new utility functionality.

Using the Btrieve Login API and Specifying Credentials

The following utilities have been updated to use the Login API, or to pass user name and password when they did not possess this ability previously.

Function Executor

Function Executor now allows you to perform Btrieve Login operation using a graphical interface. You can also perform the login operation using the operations list.

BUTIL

The Btrieve Maintenance command-line utility (BUTIL) now allows you to specify user name, password, and database for the purpose of logging into a database.

You can also specify credentials using these Linux utilities:

  • dsnadd
  • dbmaint
  • odbctest

Other Utility Changes

Besides utility changes that are designed to support the new security features, there are other utility improvements for this release. For example:

  • Redesigned Rebuild utility in Windows
  • Command-line Rebuild utilities for all platforms
  • New "In Memory"option for PSA View Module

See Utility Updates for Security to read about these other changes.

Prev
Improved SQL Support 		Up
Check for Revisions 		Next
Updates to Programming Interfaces


Updates to Programming Interfaces

For information regarding the Btrieve API changes, see Changes to Programming Interfaces for Security .

Prev
Utilities Support for New Security Models 		Up
Check for Revisions 		Next
Improved Installation Programs


Improved Installation Programs

The installation programs for the following products have been updated in this release to use Windows Installer technology:

  • Windows NT Server
  • NetWare Server
  • Workgroup
  • Win32 Client requester

Note
These new installation programs are not related to the security features in this release. The purpose of the new installation programs is to improve the installation process and the ease with which Pervasive.SQL can be embedded within other applications.

Upgrading from Previous Releases

The installation programs for Pervasive.SQL V8.5 support upgrading from previous releases. However, uninstall the beta or release candidate before attempting to install this release.

You can use the Pervasive System Analyzer (PSA) utility to remove Pervasive components from a machine. See Pervasive.SQL User's Guide for documentation on PSA.

Documentation for Installs

The procedures for installing this release are documented in the Getting Started with Pervasive.SQL manuals that correspond to the product you are installing. See the following for the procedures to install this product:

Table 1-5 Updated Installation Documentation for Security Release Beta
Product | Description
Windows Server engine | Getting Started with Pervasive.SQL (Server edition) Installing Pervasive.SQL with No Previous Installation
NetWare Server engine | Getting Started with Pervasive.SQL (Server edition) Installing Pervasive.SQL on NetWare
Workgroup engine | Getting Started with Pervasive.SQL (Workgroup edition) Installing Pervasive.SQL with No Previous Installation
Client requester | Getting Started with Pervasive.SQL (Server edition) Installing Pervasive.SQL Windows Requesters

Prev
Updates to Programming Interfaces 		Up
Check for Revisions 		Next
Documentation Updates


Documentation Updates

The documentation for Pervasive.SQL can elp you explore the new features.

Changed for This Release

The following documentation is critical to understanding and using the Pervasive.SQL V8.5 release.

Table 1-6 Updated Documentation for Security Release Beta
Manual
Description
This book, What's New
The most important book in understanding the new features. It contains a list of all features and describes how to migrate to the new security models.
Getting Started with Pervasive.SQL (Server and Workgroup editions)
The procedures for the installation of Windows Server and Workgroup engines as well as the NetWare server installation chapters have been updated with the new installation program. See Improved Installation Programs for more information.
Advanced Operations Guide
  • Updates to the Converting MicroKernel Data chapter relevant to the updated Rebuild utility. (Rebuild utility help links to this location).
  • Security information from What's New is merged into this manual
  • Updated information for Function Executor utility
Status Codes and Messages
New status codes related to security (starting at code 170).
Pervasive.SQL User's Guide
  • New option for PSA "View Modules" module.
  • New security procedures

New Features

The following new features are present in the Pervasive.SQL V8.5 documentation set.

DataExchange Included if Installed

If you have the Pervasive DataExchange (DX) product, the documentation for that product is now integrated dynamically into the Pervasive.SQL documentation set (pvswdocs.chm). You will see DX documentation in the contents window, and all DX publications are integrated into the Index and Search features. You can still use the DX documentation set independently if you choose by loading the dxdocs.chm help file.

Checking for Topic and Publication Revisions

There is a new HTML link on the bottom of every page in the documentation set labeled Check for Revisions. When you click this link, the help file loads the Pervasive Library (http://www.pervasive.com/library) in a new browser window and performs the following actions:

  • Passes meta information about the revision of the manual you have installed on your system. This information was written into the manual itself before it was shipped. None of this information is obtained by reading information from your computer, and no personal information of any kind is transmitted to Pervasive Software during this process.
  • The Pervasive Library looks up the corresponding manual and compares the meta information from your manual against the meta information of the copy on the Pervasive Library.
  • If the revision levels match, the Pervasive Library informs you that you are up-to-date.
  • If the revision levels do not match, Pervasive Library informs you that your topic or publication has a new revision level available, and provides you with a link to download this new publication.

This revisioning system is built into all manuals except:

  • Pervasive Products and Services
  • Glossary
Prev
Improved Installation Programs 		Up
Check for Revisions 		Next
Utility Updates for Security


Utility Updates for Security

An Overview of the Updates to the Utilities in Pervasive.SQL V8

To aid in the configuration of the new security features, this release provides a number of utility updates.

The following are the sections found in this chapter:

  • Overview of Utility Changes
  • Help for New Utilities
Prev
Documentation Updates
Check for Revisions 		Next
Overview of Utility Changes


Overview of Utility Changes

This release includes some new utilities as well as a variety of updates to existing utilities. This section describes these changes.

New GUI Rebuild Utility

The GUI version of the Rebuild utility has been redesigned for greater usability and increased functionality. Some of the new features include:

  • Wizard interface that guides you through the Rebuild process
  • Ability to specify file version using the utility and not via the setting in Pervasive Control Center as previously required

The following shows the options page of the new Rebuild utility:

Figure 2-1 New Rebuild Utility - Options Screen

See the following topics in Advanced Operations Guide for information on using the new Rebuild utility:

  • Rebuild Utility Concepts
  • Rebuild Utility GUI Reference
  • Rebuild Utility Tasks

New Command Line Rebuild Utilities

A command-line version of the Rebuild utility has been added for Windows and Linux (on NetWare, BREBUILD already existed). For complete documentation on this utility, see the following topic in Advanced Operations Guide: CLI Tasks .

New Database Password Utility

To allow for users to change their own passwords without needing the assistance of an administrator, this release adds a new utility called pvdbpass that is included with every installation, including the client requester.

Syntax

pvdbpass database username [-server name] [-port number]

or

pvdbpass database username password newpassword
    [-server name] [-port number]

database | this is the name of the database the user belongs to. If the database resides on another server use the notation server\database
username | the name of the user for which the password is being changed
password | The existing password for the user.
newpassword | New password for the user. If the password does not begin with a non-alphabetic character, the password should be enclosed in single quotes (') when providing the new password AND when confirming it.
servername | Optional. Server name on which the database is defined. If you do not specify this option, the local machine is assumed
port | Optional. TCP port on which the SQL engine running on servername is listening. If you do not specify this option, the default port 1583 is assumed

Remarks

Specify both the current and new password to avoid being prompted.

Examples

To change the Master user's password and be prompted:

pvdbpass demodata Master

To change an existing password to one that does not start with an alphabetic character (use single quotes):

pvdbpass demodata Joe oldpassword '123'

To change a password on a remote server:

pvdbpass demodata Joe oldpass newpass -server finance1

Updates to Existing Utilities

The following utilities that were shipped with previous versions of Pervasive.SQL have modifications for the security features.

BUTIL

The BUTIL utility has been enhaced to support the new security models by accepting the following new parameters:

/UID | userid
/PWD | password
/DB | database to which the file belongs (optional)

Pervasive Control Center

  • Maintain Named database dialog updated to include security definition for the entire database.
  • Users and Groups dialog enhanced with 'Permissions' tab to define access rights with more granularity.

Updates to Wizards and Dialogs

The following utilities that were shipped with previous versions of Pervasive.SQL have modifications for the security features.

Create Database Wizard

This wizard is not updated, but it is important to note that when you create a new database using the wizard and you want to select one of the new security models (Mixed or Database), then you need to specify Advanced features.

When you select Use advanced settings, an extra dialog displays that allows you to configure more properties from the default values. One of these properties is the setup of the database name. Since the default is to use the Classic security policy, creating a new database in Mixed or Database mode requires you to click Create as shown in the following diagram.

Maintain Named Databases Dialog

This dialog assumes an important role in the new security features for two reasons.

  • Using this dialog, you can tie together a set of Btrieve files, perhaps scattered among several directories, into a cohesive Btrieve database with a single set of security rules.
  • Using this dialog, you define the security model you will use for the Btrieve database.

The following shows the new field on the Maintain Named Database dialog:

Figure 2-2 Maintain Named Databases Dialog - Added Security Selection

Use the Security Policy control to select the security policy you want for the database. To understand the different security models, see Available Models .

Users and Groups Permissions Dialogs

The users and groups dialog is improved from the previous release in these ways:

  • it functions also for Btrieve databases as well.
  • There are new checkboxes for "database level rights", which are highlighted in red in the following diagram:

Figure 2-3 Users Permissions Dialog

In a similar fashion, the Groups dialog now has database level rights added to the selections.

Figure 2-4 Group Permissions dialog

These database rights checkboxes are the GUI equivalent to the new SQL statements described in Improved SQL Support . For example, checking the Select database right in the preceding diagram would be the same as the following SQL statement run in the DEMODATA database:

GRANT Select on * to ACCOUNTING
Prev
Utility Updates for Security 		Up
Check for Revisions 		Next
Help for New Utilities


Help for New Utilities

To receive help for using the utilities, click Help. If no help button exists, press F1 when the focus is on the area of the utility for which you want to learn more about.

For complete documentation on the utility features that existed prior to this release candidate, see Pervasive.SQL User's Guide and Advanced Operations Guide.

Prev
Overview of Utility Changes 		Up
Check for Revisions 		Next
Changes to Programming Interfaces for Security


Changes to Programming Interfaces for Security

A Reference of New Features in the SDK to Support Security Features

The purpose of this chapter is to explain in detail the changes to the programming interfaces that have been added to the MicroKernel Database Engine (MKDE) to support the new security features.

This chapter contains complex technical material and is intended for expert-level users.

  • Btrieve Updates Overview
  • Database URIs
  • New Btrieve Login Operation
  • Changes to Other Btrieve Operations
  • ActiveX Updates
  • Distributed Tuning Interface
  • New Status Codes
  • JDBC Improvements

Prev
Help for New Utilities
Check for Revisions 		Next
Btrieve Updates Overview


Btrieve Updates Overview

The new Btrieve login API brings the database concept to the Btrieve interface. It allows access to a Btrieve database without requiring the user to have operating system rights to the physical location of the database files.

The purpose of this chapter is to define the Btrieve login APIs and the database URIs that can be used as parameters in the API to access a Btrieve database resource.

To provide additional convenience and support for the Btrieve database concept discussed in New Security Models for Btrieve Applications , this release offers new Btrieve operations for explicit database Login and Logout. In addition, this release offers the ability to login implicitly to a database with a Create or Open operation using a new type of entry in the Btrieve key buffer.

Backwards Compatibility Issues

  • The new requester works with previous versions of Pervasive.SQL. However, status 1 (invalid operation error) is returned by the client requester for Btrieve Login/Logout operations when connecting to engines previous to this release. It is up to the application to decide whether to proceed.
  • The database URI will not be recognized by engines previous to this release.
Prev
Changes to Programming Interfaces for Security 		Up
Check for Revisions 		Next
Database URIs


Database URIs

A key concept in using the Btrieve Login API or the implicit login functionality via the Create or Open functions is the database URI (Uniform Resource Indicator). It provides a syntax to describe the address of a database resource on a server.

This section describes the syntax and semantics of the URIs used in Btrieve APIs.

Syntax

The basic syntax of the URI is

access_method://user@host/dbname?parameters

Table 3-1 Elements of a Database URI
Element
Definition
access_method
The method used to access the database. Currently, only btrv is supported.
user
An optional user name. The password for the user is specified in parameters if needed.
host
A server where the database is located. The local machine is assumed if host is not specified.
dbname
An optional database name, which corresponds to an entry in the DBNAMES.CFG file for the Pervasive.SQL V8 database engine. If no database name is specified, then the default database "DefaultDB" is assumed.
parameters
Additional parameters, which are delimited by the & (ampersand) character.
  • table=table - allows you to specify a specific SQL table name.
  • dbfile=file - Name of a file whose location is relative to the current database's data file locations entry in DBNAMES.CFG. The full file name will be resolved by the engine handling the request; the Pervasive.SQL client (MIF) will not manipulate <dbfile> in any manner. The use of drive letters is discouraged but if present will be assumed to be server side and not client side drive letters. Embedded spaces are permitted - they will be escaped by the engine. Using a UNC path is also permitted.
  • file=file - allows you to specify a specific data file name. Embedded spaces are permitted in this parameter.
  • pwd=password - clear text password. The Pervasive.SQL client (MIF) will change clear text passwords into encrypted passwords before transmission across the wire.
  • prompt=[ yes | no ] Tells the Win32 requester how the application wants to handle the login dialog box pop-up when the MicroKernel returns status 170 (Login failed due to missing or invalid user name) or 171 (Login failed because of invalid password). If prompt=yes is specified, the requester will always display the login dialog even if the Prompt for Client Credentials setting is Off. If prompt=no is specified in the URI, the requester assumes that the application wants to receive the status 170/171 directly and does not want the requester to display the dialog. This is useful if you want your applications to handle the prompting for credentials in response to any 170 or 171 status codes. Values other than yes or no are ignored and the requester displays the login dialog based on the Prompt for Client Credentials setting. This option is ignored on Linux and NetWare machines that are acting in the role of a client.

Special Characters

As with any URI, certain non-alphanumeric characters have special significance in the URI syntax. If you wish to use one of these characters within one of the fields in the URI, you must use an escape sequence to identify the character as actual text rather than a special character. An escape sequence is simply another special character or character combination that represents the plain text equivalent of a special character. The table below shows the special characters supported by Btrieve URI syntax, and their associated escape sequences (represented by the percent sign and the hexadecimal value for the specified character). For more information on special characters in URIs, see the RFC1738 specification at http://www.faqs.org/rfcs/rfc1738.html.
Table 3-2 Special Characters in a Database URI
Character | Meaning | Hexadecimal value
/ | Separates directories and subdirectories | %2F
? | Separates the base URI from its associated parameters | %3F
% | Specifies a special character | %25
# | Indicates a bookmark | %23
& | Separates the parameters in the URI | %26
"," | Indicates whole part | %22
= | Separates a parameter and its value | %3D
space | No special meaning, but is reserved. | %20
: | Separates host from port (reserved, but not currently supported) | %3A

Although the space character is reserved in the URI specification, it can be used without quotes and without escape sequencing because it is not used as a delimiter. The other symbols in the table above are used as delimiters and therefore must be escaped.

Examples

This section shows examples of URIs using escape sequences to identify special characters used within the field values.
Table 3-3 URI Examples with Escape Sequences
URI | Meaning
btrv://Bob@myhost/demodata?pwd= This%20Is%20Bob | User "Bob" with password "This Is Bob".
btrv://Bob@myhost/demodata?pwd= This Is Bob | User "Bob" with password "This Is Bob".
btrv://myhost/demodata?file=c:/program%20files/pvsw/mydb/c.mkd | The %20 represents a space character. File to be opened is "C:\Program Files\pvsw\mydb\ c.mkd"
btrv://Bob@myhost/demodata?pwd= mypass%20Is%20%26%3f | User Bob with password "mypass Is &?"

Remarks

Some or all of the syntax elements user and ?parameters may be excluded.

Note that an empty user name or password is different than no user name or password. For example, btrv://@host/ has an empty user name, while btrv://host/ has no user name, and
btrv://foo@host/?pwd= has a user name of "foo" with an empty password.

Examples

A URL (or "Uniform Resource Locator") is simply the address of a file or resource on the Internet. The database URI uses the same notion to address a database on a server. This section describes the syntax and semantics of URIs for Pervasive.SQL databases, particularly using the Btrieve access.
Table 3-4 Examples of Btrieve URIs
Example | Meaning
btrv://myhost/demodata | Database "demodata" on server "myhost."
btrv:///demodata | Database "demodata" on local machine.
btrv://Bob@myhost/demodata | User Bob without a password accessing database "demodata" on server "myhost."
btrv://Bob@myhost/mydb?pwd=a4 | User Bob with password "a4" accessing database "mydb" on server "myhost."
btrv://myhost/demodata?table=class | Unspecified user accessing database table named "class" in database "demodata" on server "myhost."
btrv://myhost/?table=class | Unspecified user accessing database table named "class" in default database ("DefaultDB") on server "myhost."
btrv://myhost/mydb?file=c:/mydb/a.mkd | Unspecified user accessing data file c:/mydb/a.mkd under database "mydb" on server "myhost."
btrv://myhost/mydb?dbfile=class.mkd | Unspecified user accessing data file class.mkd within first data directory location specified for database "mydb."
btrv://myhost/demodata?dbfile=class.mkd | File class.mkd under data path of "demodata" on server "myhost". Because the file name is specified with dbfile= (and not file=) the P.SQL requester will not normalize class.mkd. Only the server engine will normalize class.mkd into a full path.
btrv://myhost/demodata?dbfile=c:\pvsw\demodata\ class.mkd | File c:\pvsw\demodata\class.mkd on server "myhost" and in database "demodata". Because the file name was specified with dbfile= the drive letter c: is meaningful only to the engine on the server. Drive letter c: is not associated with the client side.

Prev
Btrieve Updates Overview 		Up
Check for Revisions 		Next
New Btrieve Login Operation


New Btrieve Login Operation

This release offers an entirely new Btrieve operation, Login/Logout (78). Login/Logout allows Btrieve users to login to a Btrieve database to set the security context for authorized users. The details regarding this new operation are provided below.

Database Login/Logout

The Login/Logout operation (B_LOGIN/B_LOGOUT) allows a user to specify his/her user credentials and obtain authentication and authorization tokens from the database engine. This operation also allows the user to reset his/her login credentials so that they must be entered again to gain access to the database.

Parameters

 | Op Code | Pos Block | Data Buf | Data Buf Len | Key Buffer | Key Number
Sent | ü |  |  |  | ü | ü
Returned |  |  |  |  |  |

Prerequisites

  • The database name and the user ID must be pre-defined.

Login Procedure

  1. Set the Operation Code to 78.
  2. Set the key number to 0.
  3. Place the server name, database name, user ID, and password in the key buffer in the form of a database URI. (See Database URIs .)

Logout Procedure

  1. Set the Operation Code to 78.
  2. Set the key number to 1.
  3. Place the server name, database name, user ID, and password in the key buffer in the form of a database URI. (See Database URIs .)

Result

If the Login or Logout operation is successful, the database engine returns status 0; otherwise, one of the following status codes may be returned:
1 | Invalid operation
172 | Database name not found
3103 | Unknown server

Notes

The combined length of the database URI must be less than 255 bytes. This is due to the maximum size of the key buffer.

The Login operation has a performance cost. You should not code applications to login and logout on every file. Instead, login once to a database at the beginning of a session, then logout when the database work is complete.

Positioning

The Login/Logout operation has no effect on any file currency information.

Prev
Database URIs 		Up
Check for Revisions 		Next
Changes to Other Btrieve Operations


Changes to Other Btrieve Operations

By adding support in the key buffer for Database URIs, the Open and Create operations in this release provide the ability to allow users to login implicitly to a Btrieve database.

Btrieve Open (0)

The Open operation has been enhanced to accept Database URIs in the Btrieve key buffer when opening a file. The Btrieve requester and MicroKernel Database Engine parse the database URI and resolve it to the correct server and file name using database catalog. For more information, see Database URIs .

Notes on Btrieve Open

  • if user and password are not specified in the database URI, either the user is currently logged into the database through the Btrieve Login API, or credentials are not required to access this database.
  • If dbname is not specified in the database URI, the current database is assumed. For more information about the default database, see Database URIs and The Default Database and the Current Database .

New Status Codes for Open Operation

The following status codes can now be returned by the Open operation:
170 | Database login required
171 | Database login failed
172 | Database name not found
174 | An explicit login is required

Create (14)

The Create operation has been enhanced to allow the ability to specify a database URL in the Btrieve key buffer when creating a file. The Btrieve requester and MicroKernel Database Engine parse the database URL and resolve it to the correct server and file name using database catalog. For more information, see Database URIs .

Notes on Btrieve Create

  • The user must have CREATE permission to the database.
  • The Create operation will not automatically add metadata into FILE.DDF or DBNames.CFG. If the directory specified in "file=" is not one of the file locations in DBNames.CFG for its database, or subdirectory of any file locations, the operation will fail with status 173 (directory error).
  • "file=" and "dbfile=" processing are as described in the Btrieve URI Syntax and Use design document.
  • If the server indicates that it requires an explicit Login, then the requester will return an error code to an Open or Create without using a URL connection string or having first called Login.

New Status Codes for Create Operation

The following status codes can now be returned by the Create operation
170 | Database login required (unknown user id)
171 | Database login failed (bad password)
172 | Database name not found
173 | Already logged in
174 | An explicit login is required
175 | Wrong URI format

Reset (28)

A Reset operation (28) will automatically logout from current active databases.

Prev
New Btrieve Login Operation 		Up
Check for Revisions 		Next
ActiveX Updates


ActiveX Updates

There are no ActiveX updates available for this release that utilize the new Btrieve Login API. The Pervasive.SQL SDK will be updated following this release.

Prev
Changes to Other Btrieve Operations 		Up
Check for Revisions 		Next
Distributed Tuning Interface


Distributed Tuning Interface

The changes to Distributed Tuning Interface (DTI) for this release are:

New Flags for Security

allow you to specify a security model when creating or modifying a database, and to allow you to query the security model of an existing database. The changes take the form of new database flags and affect the following functions:

  • PvCreateDatabase()
  • PvModifyDatabase()
  • PvGetDbFlags

The new database flags for these functions are:

Flag | Hex | Meaning
P_DBFLAG_DBSEC_AUTHENTICATION | 0x00000008 | Database Security Authentication
P_DBFLAG_DBSEC_AUTHORIZATION | 0x00000010 | Database Security Authorization

The following table indicates how to specify a security model in a new database, or to interpret the security model of an existing database.

This Flag Combination | Represents this Security Model
No flags | Classic
P_DBFLAG_DBSEC_AUTHENTICATION | Mixed
P_DBFLAG_DBSEC_AUTHENTICATION + P_DBFLAG_DBSEC_AUTHORIZATION | Database

Catalog Functionality

If you use the PvCreateDictionary() or PvOpenDictionary() functions, you should note that the functionality to open a dictionary based on a path will be deprecated in the future. Currently, you can use these functions as they were originally designed, including the use of enforced SQL security by rights defined in USER.DDF and RIGHTS.DDF.

Prev
ActiveX Updates 		Up
Check for Revisions 		Next
New Status Codes


New Status Codes

The following are new status codes related to the security features. These will be added to Status Codes and Messages after the Beta release.

Table 3-5 New Status Codes
Code
Meaning/Resolution
170
Database login required - authentication failed due to a wrong or missing username.
171
Database login failed - authentication failed due to a wrong or missing password.
172
Database name not found.
173
Already Logged In.
A Btrieve login request failed because the client is already logged into the specified database.
174
Logout Failed.
A logout can fail if you are not logged in to a database or there are remaining open file handles to the database when the logout is attempted.
175
Wrong database URI format.
The URI connection string was formatted incorrectly. The first five bytes should be "btrv:".
176
File and Table Name Not Specified in URI.
An Open was issued using a URI connection string that contained neither a file name nor a table name.
177
Table Not In Database.
An Open was issued using a URI connection string that contained no file name, and a table name that does not exist in FILE.DDF.
178
Directory Not In Database.
An Open was issued using a URI connection string that contained a full path file name that references a directory that does not exist as one of the data directories for the database.
Add the directory to the database using the Maintain Named Databases dialog in the Pervasive Control Center.
5000
Client requires encryption
Server either does not support encryption or it is not configured correctly.
5001
Server requires encryption
Client either does not support encryption or it is not configured correctly.

Prev
Distributed Tuning Interface 		Up
Check for Revisions 		Next
JDBC Improvements


JDBC Improvements

This section describes the improvements offered in this release of the Pervasive JDBC driver.

Encryption Support

With the addition of wire-encryption as a feature of the communications layer, support for two new connection URL options has been added. Table 3-6 explains the new options.

Table 3-6 New JDBC Connection URL Options
Option
Description
encrypt
Determines whether the JDBC driver should use Encrypted Network Communications, also known as wire encryption.
Values: always, never
If this option is not specified, the driver reflects the server's setting, the equivalent of the value "if needed."
If the value always is specified, the JDBC driver will use encryption or else return an error if wire encryption is not allowed by the server. If the value never is specified, the JDBC driver will not use encryption and will return an error if wire encryption is required by the server.
encryption
Determines the minimum level of encryption allowed by the JDBC driver.
Values: low, medium, high
Default: medium
The meaning of these values depends on the encryption module used. With the default encryption provider, these values correspond to 40-, 56-, and 128-bit encryption, respectively.

Requirements

To use wire encryption with the JDBC driver, another JAR file is required to be in your classpath. This JAR, jpscs.jar, is installed in the PVSW\bin directory by default. This JAR uses Java Cryptography Extensions (JCE) which comes standard with Java 2 SDK v1.4. It is also available as a separate download for Java 2 SDK 1.3 and 1.2 (see http://java.sun.com/products/jce).

Example

The following example specifies that the JDBC driver always requires encryption, and it requires at least the low level of encryption, or it returns an error code.

jdbc:pervasive://host/demodata?encoding=UTF-8&encrypt= always&encryption=low

Standard URL Syntax

The JDBC connection URL syntax has been improved to allow for more standard syntax. This improvement is best demonstrated by example:

Old Syntax
jdbc:pervasive://host/demodata;encoding=UTF-8;encrypt=always

  •  (notice the non-standard semi-colon separator ";")
New Syntax
jdbc:pervasive://host/demodata?encoding=UTF-8&encrypt=always

  •  (notice the standard URL separator "?" and parameter separator "&")

While both forms will be supported for the foreseeable future, the new syntax is recommended because it is more standard.

Prev
New Status Codes 		Up
Check for Revisions 		Next
Setting Up Btrieve Security


Setting Up Btrieve Security

A Set of Procedures and Checklists for Successfully Integrating the New Security Features

To aid in the configuration of the new security features, this release provides a number of utility updates.

The following are the sections found in this chapter:

  • Planning Your Security Scheme
  • Before you Begin
  • Btrieve Security Quick Start
  • Configuring Security
  • Creating Database Users and Privileges
  • Security Issues

Prev
JDBC Improvements
Check for Revisions 		Next
Planning Your Security Scheme


Planning Your Security Scheme

After you install this product, 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 new 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 4-1 Feature Comparison of Security Policies
Feature | Classic | Mixed | Database
Users' data access rights are derived directly from OS users' file system rights | ü |  |
Users' data access rights are unrelated to users' file system rights if OS-level file access rights are removed for those users. |  | ü | ü
Database user accounts are derived directly from OS user accounts; Database accepts successful OS login as valid database user | ü | ü |
User must log into database separately from logging into computer; OS user accounts have no bearing on authorization or access. |  |  | ü
Supports automatic pop-up dialog for entering database user name and password from any Pervasive-based Windows application when server configuration permits. |  |  | ü

The following table shows how rights are assigned using the various security policies.

Table 4-2 Permissions Defined Comparison of Security Policies
Feature | Classic | Mixed | Database
To assign individual-level rights, Administrator must set up separate OS and database user accounts for each user. |  | ü | ü
To assign a set of default privileges for a set of users in your organization, administrators can define a group with a set of privileges, and then add users to that group. Users that are members of groups other than PUBLIC cannot have individual rights. |  | ü | ü
To assign a set of default privileges for all users who are not members of a group, administrators can assign the privileges to the PUBLIC group. All users who are not explicitly a member of a group are considered implicit members of the PUBLIC group. A user who is a member of PUBLIC inherently possesses the sum of user-level and PUBLIC rights. |  | ü | ü
All user rights are related to OS file-level rights. | ü |  |

Under Database security, database user accounts are completely unrelated to OS user accounts and OS-level file access rights can and should be removed in order to ensure data privacy.

In contrast, under Classic security, a user who succesfully logs into the computer has access to the database contents, at whatever level of filesystem 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 login 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 filesystem 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. Note that some government regulations such as HIPAA specifically prohibit this kind of behavior. Consult your legal advisor for issues regarding compliance with government regulations.
  • 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 (PUBLIC account defines permissions for all users).
  • You do not need to have a variety of data access rights that vary from each user's filesystem 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 to 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. Or, all your users have the same level of permissions, and you can address their needs by assigning permissions to one database user account, 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 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 can also achieve this goal using the Mixed security policy.
  • 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 Simplest Way to Secure your Data Files at the Operating System Level

If your primary concern is simply to implement a Btrieve security scheme such that your database users do not have rights to copy, overwrite, or delete the data files in the operating system, then this section describes an overview of what you need to do.

  1. Install Pervasive.SQL 8.5 database engine.

    As long as you do not use wire encryption, V8 clients are compatible with the V8.5 engine. You are not required to upgrade the clients.

  2. In PCC, use the Maintain Database Names utility to add the data file locations of your Btrieve files into the DefaultDB database.

    You do not need to enter every directory, just the highest level directory that is common to all data files.

  3. Enable security on DefaultDB using either the Database Properties dialog in PCC, or the SET SECURITY statement in SQL.
  4. When logged into DefaultDB as the Master user, grant rights to the PUBLIC user at the database level. For example, if you want to grant all rights to all authenticated users, you would use the statement: GRANT ALL ON * TO PUBLIC. This statement will give all users the same rights to the data.

    If you need to grant users varying rights, then you must create group accounts (if applicable) and individual user accounts using the GRANT statement in SQL or using the Users and Groups dialog in PCC.

  5. Use the Maintain Database Names dialog in PCC to set the Btrieve security policy for DefaultDB to Mixed.
  6. Secure the data files in the operating system according to your operating system instructions. You can now deny operating system users from having any rights to the data files, without affecting their ability to access the data through the database engine.

For step-by-step instructions for this procedure, see Btrieve Security Quick Start .

Prev
Setting Up Btrieve Security 		Up
Check for Revisions 		Next
Before you Begin


Before you Begin

Setting up Btrieve security 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 pathname. 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 from the default (PUBLIC) permissions. 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.

  1. Preparation. As specified above in Before you Begin, 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?
  2. 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 Btrieve files , or To create a new database for use with your Btrieve files

  3. Turn on security. On the Database Properties dialog, enter a Master user password to enable security.

    For details on this step, see To turn security on for a Database .

  4. Create users and permissions. Using SQL statements or the Groups and Users dialog in 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 grant the same rights to all users .

  5. Using the Maintain Named Databases dialog, set the Btrieve Security policy for your database to Mixed or Database. Then restart the database engine.

    For details on this step, see To Change Security Policy for a Database .

  6. Secure the data files in the operating system. 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.

    Caution
    If you do not secure the file access to the directories containing Pervasive.SQL data, you leave it vulnerable to operation ssystem users on that system. For complete

Prev
Planning Your Security Scheme 		Up
Check for Revisions 		Next
Btrieve Security Quick Start


Btrieve Security Quick Start

This section provides step-by-step instructions on the fastest, easiest way to secure your Btrieve data files in the operating system while still allowing database users to access the data.

When this procedure is complete, you can revoke operating system user rights to the data files without affecting database user rights to access the data through an application.

Note
You must be logged into the computer where the database engine is installed, as an operating system user with administrative rights or as a user who is a member of the Pervasive_Admin security group.

  1. Start Pervasive Control Center (PCC). If you need instructions on starting PCC, see To start Pervasive Control Center .
  2. If the database engine you wish to work with is not registered with PCC, register it now. If you need instructions on how to register a database engine with PCC, see Pervasive.SQL User's Guide.
  3. In PCC, double-click the database engine you wish to work with. Then right-click the Configuration folder underneath the database engine name, and choose Maintain Named Databases.
  4. In the Maintain Named Databases dialog, click on the DefaultDB database to select it. Then click the plus button [image] . Now, in the space provided, enter or select a directory containing your Btrieve files. If your files are spread over many directories, specify a high-level directory that they all have in common. You can specify C:\ if necessary, but realize that doing so will include all Btrieve files on the C:\ drive in DefaultDB. When finished, click OK.

    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. Now you must enable security on DefaultDB. You can perform this task by issuing a SQL statement or using either the Database Properties dialog in PCC.

    To use the Database Properties dialog, right-click on DefaultDB in the left-hand pane of PCC. From the drop-down menu, choose Properties. In the dialog that appears, click on the Security tab. Enter the password that you wish to use for the Master user, twice as prompted. Click OK.

    To turn on security using a SQL statement, issue the following statement within an ODBC client application (you cannot use SQL Data Manager):

    SET SECURITY = password

    where password is the password you choose for the Master user.

    Now security is turned on, but access is based on OS user rights by default, so your users currently have the same access that they had before. The next step will address this situation.

  6. In SQL Data Manager, grant rights to the PUBLIC user at the database level. For example, if you want to grant read-only rights to all authenticated users, you would use the statement: GRANT SELECT ON * TO PUBLIC. This statement will give all users read-only rights to the data. To give all users read and write access to the data, use the statement: GRANT ALL ON * TO PUBLIC.

    If you need to grant individual users varying rights, then you must create group accounts (if desired) and individual user accounts using the GRANT statement in SQL or using the Users and Groups dialog in PCC. For information on creating groups and users, see Advanced Operations Guide.

  7. Now, return to the left-hand pane of PCC, right-click on Configuration for the database engine you are working with, and choose Maintain Named Databases.

    In the Maintain Named Databases dialog, click on the DefaultDB database to select it. Click on the Btrieve Security policy drop down list and change the value to Mixed. Click OK.

     Caution
    Do not change the Btrieve Security Policy setting until you have completed step 6 as instructed. If you have not created user accounts or granted rights to PUBLIC, changing the security policy will prevent all your users from accessing the data.

    You have now granted login access only to those users who are authenticated by the operating system, and you have specified that the access rights of those users are defined by the permissions you granted to them in the database.

  8. Restart the database engine.

    On NetWare, reboot the server. On Windows server, stop and restart the Pervasive.SQL services. For Workgroup engine, right-click the tray icon and stop the engine, then restart it.

  9. Secure the data files in the operating system according to your operating system instructions. You can now deny operating system users from having any rights to the data files, without affecting their ability to access the data through the database engine.

    Caution
    Be sure to secure the data files in the operating system. If you do not perform this step, the users still can access the files through the operating system with the same level of permissions that they had prior to this procedure. You must revoke the users' operating system privileges to the data files if you want to prevent users from being able to delete or modify the files directly.

Prev
Before you Begin 		Up
Check for Revisions 		Next
Configuring Security


Configuring Security

Once you have gathered the necessary information and decided on a security scheme and a database to use, you can follow these instructions to set up security.

IMPORTANT: This section is only needed if you wish to use Mixed or Database security. Pervasive.SQL uses Classic security automatically unless you change it to a different policy.

Note
To set up security, you must have administrative permissions on the operating system where the database engine is located, or you must be logged in to the computer as a user who is a member of the Pervasive_Admin security group.

You must start by either creating a new database or selecting an existing database to use with your Btrieve files. Unless you have a specific need to create a new database or use your own existing database, Pervasive recommends using the pre-defined DefaultDB database.

Regardless of whether you wish to use an existing database or create a new one, you should use Pervasive Control Center (PCC) to get started.

This section contains the following procedures:

  • To start Pervasive Control Center
  • To create a new database for use with your Btrieve files
  • To use an existing database, including the pre-defined DefaultDB, with your Btrieve files
  • To Change Security Policy for a Database

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.

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

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 4-1. Enter a name for the database, specify a location for the database definitions, and check Use Advanced Settings. Click Next.

Figure 4-1 Create Database Wizard

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

Figure 4-2 Specify DSN Attributes

  4. In the next screen, shown in Figure 4-3, click the plus button [image] . 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 4-3 Data File Locations

  5. You should now be back in the screen shown in Figure 4-2. Click Next to display the confirmation screen.
  6. 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 expose 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 4-4.

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

Figure 4-4 Maintain Named Databases

  3. Click the Add Data File Location button, circled in Figure 4-4. 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.

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

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 Creating Database Users and Privileges .

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.

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 Security Policy for a Database .

To Change 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 4-5.

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

Figure 4-5 Maintain Named Databases - Changing Security Policy

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

  4. If you are prompted to confirm your changes, click OK again.
  5. Restart the database engine for these changes to take effect.
Prev
Btrieve Security Quick Start 		Up
Check for Revisions 		Next
Creating Database Users and Privileges


Creating Database Users and Privileges

This section explains how to create users and assign privileges for databases. User accounts and associated file or table privileges can be created by two different methods:

  • SQL statements
  • Users and Groups window in PCC

You must be logged into the database as Master before you can create users or assign privileges by either method.

Note
If your database uses Mixed security, you must create database user names that are the same as the operating system user names used to login to the computer where the database engine is installed.

General Tasks

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.

Creating Database Users

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 .

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 4-6 Creating a New Database User With PCC

  4. 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 4-7 Entering a New User

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

Figure 4-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 4-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 4-8:

GRANT Update on * to Joe#

GRANT Insert on Customers to Joe#

GRANT Insert on Employees to Joe#

  6. It is also important to note that Joe could also inherit rights from the default group PUBLIC that you could not see in Figure 4-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 4-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

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 4-9 . Any right that is not checked is not granted to default new users.
Prev
Configuring Security 		Up
Check for Revisions 		Next
Security Issues


Security Issues

This section lists special cases when using Pervasive.SQL security.

Mixed/Classic Mode and Windows XP Professional

A local security policy in Windows XP Professional "Sharing and security model for local accounts" can adversely affect network logins.

Under the default policy, all network logins to local accounts are authenticated as Guest, no matter what credentials were supplied. This causes your authorization for databases using Mixed or Database mode not to match your OS credentials and can prevent data access you intended to authorize.

To resolve this condition, ensure that your policy for "Sharing and security model for local accounts" is set to Classic, which authenticates users as themselves. Windows 2003 already has a default setting of Classic. Windows XP Professional has a default setting of Guest, and Windows 2000 and earlier releases do not have this security policy. To find this policy in Windows XP, browse to Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options.

.

Prev
Creating Database Users and Privileges 		Up
Check for Revisions 		Next