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)