SQL Users, Roles, and Privileges
InterSystems IRIS® data platform has both system-level security, and an additional set of SQL-related security features. The InterSystems 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.
InterSystems SQL enforces privilege checking for ODBC, JDBC, Dynamic SQL, and the SQL Shell interface on InterSystems IRIS data platform. 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. Similarly, direct invocation of class queries that do not involve %SQL.Statement objects is considered application access and does not check for SQL privileges.
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. A user may be granted SQL privileges directly, or the user may belong to a role that has SQL privileges.
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 InterSystems IRIS on a Windows machine:
-
There is a persistent 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 InterSystems IRIS makes the Test user a member of the %SQL role (which includes the %Service_SQL:Use privilege) and the %DB_USER role, so the user has the necessary privileges 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 Terminal object mechanisms, 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.)
For more information about SQL privileges, see SQL privileges.
InterSystems IRIS persistent classes also support privileges for row-level security.
%Admin_Secure Permission
If you are a user with the %Admin_Secure administrative resource with USE permission, you can perform the following operations:
-
Create, modify, or delete a user.
-
Create, modify, or delete a role.
-
See the privileges granted to a user.
-
See the privileges granted to a role.
-
Revoke SQL privileges that were granted by another user.
%Admin_Secure allows a user to perform these operations without being given full security privileges on the system.
%Admin_RoleEdit Permission
Users with the %Admin_RoleEdit administrative resource with USE permission can perform the following operations:
-
Create or delete a role.
%Admin_RoleEdit allows a user to perform these operations without being given full security privileges on the system. Users with %Admin_RoleEdit but not %Admin_Secure can delete any non-system role and system roles that they have created themselves.
%Admin_UserEdit Permission
Users with the %Admin_UserEdit administrative resource with USE permission can perform the following operations:
-
Create, modify, or delete a user.
%Admin_UserEdit allows a user to perform these operations without being given full security privileges on the system. Users with %Admin_UserEdit but not %Admin_Secure can drop other users they have created, as well as any user created by a user they have created. Such a user cannot drop system users or themselves.
Users
An InterSystems SQL user is the same as a user defined for InterSystems 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. The newly created user has no roles. 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.
User Name as Schema Name
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.
InterSystems IRIS 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.
Use ObjectScript to List Users
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
SQL privileges are assigned to a user or role. A role enables you to set the same privileges for multiple users. Roles are shared by SQL and system level security: a single role can include both system privileges and SQL privileges.
The Management Portal, System Administration, Security, Roles page provides a list of role definitions for an InterSystems IRIS 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 InterSystems 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 InterSystems 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.
You can also define users and roles with the following SQL commands: CREATE USER, CREATE ROLE, ALTER USER, GRANT, DROP USER, and DROP ROLE.
If you manually create a role names SQLAdminRole, there is some special behavior if you would like a user with this role to issue a DROP ROLE command. In particular, you will need to give the %Admin_Secure resource to SQLAdminRole to allow users with this role to perform a DROP ROLE.
Use ObjectScript to List Roles
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”)
SQL Privileges
SQL privileges are assigned to a user or role. A role enables you to set the same privileges for multiple users.
InterSystems SQL supports two types of privileges: administrative and object.
-
Administrative privileges are namespace-specific.
Administrative privileges cover the creation, altering, and deleting of types of objects, such as the %CREATE_TABLE privilege required to create tables. The %ALTER_TABLE privilege is required not only to alter a table, but to create or drop an index, to create or drop a trigger, and to run TUNE TABLE.
Administrative privileges also include %NOCHECK, %NOINDEX, %NOLOCK, %NOJOURN, and %NOTRIGGER, which determine whether the user can apply the corresponding keyword 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 are specific to a table, view, or stored procedure. They specify the type of access to specific named SQL objects (in the SQL sense of the word: a table, a view, a column, or a stored procedure). If the user is the Owner (creator) of the SQL object, the user is automatically granted all privileges for that object.
Table-level object privileges provide access (%ALTER, DELETE, SELECT, INSERT, UPDATE, EXECUTE, REFERENCES, CANCEL) 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.
Granting SQL Privileges
You can grant privileges in the following ways:
-
Use the Management Portal. From System Administration select Security, then select either Users or Roles. Select the desired user or role, then select the appropriate tab: SQL Privileges for administrative privileges, SQL Tables, SQL Views, or SQL Procedures for object privileges.
-
From SQL, use the GRANT command to grant specific administrative privileges or object privileges to a specified user or role (or list of users or roles). You can use the REVOKE command to remove privileges.
-
From ObjectScript, use the $SYSTEM.SQL.Security.GrantPrivilege()Opens in a new tab method to grant specific object privileges to a specified user (or list of users).
Listing SQL Privileges
-
Use the Management Portal. From System Administration select Security, then select either Users or Roles. Select the desired user or role, then select the appropriate tab: SQL Privileges for administrative privileges, SQL Tables, SQL Views, or SQL Procedures for object privileges.
-
From SQL, use the %CHECKPRIV command to determine if the current user has a specific administrative or object privilege.
-
From ObjectScript, use the $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab method to determine if a specified user has a specific object privilege.
Auditing Privilege Errors
When an InterSystems IRIS process invokes an SQL statement for which the user is not privileged, the operation fails and an SQLCODE –99 error is generated. When the Audit Event %System/%SQL/PrivilegeFailure is enabled, a record will be placed in the Audit database for each SQLCODE -99 error encountered. This Audit database option is disabled by default.