Skip to main content

%CHECKPRIV (SQL)

Checks whether the user holds a specified privilege.

Synopsis

%CHECKPRIV [GRANT OPTION FOR | ADMIN OPTION FOR] syspriv [,syspriv]  
%CHECKPRIV [GRANT OPTION FOR] objpriv
      ON object  
%CHECKPRIV column-privilege (column-list)
      ON table

Description

%CHECKPRIV can be used in two ways:

  • To determine if the current user holds a specified system privilege, or holds all of the system privileges specified in a comma-separated list.

  • To determine if the current user holds a user privilege of a specified type on a specified object. These objects can include table-level privileges on tables or views, column-level privileges on specified columns, and privileges on stored procedures.

If the user holds the specified privilege, %CHECKPRIV sets SQLCODE=0. If the user does not hold the specified privilege, %CHECKPRIV sets SQLCODE=100.

%CHECKPRIV enables you to check whether a privilege is held. It does not enforce privileges:

  • Embedded SQL does not enforce privileges. %CHECKPRIV is primarily used for Embedded SQL. See Embedded SQL and Privileges.

  • Dynamic SQL enforces privileges at runtime. For example, if you do not have the %CREATE_TABLE system privilege, %CHECKPRIV %CREATE_TABLE sets SQLCODE=100, showing that you don’t have this privilege. Dynamic SQL enforces this privilege; a CREATE TABLE operation fails with an SQLCODE -99 error.

    At runtime, Dynamic SQL and ODBC/JDBC enforce privileges and generate appropriate errors. The Management Portal Execute Query SQL interface and the SQL Shell both execute as Dynamic SQL.

Because %CHECKPRIV requires access to the SQLCODE 100 value (an SQLCODE status value, not an SQLCODE error value) to determine its result, %CHECKPRIV cannot be directly used by JDBC and other clients that can only distinguish error or no error status.

Because %CHECKPRIV prepares and executes quickly, and is generally run only once, InterSystems IRIS does not create a cached query for %CHECKPRIV.

The CheckPrivilege() Method

The $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab method provides greater functionality for checking user privileges on a table, view, or stored procedure:

  • CheckPrivilege() checks privileges for a specified user. %CHECKPRIV only checks privileges for the current user.

  • CheckPrivilege() allows you to check multiple privileges. Each invocation of %CHECKPRIV can only check one objpriv privilege.

  • CheckPrivilege() allows you to check privileges on a table, view, or procedure defined in another namespace. %CHECKPRIV only checks privileges for objects in the current namespace.

Embedded SQL and Privileges

Privileges are not automatically checked or enforced for Embedded SQL. Therefore, an Embedded SQL program should (in most cases) call %CHECKPRIV before attempting a privileged operation, such as an update:

  SET name="Fred",age=25
  SET SQLCODE=""
  &sql(%CHECKPRIV UPDATE ON Sample.Person)
  IF SQLCODE=100 {
     WRITE !,"No UPDATE privilege"
     QUIT }
  ELSEIF SQLCODE < 0 {
     WRITE !,"Unexpected SQL error: ",SQLCODE," ",%msg
     QUIT }
  ELSE { 
     WRITE !,"Proceeding with UPDATE" }
  &sql(UPDATE Sample.Person SET Name=:name,Age=:age WHERE Address='123 Bedrock')
  IF SQLCODE=0 { WRITE !,"UPDATE successful" }
  ELSE { WRITE "UPDATE error SQLCODE=",SQLCODE }

Arguments

GRANT OPTION FOR

This optional keyword phrase specifies checking whether the current user holds the WITH GRANT OPTION privilege on the specified privilege(s). A %CHECKPRIV with this option does not check whether the user holds the specified privilege(s) itself.

ADMIN OPTION FOR

This optional keyword phrase specifies checking whether the current user can grant the specified system privilege(s) to other users or roles. A %CHECKPRIV with this option does not check whether the user holds the specified privilege(s) itself.

syspriv

A system privilege, or a comma-separated list of system privileges. The available syspriv options include sixteen object definition privileges and four data modification privileges.

