GRANT (SQL)

GRANT admin-privilege

SQL administrative (admin) privileges apply to users or roles. Any privilege that is not tied to any particular object (and thus is a general right for that user or role) is considered an admin privilege. These privileges are granted on a per-namespace basis for the current namespace.

The %DB_OBJECT_DEFINITION privilege grants all 16 of the data definition privileges. It does not grant %BUILD_INDEX, %NOCHECK, %NOINDEX, %NOLOCK, and %NOTRIGGER privileges, which must be granted explicitly.

The %BUILD_INDEX privilege grants use of the BUILD INDEX command. The %NOCHECK, %NOINDEX, %NOLOCK, and %NOTRIGGER privileges grant use of these options in the restriction clause of an INSERT, UPDATE, INSERT OR UPDATE, or DELETE statement. They have no effect on the use of the %NOINDEX keyword as a preface to a predicate condition. Because TRUNCATE TABLE performs a delete of all of the rows from a table with %NOTRIGGER behavior, you must have %NOTRIGGER privilege in order to run TRUNCATE TABLE. You must have the appropriate %NOCHECK, %NOINDEX, %NOLOCK, or %NOTRIGGER privilege to use that restriction when preparing an INSERT, UPDATE, INSERT OR UPDATE, or DELETE statement.

If the specified admin privilege is not a valid privilege name (for example, due to a spelling error), InterSystems IRIS completes successfully, issuing an SQLCODE 100 (reached end of data); InterSystems IRIS does not check if the specified user (or role) exists. If the specified admin privilege is valid, but the specified user (or role) does not exist, InterSystems IRIS issues an SQLCODE -118 error.

GRANT role

This form of GRANT assigns a user to a specified role. You can also assign a role to another role. If the specified role that receives the assignment does not exist, InterSystems IRIS issues an SQLCODE 100 (reached end of data). If the specified user (or role) that is assigned to a role does not exist, InterSystems IRIS issues an SQLCODE -118 error. If you are not the SuperUser, and you are attempting to grant a role that you don't own and don't have ADMIN OPTION for, InterSystems IRIS issues an SQLCODE -112 error.

Roles are created using the CREATE ROLE statement. If the role name is a delimited identifier, you must enclose it in quotation marks when assigning to it.

Roles can be granted or revoked via either the SQL GRANT and REVOKE commands, or via InterSystems IRIS System Security:

• Go to the Management Portal, select System Administration, Security, Users to display the current users. Select the name of the desired user to display edit options for that user, then select the Roles tab to assign (or unassign) the user to one or more roles.

• Go to the Management Portal, select System Administration, Security, Roles to display the current roles. Select the name of the desired role to display edit options for that role, then select the Assigned To tab to assign (or unassign) the role to one or more roles. Note that the ObjectScript $ROLES special variable does not display roles granted to roles.

GRANT object-privilege

Object privileges give a user or role some right to a particular object. You grant an object-privilege ON an object-list TO a grantee. An object-list can specify one or more tables, views, stored procedures, or cubes in the current namespace. By using comma-separated lists, a single GRANT statement can grant multiple object privileges on multiple objects to multiple users and/or roles.

The following are the available object-privilege values:

• The %ALTER and DELETE privileges grant access to table or view definitions.

• The SELECT, INSERT, UPDATE, DELETE, and REFERENCES privileges grant access to table data.

• The EXECUTE privilege grants access to stored procedures. This privilege is required to execute a stored procedure or to call a user-defined SQL function in a query. For example, SELECT Field1,MyFunc() FROM SQLUser.MyTable requires SELECT privilege on SQLUser.MyTable and EXECUTE privilege on the SQLUser.MyFunc procedure.

• The ALL PRIVILEGES privilege grants all table and view privileges; it does not grant the EXECUTE privilege.

You can use the asterisk (*) wildcard as the object-list value to grant the object-privilege to all of the objects in the current namespace. For example, GRANT SELECT ON * TO Deborah grants this user SELECT privilege for all tables and views. GRANT EXECUTE ON * TO Deborah grants this user EXECUTE privilege for all non-hidden Stored Procedures.

You can use SCHEMA schema-name as the object-list value to grant the object-privilege to all of the tables, views, and stored procedures in the named schema, in the current namespace. The following example grants this user SELECT privilege for all objects in the Sample schema.

GRANT SELECT ON SCHEMA Sample TO Deborah

