Using Caché SQL
Users, Roles, and Privileges
[Back] [Next]
Go to:

Caché SQL provides security through the use of users and their granted privileges. Caché SQL enforces privilege checking for ODBC, JDBC, Dynamic SQL, and the SQL Shell interface. Embedded SQL statements do not perform privilege checking; it is assumed that applications using Embedded SQL will check for privileges before using Embedded SQL statements.

This chapter discusses the following topics:
A Caché SQL user is the same as a user defined for Caché security. You can define a user using either SQL commands or the Management Portal.
If a user has SQL table privileges, or general SQL privileges, then roles granted or revoked on the user’s Roles tab do not affect a user’s access to tables through SQL-based services, such as ODBC. This is because, in the SQL-based services, table-based privileges take precedence over resource-based privileges.
The [Home] > [Security Management] > [Roles] page provides a list of role definitions for a Caché instance. To view or change details on a particular role, select the Edit link for the role. On the [Home] > [Security Management] > [Roles] > [Edit Role] page that appears, there is information regarding the roles privileges and which users or roles hold it.
The General tab lists a role’s privileges for Caché security resources. If a role only holds SQL privileges, the General tab’s Resources table lists the role’s privileges as “None defined.”
The SQL Privileges tab lists a role’s privileges for Caché SQL resources, where a drop-down list of namespaces allows you to view each namespace’s resources. Because privileges are listed by namespace, the listing for a role holding no privileges in a particular namespace displays “None.”
You should define privileges using roles and associate specific users with these roles. There are two reasons for this:
  1. It is much more efficient for the SQL Engine to determine privilege levels by checking a relatively small role database than by checking individual user entries.
  2. It is much easier to administer a system using a small set of roles as compared with a system with many individual user settings.
For example, you can define a role called “ACCOUNTING” with certain access privileges. As the Accounting Department grows, you can define new users and associate them with the ACCOUNTING role. If you need to modify the privileges for ACCOUNTING, you can do it once and it will automatically cover all the members of the Accounting Department.
A role can hold other roles. For example, the ACCOUNTING role can hold the BILLINGCLERK role. A user granted the ACCOUNTING role would have the privileges of both the ACCOUNTING role and the BILLINGCLERK role.
You can also define users and roles with the following SQL commands: CREATE USER, CREATE ROLE, ALTER USER, GRANT, DROP USER, and DROP ROLE.
Privileges are assigned to a user or role. Caché SQL supports two types of privileges: administrative and object.
Administrative privileges cover the creation, altering, and deleting of types of objects, such as the permission to create tables. They also determine whether a user can apply %NOCHECK, %NOINDEX, %NOLOCK, or %NOTRIGGER restrictions when performing an INSERT, UPDATE, INSERT OR UPDATE, or DELETE. Assigning the %NOTRIGGER administrative privilege is required for a user to perform a TRUNCATE TABLE.
Object privileges cover access to specific named objects (in the SQL sense of the word: a table, a view, a column, or a stored procedure). Table-level object privileges provide access (%ALTER, DELETE, SELECT, INSERT, UPDATE, EXECUTE, REFERENCES) to the data in all columns of a table or view, both those columns that currently exist and any subsequently added columns. Column-level object privileges provide access to the data in only the specified columns of a table or view. You do not need to assign column-level privileges for columns with system-defined values, such as RowID and Identity. Stored procedure object privileges permit the assignment of EXECUTE privilege for the procedure to specified users or roles. For further details, refer to the GRANT command.
You can grant privileges in the following ways:
Privileges are namespace-specific.