Written by

Senior Startups and Community Programs Manager at InterSystems Corporation

STAFF

Question Evgeny Shvarov · Jun 10, 2021

Is it possible to grant sql access to schema?

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)0

Add reply

Comments

Stefan Cronje · Jun 10, 2021

GRANT SELECT, UPDATE ON SCHEMA Packacge_X TO UserZ

0 0

Evgeny Shvarov Jun 11, 2021 to Stefan Cronje

Thanks Stefan! This is what I was looking for!

0 0

Nigel Salm · Jun 10, 2021

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)