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.
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.
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 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
(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
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.)
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.
command is namespace-specific: granting a user this privilege for a specific namespace enables the user to create new tables in that namespace only.
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 @
Caché handles this situation differently depending on the setting of the Delimited Identifiers
If the use of delimited identifiers is enabled, no special processing occurs.
This does not affect the value returned by the SQL CURRENT_USER
function. It is always the same as $USERNAME
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
, and DELETE
provide those privileges.