This includes all objects that will be defined in this schema in the future. You can specify multiple schemas as a comma-separated list, as in the following example, which grants SELECT privilege for all objects in both the Sample and the Cinema schemas.

GRANT SELECT ON SCHEMA Sample,Cinema TO Deborah

Cubes are SQL identifiers that are not qualified by a schema name. To specify a cubes object-list, you must specify the CUBE (or CUBES) keyword. You can only grant SELECT privilege to a cube.

The following example demonstrates the granting of the SELECT and UPDATE privileges to a specific user for a specific table:

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
CreateUser
   SET x=$SYSTEM.SQL.Security.UserExists("DeborahTest")
   IF x=0 {&sql(CREATE USER DeborahTest IDENTIFY BY birdpw)
           IF SQLCODE '= 0 {WRITE "CREATE USER error: ",SQLCODE,!
                            QUIT}
          }
   ELSE {WRITE "User DeborahTest exists, not changing privileges",!
         QUIT }
GrantPrivsToUser
   &sql(GRANT SELECT,UPDATE ON SQLUSER.T1 TO DeborahTest)
   WRITE !,"GRANT error code: ",SQLCODE
DropUser
   &sql(DROP USER DeborahTest)
     IF SQLCODE '= 0 {WRITE "DROP USER error: ",SQLCODE,!}

Privileges can only be granted explicitly to a table, view, or stored procedure that already exists. If the specified object does not exist, InterSystems IRIS issues an SQLCODE -30 error. You can, however, grant privileges to a schema, which grant privileges both to all existing objects in that schema and to all future objects in that schema that did not exist when the privilege was granted.

If the owner of a table is _PUBLIC, users do not need to be granted object privileges to access the table.

If the specified user does not exist, InterSystems IRIS issues an SQLCODE -118 error. If the specified object privilege has already been granted, InterSystems IRIS issues an SQLCODE 100 (reached end of data).

Object privileges can be granted or revoked by any of the following:

• The GRANT and REVOKE commands.

• The $SYSTEM.SQL.Security.GrantPrivilege()Opens in a new tab and $SYSTEM.SQL.Security.RevokePrivilege()Opens in a new tab methods. These methods return a %Status value and set the SQLCODE variable. As with any method or function, always test the returned value first:

  • If %Status=1 and SQLCODE=0: a privilege was granted or revoked.

  • If %Status=1 and SQLCODE=100: no privilege was granted or revoked because it already has been granted or revoked.

  • If %Status is not 1 and SQLCODE is not set and may be undefined: no privilege was granted or revoked due to a method error. The %Status contains an SQLCODE indicating the type of failure: ObjPriv: SQLCODE -60 for an invalid privilege; ObjList: an ObjList object of the specified Object Type does not exist: SQLCODE -30, -187, -428, or -473; Type: SQLCODE -400 Object Type of TABLE, VIEW, CUBES, SCHEMA, or STORED PROCEDURES expected; User: SQLCODE -118 Unknown or non-unique user or role.

• Via InterSystems IRIS System Security. Go to the Management Portal, select System Administration, Security, Users (or System Administration, Security, Roles) select the name of the desired user or role, then select the SQL Tables or SQL Views tab. Select the desired Namespace from the drop-down list. Then select the Add Tables or Add Views button. In the displayed window, choose a schema, select one or more tables, and assign privileges.

You can determine if the current user has a specified object privilege by invoking the %CHECKPRIV command. You can determine if a specified user has a specified table-level object privilege by invoking the $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab method, as shown in the following example:

   WRITE "SELECT privilege? ",$SYSTEM.SQL.Security.CheckPrivilege("DeborahTest","1,SQLUSER.TestT1","s"),!
   WRITE "UPDATE privilege? ",$SYSTEM.SQL.Security.CheckPrivilege("DeborahTest","1,SQLUSER.TestT1","u"),!
   WRITE "DELETE privilege? ",$SYSTEM.SQL.Security.CheckPrivilege("DeborahTest","1,SQLUSER.TestT1","d"),!

GRANT column-privilege

Column privileges give a user or role a specified privilege to a specified list of columns on a specified table or view. This permits you to allow access to some table columns and not to other columns of the same table. This gives more specific access control than the GRANT object-privilege option, which defines privileges for an entire table or view. When granting privileges to a grantee, you should grant either table-level privilege or column-level privileges for a table, but not both. The SELECT, INSERT, UPDATE, and REFERENCES privileges can be used to grant access to data in individual columns.

