%CHECKPRIV

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

Arguments

GRANT OPTION FOR	Optional — This 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	Optional — This 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 system-wide default schema name. A schema search path cannot be present when invoking %CHECKPRIV.

Description

%CHECKPRIV can be used in two ways:

To determine if the current user holds a system privilege of a specified type.
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.

%CHECKPRIV enables you to check whether a privilege is held. It does not enforce privileges. At runtime privileges are enforced through ODBC/JDBC and through Dynamic SQL (for example, through the Management Portal Execute SQL Statement). Privileges are not enforced for Embedded SQL. %CHECKPRIV is primarily used for Embedded SQL.

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

Because %CHECKPRIV requires access to the SQLCODE 100 value (an SQLCODE status value, not an SQLCODE error value) to determine its result, it 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, Caché does not create a cached query for %CHECKPRIV.

You can determine if a specified user has a specified table-level privilege by invoking the $SYSTEM.SQL.CheckPriv()Opens in a new tab method.

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=99
  SET SQLCODE=""
  &sql(%CHECKPRIV UPDATE ON Sample.Person)
  IF SQLCODE=100 {
     WRITE !,"No UPDATE privilege"
     QUIT }
  ELSEIF SQLCODE < 0 {
     WRITE !,"Unexpected SQL error: ",SQLCODE
     QUIT }
  ELSE { 
     WRITE !,"Proceeding with UPDATE" }
  &sql(UPDATE Sample.Person SET Name=:name,Age=:age)
  IF SQLCODE=0 { WRITE !,"UPDATE successful" }
  ELSE { WRITE "UPDATE error SQLCODE=",SQLCODE }

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 privilege"}
  ELSEIF SQLCODE=100 {WRITE "Do not have privilege"}
  ELSE {WRITE "Unexpected SQLCODE error: ",SQLCODE}

The following Embedded SQL example checks whether the current user holds system privileges on the three table operations for all tables:

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

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 privileges"}
  ELSEIF SQLCODE=100 {WRITE "Do not have one or more privileges"}
  ELSE {WRITE "Unexpected SQLCODE error: ",SQLCODE}

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}

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}