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:

Users

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 select Security, then select Users. Click the Create New User button at the top of the page. This takes you to the 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.

You can use %Library.SQLCatalogPrivOpens in a new tab class queries to list:

All users SQLUsers()
All privileges granted to a specified user SQLUserPrivs(“username”)
All system privileges granted to a specified user SQLUserSysPrivs(“username”)
All roles granted to a specified user SQLUserRole(“username”)

The following example lists the privileges granted to the current user:

   SET statemt=##class(%SQL.Statement).%New()
   SET cqStatus=statemt.%PrepareClassQuery("%Library.SQLCatalogPriv","SQLUserPrivs")
     IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
   SET rset=statemt.%Execute($USERNAME)
   WRITE "Privileges for ",$USERNAME
   DO rset.%Display()

Roles

The Management Portal, System Administration, Security, Roles page provides a list of role definitions for a Caché instance. To view or change details on a particular role, select the Name link for the role. On the 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.”

Note:

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.

You can also define users and roles with the following SQL commands: CREATE USER, CREATE ROLE, ALTER USER, GRANT, DROP USER, and DROP ROLE.

You can use %Library.SQLCatalogPrivOpens in a new tab class queries to list:

All roles SQLRoles()
All privileges granted to a specified role SQLRolePrivileges(“rolename”)
All roles or users granted to a specified role SQLRoleUser(“rolename”)
All roles granted to a specified user SQLUserRole(“username”)

Privileges

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:

Interactively, using the Management Portal. From System Administration select Security, then select either Users or Roles. Select the desired user or role, then select the SQL Privileges tab.
From SQL, using the GRANT command.
For ObjectScript, you can use the %SYSTEM.SQL.GrantObjPriv()Opens in a new tab method to grant object privileges for a table or a view.

Privileges are namespace-specific.