Skip to main content

%SYSTEM.SQL.Security

abstract class %SYSTEM.SQL.Security extends %SYSTEM.Help

Method Inventory

Methods

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")
  • 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")
  • classmethod GrantPrivilege(ObjPriv As %String, ObjList As %String, Type As %String, User As %String) as %Status
    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
    classmethod GrantPrivilegeWithGrant(ObjPriv As %String, ObjList As %String, Type As %String, User As %String) as %Status
    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
    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
    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 "*".
    classmethod RoleExists(rolename As %Library.String = "") as %Library.Boolean [ SQLProc = Security_RoleExists ]
    Projected as the stored procedure: Security_RoleExists
    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)
    classmethod UserExists(username As %Library.String = "") as %Library.Boolean [ SQLProc = Security_UserExists ]
    Projected as the stored procedure: Security_UserExists
    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)

    Inherited Members

    Inherited Methods

    FeedbackOpens in a new tab