Caché Security Administration Guide
SQL Security
[Back] [Next]
Go to:

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 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:
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 Caché 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 Caché 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.
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:
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.