A user having a SELECT, INSERT, UPDATE, or REFERENCES object-privilege on a table WITH GRANT OPTION can grant to other users a column-privilege of the same type for columns of that table.

You can specify a single column, or a comma-separated list of columns. The column-list must be enclosed in parentheses. Column names can be specified in any order, and duplication is permitted. Granting a column privilege to a column that already has that privilege has no effect.

The following example grants the UPDATE privilege for two columns:

GRANT UPDATE(Name,FavoriteColors) ON Sample.Person TO Deborah

You can grant column privileges on a table or a view. You can grant column privileges to any type of grantee, including a list of users, a list of roles, *, and _PUBLIC. However, you cannot use the asterisk (*) wildcard for privileges, field names, or table names.

If a user inserts a new record into a table, data is inserted into only those fields for which column privileges have been granted. All other data columns are set to either the defined column default value, or to NULL if there is no defined default value. You cannot grant column-level INSERT or UPDATE privileges to the RowID and Identity columns. Upon INSERT, InterSystems SQL automatically provides a RowID and (if needed) an Identity column value.

Column-level privileges can be granted or revoked via either the SQL GRANT and REVOKE commands, or via InterSystems IRIS System Security. Go to the Management Portal, select System Administration, Security, Users (or System Administration, Security, Roles), select the name of the desired user or role, then select the SQL Tables or SQL Views tab. Select the desired Namespace from the drop-down list. Then select the Add Columns button. In the displayed window, choose a schema, choose a table, select one or more columns, and assign privileges.

Granting Multiple Privileges

You can use a single GRANT statement to specify the following combinations of privileges:

• One or more roles.

• One or more table-level privileges and one or more column-level privileges. To specify multiple table-level and column-level privileges, the privilege must immediately precede a column-list to grant a column-level privilege. Otherwise, it grants a table-level privilege.

• One or more admin-privileges. You cannot include admin-privileges and role names or object privileges in the same GRANT statement. Attempting to do so results in an SQLCODE -1 error.

The following example grants Deborah table-level SELECT and UPDATE privileges, and column-level INSERT privileges:

GRANT SELECT,UPDATE,INSERT(Name,FavoriteColors) ON Sample.Person TO Deborah

The following example grants Deborah column-level SELECT, INSERT, and UPDATE privileges:

GRANT SELECT(Name,FavoriteColors),INSERT(Name,FavoriteColors),UPDATE(FavoriteColors) ON Sample.Person TO Deborah

The WITH GRANT OPTION Clause

The owner of an object automatically holds all privileges on that object. The GRANT statement’s TO clause specifies the users or roles to whom to access is being granted. After using the TO option to specify the grantee, you may optionally specify the WITH GRANT OPTION keyword clause to allow the grantee(s) to also be able to grant the same privileges to other users. You can use the WITH GRANT OPTION keyword clause with object privileges or column privileges. The REVOKE command with CASCADE can be used to undo this cascading series of granted privileges.

For instance, you can give the user Chris %ALTER, SELECT, and INSERT privileges on the EMPLOYEES table with the following command:

GRANT %ALTER, SELECT, INSERT
     ON EMPLOYEES
     TO Chris

To also give Chris the ability to give these privileges to other users, the GRANT command includes the WITH GRANT OPTION clause:

GRANT %ALTER, SELECT, INSERT
     ON EMPLOYEES
     TO Chris WITH GRANT OPTION

You can find out the results of a GRANT statement using the %SQLCatalogPriv.SQLUsers() method call.

Granting privileges to a schema WITH GRANT OPTION allow the grantee(s) to be able to grant the same schema privileges to other users. However, it does not allow the grantee to grant a privilege on a specified object within that schema, unless the user has been explicitly granted the privilege on that particular object WITH GRANT OPTION. This is shown in the following example:

• UserA and UserB start with no privileges.

• You grant UserA SELECT privilege on schema Sample WITH GRANT OPTION.

• UserA can grant SELECT privilege on schema Sample to UserB.

• UserA cannot grant SELECT privilege on table Sample.Person to UserB.

The WITH ADMIN OPTION Clause

The WITH ADMIN OPTION clause grants the grantee the right to grant the same privileges it received to others. To grant a system privilege, you must have been granted the system privilege WITH ADMIN OPTION.

