SQL Security

Caché has both system-level security, and an additional set of SQL-related security features. The Caché SQL security provides an additional level of security capabilities beyond its database-level protections. Some of the key differences between SQL and system-level security are:

SQL protections are more granular than system-level protections. You can define privileges for tables, views, and stored procedures.
SQL privileges can be granted to users as well as to roles. System-level privileges are only assigned to roles.
Holding an SQL privilege implicitly grants any related system privileges that are required to perform the SQL action. (Conversely, system-level privileges do not imply table-level privileges.) The different types of privileges are described in the “SQL Privileges and System Privileges” section.

SQL Privileges and System Privileges

To manipulate tables or other SQL entities through SQL-specific mechanisms, a user must have the appropriate SQL privileges. System-level privileges are not sufficient.

Note:

Roles are shared by SQL and system level security: a single role can include both system and SQL privileges.

Consider the following example for an instance of Caché on a Windows machine:

There is a class in the USER namespace called User.MyPerson. This class is projected to SQL as the SQLUser.MyPerson table.
There is a user called Test, who belongs to no roles (and therefore has no system privileges) and who has all privileges on the SQLUser.MyPerson table (and no other SQL privileges).
There is a second user, called Test2. This user is assigned to the following roles: %DB_USER (and so can read or write data on the USER database); %SQL (and so has SQL access through the %Service_Bindings service); and, through a custom role, has privileges for using the Console and %Development.

If the Test user attempts to read or write data in the SQLUser.MyPerson table through any SQL-specific mechanism (such as one that uses ODBC), the attempt succeeds. This is because Caché makes the Test user a member of the %DB_USER and %SQL role to establish the connection; this is visible in audit events that the connection generates, such as the %System/%Login/Login event. (If the Test user attempts to use the Terminal or Management Portal, these attempts fail, because the user lacks sufficient privilege for these.)

If the Test2 user attempts to read or write data in the SQLUser.MyPerson table through any SQL-specific mechanism (such as one that uses ODBC), the attempt fails because the user does not have sufficient privileges for the table. (If the Test2 user attempts to view the same data in the Terminal using object mechanisms, the attempt succeeds — because the user is sufficiently privileged for this type of connection.)

The SQL Service

The %Service_SQL:Use privilege controls a user’s ability to connect using a Caché object or SQL client and then use SQL. When a user attempts to connect to Caché, the server determines whether the user holds any SQL-level privileges for the namespace. If the user holds at least one such privilege, then the server automatically adds two roles: the %SQL role, which has the %Service_SQL:Use privilege, and the implicit database role for the namespace’s default database. As a result of this automatic role addition, it is not necessary for SQL users to hold any database privileges, because the server adds them automatically.

With the exception of this role addition at connect-time, no automatic role escalation occurs during the processing of an SQL statement. The user must hold the necessary system-level privileges when the SQL statement is executed.

Note that the %Service_SQL:Use privilege is only required to use SQL in a client/server configuration. For example, a user running an application that employs server-side embedded SQL requests does not require this permission.

The %CREATE_TABLE command is namespace-specific: granting a user this privilege for a specific namespace enables the user to create new tables in that namespace only.

CREATE USER

The SQL CREATE USER statement can be used to create Caché users. The newly created user has no roles.

Under some circumstances, a username can be implicitly used as an SQL schema name. This may pose problems if the username contains characters that are forbidden in an SQL identifier. For example, in a multiple domain configuration the username contains the “@” character.

Caché handles this situation differently depending on the setting of the Delimited Identifiers configuration parameter:

If the use of delimited identifiers is enabled, no special processing occurs.
If the use of delimited identifiers is disabled, then any forbidden characters are removed from the username to form a schema name. For example, the username “documentation@intersystems.com” would become the schema name “documentationintersystemscom”.

This does not affect the value returned by the SQL CURRENT_USER function. It is always the same as $USERNAME.

Effect of Changes

Setting an SQL security value takes effect when the user next connects, not during that user’s current session.

Required Privileges for Working with Tables

Creating a user in SQL, with the statement

CREATE USER <username> IDENTIFY BY <password>

is equivalent to performing the same action using the Management Portal. For the user to be able to work with a particular table, privileges for that table must be explicitly granted, such as with the Management Portal.

The minimum privilege required to work with a particular table is: any SQL privilege, like SELECT, on the relevant table. If the user has the right to perform a SELECT command, then this grants the ability to read and use but not write; analogously, INSERT, UPDATE, and DELETE provide those privileges.