Using Caché SQL
Users, Roles, and Privileges
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
In SQL you use the CREATE USER
statement to create a user. This simply creates a user name and user password. You must use the GRANT
statement to assign privileges and roles to the user. You can use the ALTER USER
and DROP USER
statements to modify existing user definitions.
In the Management Portal
Select System Administration
, then select Users
([Home] > [Security Management] > [Users]
). At click the Create New User
button at the top of the page. This takes you to the General
tab of the [Home] > [Security Management] > [Users] > [Edit User]
page where you can specify the user name, user password, and other parameters. Once you create a user, the other tabs become available, where you can specify which roles a user holds, which general SQL privileges
the user holds, which table-level privileges the user holds, which views are available, and which stored procedures can be executed.
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.
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:
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.
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.
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
, 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
You can grant privileges in the following ways:
Interactively, using the Management Portal
. From System Administration
, then select either Users
([Home] > [Security Management] > [Users]
) or Roles
([Home] > [Security Management] > [Roles]
). Select the desired user or role, then select the SQL Privileges
From SQL, using the GRANT
Privileges are namespace-specific.