docs.intersystems.com
InterSystems IRIS Data Platform 2019.2  /  InterSystems SQL Reference  /  SQL Commands

InterSystems SQL Reference
%CHECKPRIV
Previous section           Next section
InterSystems: The power behind what matters   
Search:  


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 default schema name; a schema search path is ignored.
Description
%CHECKPRIV can be used in two ways:
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:
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 CheckPriv() Method
The $SYSTEM.SQL.CheckPriv() method provides greater functionality for checking user privileges on a table, view, or stored procedure:
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 }
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


Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-10-22 04:57:16