New post
Question
· Jun 10, 2021

Is it possible to grant sql access to schema?

#Security #SQL #InterSystems IRIS

Hi folks!

Here is how I grant SQL access of user to a particular class/table:

GRANT SELECT, UPDATE ON Packacge_X.Table_Y TO UserZ

How can I grant SQL access of a certain user to a whole schema?

Product version: IRIS 2020.4
Discussion (3)2
Log in or sign up to continue
Nigel Salm · Jun 10, 2021

Hi

According to the documentation you can GRANT priveledges to a Class/Table and you can use a wildcard "*" for a collection of Classes/Tables

The documentation reference in the Ensemble documentation is:

http://localhost:57772/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_grant

and in the explanation there is an example:

GRANT object-privilege ON object-list TO grantee [WITH GRANT OPTION]

and further on the documentation says:

object-list

A comma-separated list of one or more tables, views, stored procedures, or cubes for which the object-privilege(s) are being granted. You can use the SCHEMA keyword to specify granting the object-privilege to all objects in the specified schema. You can use “*” to specify granting the object-privilege to all tables, or to all non-hidden Stored Procedures, in the current namespace. Note that a cubes object-list requires the CUBE (or CUBES) keyword, and can only be granted SELECT privilege.

The full syntax is:

grantee
 		 A comma-separated list of one or more users or roles. Valid values are a list of users, a list of roles, "*", or _PUBLIC. The asterisk (*) specifies all currently defined users who do not have the %All role. The _PUBLIC keyword specifies all currently defined and yet-to-be-defined users.
admin-privilege

An administrative-level privilege or a comma-separated list of administrative-level privileges being granted. The list may consist of one or more of the following in any order:

%CREATE_METHOD, %DROP_METHOD, %CREATE_FUNCTION, %DROP_FUNCTION, %CREATE_PROCEDURE, %DROP_PROCEDURE, %CREATE_QUERY, %DROP_QUERY, %CREATE_TABLE, %ALTER_TABLE, %DROP_TABLE, %CREATE_VIEW, %ALTER_VIEW, %DROP_VIEW, %CREATE_TRIGGER, %DROP_TRIGGER

%DB_OBJECT_DEFINITION, which grants all 16 of the above privileges.

%NOCHECK, %NOINDEX, %NOLOCK, %NOTRIGGER privileges for INSERT, UPDATE, and DELETE operations.
role A role or comma-separated list of roles whose privileges are being granted.
object-privilege A basic-level privilege or comma-separated list of basic-level privileges being granted. The list may consist of one or more of the following: %ALTER, DELETE, SELECT, INSERT, UPDATE, EXECUTE, and REFERENCES. You can confer all table and view privileges using either "ALL [PRIVILEGES]" or “*” as the argument value. Note that you can only grant SELECT privilege to CUBES.
object-list A comma-separated list of one or more tables, views, stored procedures, or cubes for which the object-privilege(s) are being granted. You can use the SCHEMA keyword to specify granting the object-privilege to all objects in the specified schema. You can use “*” to specify granting the object-privilege to all tables, or to all non-hidden Stored Procedures, in the current namespace. Note that a cubes object-list requires the CUBE (or CUBES) keyword, and can only be granted SELECT privilege.
column-privilege A basic-level privilege being granted to one or more listed columns. Available options are SELECT, INSERT, UPDATE, and REFERENCES.
column-list A list of one or more column names, separated by commas and enclosed in parentheses.
table The name of the table or view that contains the column-list columns.

  

In IRIS look at the documentation at this link:

%SYSTEM.SQL.Security

You can check priveledges with:

Methods

classmethod CheckPrivilege(Username As %String, ObjectType As %Integer, Object As %String, Action As %String, Namespace As %String = "") as %Boolean [ Language = objectscript ]

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), 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" (in any order) which stands for ALTER,SELECT,INSERT,UPDATE,DELETE,REFERENCES,EXECUTE. Privilege "e" is only allowed for Procedures. 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")

and you can set Priveledges with

classmethod GrantPrivilege(ObjPriv As %String, ObjList As %String, Type As %String, User As %String) as %Status [ Language = objectscript ]

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
  • or any combination
ObjList
* for all objects, else a comma delimited list of SQL object names (tables, views, procedures, schemas)
Type
Table, View, Schema or Stored Procedures
User
Comma delimited list of users

classmethod GrantPrivilegeWithGrant(ObjPriv As %String, ObjList As %String, Type As %String, User As %String) as %Status [ Language = objectscript ]

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
  • or any combination
ObjList
* for all objects, else a comma delimited list of SQL object names (tables, views, procedures, schemas)
Type
Table, View, Schema or Stored Procedures
User
Comma delimited list of users

Nigel

0 0