Skip to main content

%SYSTEM.SQL.Security

Class %SYSTEM.SQL.Security Extends %SYSTEM.Help [ Abstract, Not ProcedureBlock, System = 4 ]

Methods​

CheckPrivilege​

ClassMethod CheckPrivilege(Username As %String, ObjectType As %Integer, Object As %String, Action As %String, Namespace As %String = "") As %Boolean

Check if user has SQL privilege for a particular action. This does not check grant privileges.

Parameters:

Username Name of the user to check. Required. ObjectType Required. Specifies the type to check the privilege of. ObjectTypes are 1 (table), 3 (view), 5 (schema), 6 (ml configuration), 7 (foreign server), 9 (procedure). Object

Required. The name the object to check the privilege of.

For example, ObjectType and Object could be "1" and "Sample.Person", or "9" and "SQLUser.My_Procedure". Action Comma delimited string of actions letters to check privileges for. Actions are one or more of the letters "a,s,i,u,d,r,e,l" (in any order) which stands for ALTER,SELECT,INSERT,UPDATE,DELETE,REFERENCES,EXECUTE,USE (USE for ML Configurations/Foreign Server). Privilege "e" is only allowed for Procedures. Privilege "l" is only allowed for ML Configurations and Foreign Servers. CheckPrivilege will only return 1 if the user has privileges on all Actions specified. Required. Namespace Namespace object resides in (optional) default is current namespace

Returns:

  • 1 - if the Username does have the privilege
  • 0 - if the Username does not have the privilege
  • %Status - if CheckPrivilege call is reporting an error

Notes:

  • If Username is a user with the %All role, CheckPrivilege will return 1 even if the Object does not exist. If the user calling CheckPrivilege is not the same as Username, the calling user must hold the %Admin_Secure:"U" privilege. Example:
  • Do $SYSTEM.SQL.Security.CheckPrivilege("Miranda",3,"SQLUser.Person","s","PRODUCT")

CheckPrivilegeWithGrant​

ClassMethod CheckPrivilegeWithGrant(Username As %String, ObjectType As %Integer, Object As %String, Action As %String, Namespace As %String = "") As %Boolean

Check if user has SQL grant privilege for a particular action.

Parameters:

Username Name of the user to check. Required. ObjectType Required. Specifies the type to check the grant privilege of. ObjectTypes are 1 (table), 3 (view), 5 (schema), 6 (ml configuration), 7 (foreign server), 9 (procedure). Object

Required. The name the object to check the grant privilege of.

For example, ObjectType and Object could be "1" and "Sample.Person", or "9" and "SQLUser.My_Procedure". Action Comma delimited string of actions letters to check grant privileges for. Actions are one or more of the letters "a,s,i,u,d,r,e,l" (in any order) which stands for ALTER,SELECT,INSERT,UPDATE,DELETE,REFERENCES,EXECUTE,USE (USE for ML Configurations/ForeignServers). Privilege "e" is only allowed for Procedures. Privilege "l" is only allowed for ML Configurations and Foreign Servers. CheckPrivilegeWithGrant will only return 1 if the user has grant privileges on all Actions specified. Required. Namespace Namespace object resides in (optional) default is current namespace

Returns:

  • 1 - if the Username does have the privilege
  • 0 - if the Username does not have the privilege
  • %Status - if CheckPrivilegeWithGrant call is reporting an error

Notes:

  • If Username is a user with the %All role, CheckPrivilegeWithGrant will return 1 even if the Object does not exist. If the user calling CheckPrivilegeWithGrant is not the same as Username, the calling user must hold the %Admin_Secure:"U" privilege. Example:
  • Do $SYSTEM.SQL.Security.CheckPrivilegeWithGrant($username,1,"HHR.ProductionValues","s,i,u,d","USER")

GrantPrivilege​

ClassMethod GrantPrivilege(ObjPriv As %String, ObjList As %String, Type As %String, User As %String) As %Status [ ProcedureBlock = 1, PublicList = SQLCODE ]

GrantPrivilege lets you grant an ObjPriv to a User via this call instead of using the SQL GRANT statement. This does not include grant privileges.