You may grant a role if either the role has been granted to you WITH ADMIN OPTION, or if you have the %Admin_Secure:"U" resource.

A grant WITH ADMIN OPTION supersedes a previous grant of the same privilege(s) without this option. Thus, if you grant a user a privilege without WITH ADMIN OPTION, and then grant the same privilege to the user WITH ADMIN OPTION, the user has the WITH ADMIN OPTION rights. However, a grant without the WITH ADMIN OPTION does not supersede a previous grant of the same privilege(s) with this option. To remove WITH ADMIN OPTION rights from a privilege, you must revoke the privilege and then re-grant the privilege without this clause.

Exporting Privileges

You can export privileges using the $SYSTEM.SQL.Schema.ExportDDL()Opens in a new tab method. When you specify a table in this method, InterSystems IRIS exports both all table-level privileges and all column-level privileges granted for that table. For further details, refer to the InterSystems Class Reference.

InterSystems IRIS Security

Before using GRANT in embedded SQL, it is necessary to be logged in as a user with appropriate privileges. Failing to do so results in an SQLCODE -99 error (Privilege Violation). Use the $SYSTEM.Security.Login()Opens in a new tab method to assign a user with appropriate privileges:

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, refer to %SYSTEM.SecurityOpens in a new tab in the InterSystems Class Reference.

Enforcement of Privileges

SQL privileges are only enforced through ODBC, JDBC, and Dynamic SQL (%SQL.Statement).

The enforcement of privileges system-wide depends upon the setting of the $SYSTEM.SQL.Util.SetOption("SQLSecurity")Opens in a new tab method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a SQL Security ON: setting.

The default is 1 (Yes): a user can only perform actions on tables and views for which that user has been granted privilege. This is the recommended setting for this option. If this option is set to 0 (No), SQL Security is disabled for any new process started after changing this setting. This means privilege-based table/view security is suppressed. You can create a table without specifying a user. In this case, the Management Portal assigns “_SYSTEM” as user, and embedded SQL assigns "" (the empty string) as user. Any user can perform actions on a table or view even if that user has no privileges to do so.

grantee

A comma-separated list of one or more users or roles. Valid values are a list of users, a list of roles, "*", or _PUBLIC. The asterisk (*) specifies all currently defined users who do not have the %All role. The _PUBLIC keyword specifies all currently defined and yet-to-be-defined users.

admin-privilege

An administrative-level privilege or a comma-separated list of administrative-level privileges being granted. The list may consist of one or more of the following in any order:

%CREATE_METHOD, %DROP_METHOD, %CREATE_FUNCTION, %DROP_FUNCTION, %CREATE_PROCEDURE, %DROP_PROCEDURE, %CREATE_QUERY, %DROP_QUERY, %CREATE_TABLE, %ALTER_TABLE, %DROP_TABLE, %CREATE_VIEW, %ALTER_VIEW, %DROP_VIEW, %CREATE_TRIGGER, %DROP_TRIGGER

%DB_OBJECT_DEFINITION, which grants all 16 of the above privileges.

%NOCHECK, %NOINDEX, %NOLOCK, %NOTRIGGER privileges for INSERT, UPDATE, and DELETE operations.

%BUILD_INDEX which grants privileges for the BUILD INDEX command.

role

A role or comma-separated list of roles whose privileges are being granted.

object-privilege

A basic-level privilege or comma-separated list of basic-level privileges being granted. The list may consist of one or more of the following: %ALTER, DELETE, SELECT, INSERT, UPDATE, EXECUTE, and REFERENCES. You can confer all table and view privileges using either "ALL [PRIVILEGES]" or “*” as the argument value. Note that you can only grant SELECT privilege to CUBES.

object-list

A comma-separated list of one or more tables, views, stored procedures, or cubes for which the object-privilege(s) are being granted. You can use the SCHEMA keyword to specify granting the object-privilege to all objects in the specified schema. You can use “*” to specify granting the object-privilege to all tables, or to all non-hidden Stored Procedures, in the current namespace. Note that a cubes object-list requires the CUBE (or CUBES) keyword, and can only be granted SELECT privilege.

column-privilege

A basic-level privilege being granted to one or more listed columns. Available options are SELECT, INSERT, UPDATE, and REFERENCES.

column-list

A list of one or more column names, separated by commas and enclosed in parentheses.

table

The name of the table or view that contains the column-list columns.