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.

Roles