$SYSTEM.SQL.Security.GrantPrivilege(ObjPriv,ObjList,Type,User)
Parameters: ObjPriv Comma delimited string of actions to grant. * for all actions:

  • Alter
  • Select
  • Insert
  • Update
  • Delete
  • References
  • Execute
  • Use
  • or any combination ObjList * for all objects, else a comma delimited list of SQL object names (tables, views, procedures, schemas). The specified schemas cannot be empty.
    Type Table, View, Schema, Stored Procedures, ML Configuration, or Foreign Server
    User Comma delimited list of users

GrantPrivilegeWithGrant​

ClassMethod GrantPrivilegeWithGrant(ObjPriv As %String, ObjList As %String, Type As %String, User As %String) As %Status [ ProcedureBlock = 1, PublicList = SQLCODE ]

GrantPrivilegeWithGrant lets you grant an ObjPriv, WITH GRANT OPTION, to a User

$SYSTEM.SQL.Security.GrantPrivilegeWithGrant(ObjPriv,ObjList,Type,User)
Parameters: ObjPriv Comma delimited string of actions to grant. * for all actions:

  • Alter
  • Select
  • Insert
  • Update
  • Delete
  • References
  • Execute
  • Use
  • or any combination ObjList * for all objects, else a comma delimited list of SQL object names (tables, views, procedures, schemas). The specified schemas cannot be empty.
    Type Table, View, Schema, Stored Procedure, ML Configuration, or Foreign Server
    User Comma delimited list of users

GrantOne​

ClassMethod GrantOne(ByRef Properties As %String) As %Status [ Internal, ProcedureBlock = 1 ]

Wrapper around GrantPrivilege() and GrantPrivilegeWithGrant() to support declarative syntax in CPF Actions

RevokePrivilege​

ClassMethod RevokePrivilege(ObjPriv As %String, ObjList As %String, Type As %String, User As %String, wGrant As %Integer = 0, Cascade As %Integer = 0, AsGrantor As %String = "") As %Status [ ProcedureBlock = 1, PublicList = SQLCODE ]

RevokePrivilege lets you revoke an ObjPriv from a User via this call instead of using the SQL REVOKE statement

$SYSTEM.SQL.Security.RevokePrivilege(ObjPriv,ObjList,Type,User,wGrant,Cascade,AsGrantor)
Parameters: ObjPriv Comma delimited string of actions to grant. * for all actions:

  • Alter
  • Select
  • Insert
  • Update
  • Delete
  • References
  • Execute
  • Use
  • or any combination ObjList * for all objects, else a comma delimited list of SQL object names (tables, views, procedures, schemas)
    Type Table, View, Schema, ML Configuration, Foreign Server, or Stored Procedures
    User Comma delimited list of users
    wGrant 0/1 for WITH GRANT OPTION
    Cascade 0/1 cascade revoke?
    AsGrantor Alternate User to remove privileges for. AsGrantor can be a user name, a comma-separated list of user names, or "*".

RevokeOne​

ClassMethod RevokeOne(ByRef Properties As %String) As %Status [ Internal, ProcedureBlock = 1 ]

Wrapper around RevokePrivilege() to support declarative syntax in CPF Actions

GrantAdminOne​

ClassMethod GrantAdminOne(ByRef Properties As %String) As %Status [ Internal, ProcedureBlock = 1 ]

Wrapper around SQL call "GRANT TO [WITH ADMIN OPTION]" Validates all user input to protect against SQL injection

RevokeAdminOne​

ClassMethod RevokeAdminOne(ByRef Properties As %String) As %Status [ Internal, ProcedureBlock = 1 ]

Wrapper around SQL call "REVOKE FROM " Validates all user input to protect against SQL injection

RoleExists​

ClassMethod RoleExists(rolename As %Library.String = "") As %Library.Boolean [ SqlProc ]

This entry point can be used to determine if a role exists.

Parameters:

rolename Name of the role to check. Examples:

  • Write $SYSTEM.SQL.Security.RoleExists("SalesManager") // Writes a 1 if role SalesManager exists This method can also be called as a Stored Procedure named %SYSTEM_SQL.Security_RoleExists(rolename)

UserExists​

ClassMethod UserExists(username As %Library.String = "") As %Library.Boolean [ SqlProc ]

This entry point can be used to determine if a user exists.

Parameters:

username Name of the user to check. Examples:

  • Write $SYSTEM.SQL.Security.UserExists("Robert") // Writes a 1 if user Robert exists This method can also be called as a Stored Procedure named %SYSTEM_SQL.Security_UserExists(username)