The object definition privileges are: %CREATE_FUNCTION, %DROP_FUNCTION, %CREATE_METHOD, %DROP_METHOD, %CREATE_PROCEDURE, %DROP_PROCEDURE, %CREATE_QUERY, %DROP_QUERY, %CREATE_TABLE, %ALTER_TABLE, %DROP_TABLE, %CREATE_VIEW, %ALTER_VIEW, %DROP_VIEW, %CREATE_TRIGGER, %DROP_TRIGGER. Alternatively, you can specify %DB_OBJECT_DEFINITION, which tests all 16 object definition privileges.

The data modification privileges are the %NOCHECK, %NOINDEX, %NOLOCK, %NOTRIGGER privileges for INSERT, UPDATE, and DELETE operations.

objpriv

An object privilege associated with a specified object. The available options are: %ALTER, DELETE, SELECT, INSERT, UPDATE, EXECUTE, and REFERENCES.

object

The name of the object for which the objpriv is being checked.

column-privilege

A column-level privilege associated with one or more listed columns. Available options are SELECT, INSERT, UPDATE, and REFERENCES.

column-list

A list of one or more column names for which privilege assignment is being checked, separated by commas and enclosed in parentheses. A space may be included or omitted between the column-privilege name and the opening parenthesis.

table

The name of the table or view that contains the column-list columns. A table name or view name can be qualified (schema.tablename), or unqualified (tablename). An unqualified name takes the default schema name; a schema search path is ignored.

Examples

The following Embedded SQL example checks whether the current user holds a specific object privilege for a specific table:

  &sql(%CHECKPRIV UPDATE ON Sample.Person)
  IF SQLCODE=0 {WRITE "Have update privilege"}
  ELSEIF SQLCODE=100 {WRITE "Do not have update privilege" QUIT}
  ELSE {WRITE "Unexpected %CHECKPRIV error: ",SQLCODE," ",%msg  QUIT}

The following Embedded SQL example checks whether the current user holds system privileges on the three table operations. If it has privileges, it creates a table:

  &sql(%CHECKPRIV %CREATE_TABLE,%ALTER_TABLE,%DROP_TABLE)
  IF SQLCODE=0 {WRITE "Have table privileges",!}
  ELSEIF SQLCODE=100 {WRITE "Do not have one or more table privileges"  QUIT}
  ELSE {WRITE "Unexpected %CHECKPRIV error: ",SQLCODE," ",%msg  QUIT}
  &sql(CREATE TABLE Sample.MyTable (Name VARCHAR(40),Age INTEGER))
  WRITE "Created table"

The following Embedded SQL example checks whether the current user holds all 16 object definition privileges. The SQLCODE value is set to either 0 (holds all 16 privileges) or 100 (does not hold one or more of the 16 privileges):

  &sql(%CHECKPRIV %DB_OBJECT_DEFINITION)
  IF SQLCODE=0 {WRITE "Have all system privileges"}
  ELSEIF SQLCODE=100 {WRITE "Do not have one or more system privileges"}
  ELSE {WRITE "Unexpected SQLCODE error: ",SQLCODE," ",%msg}

The following Embedded SQL example checks whether the current user can grant the %CREATE_TABLE privilege to other users or roles:

  &sql(%CHECKPRIV ADMIN OPTION FOR %CREATE_TABLE)
  IF SQLCODE=0 {WRITE "Have admin option on privilege"}
  ELSEIF SQLCODE=100 {WRITE "Do not have admin option on privilege"}
  ELSE {WRITE "Unexpected SQLCODE error: ",SQLCODE," ",%msg}

The following Embedded SQL example checks whether the current user holds the specified column-level privileges. Following the name of the privilege, specify the name of a column (or a comma-separated list of columns) in parentheses:

  &sql(%CHECKPRIV UPDATE(Name,Age) ON Sample.Person)
  IF SQLCODE=0 {WRITE "Have privilege on all specified columns"}
  ELSEIF SQLCODE=100 {WRITE "Do not have privilege on one or more specified columns"}
  ELSE {WRITE "Unexpected SQLCODE error: ",SQLCODE," ",%msg}

See Also

FeedbackOpens in a new tab