Roles
SQL privileges are assigned to a user or role. A role enables you to set the same privileges for multiple users. Roles are shared by SQL and system level security: a single role can include both system privileges and SQL privileges.
The Management Portal, System Administration, Security, Roles page provides a list of role definitions for an InterSystems IRIS instance. To view or change details on a particular role, select the Name link for the role. On the Edit Role page that appears, there is information regarding the roles privileges, and which users or roles hold it.
The General tab lists a role’s privileges for InterSystems security resources. If a role only holds SQL privileges, the General tab’s Resources table lists the role’s privileges as “None defined.”
The SQL Privileges tab lists a role’s privileges for InterSystems SQL resources, where a drop-down list of namespaces allows you to view each namespace’s resources. Because privileges are listed by namespace, the listing for a role holding no privileges in a particular namespace displays “None.”
Note:
You should define privileges using roles and associate specific users with these roles. There are two reasons for this:
-
It is much more efficient for the SQL Engine to determine privilege levels by checking a relatively small role database than by checking individual user entries.
-
It is much easier to administer a system using a small set of roles as compared with a system with many individual user settings.
For example, you can define a role called “ACCOUNTING” with certain access privileges. As the Accounting Department grows, you can define new users and associate them with the ACCOUNTING role. If you need to modify the privileges for ACCOUNTING, you can do it once and it will automatically cover all the members of the Accounting Department.
A role can hold other roles. For example, the ACCOUNTING role can hold the BILLINGCLERK role. A user granted the ACCOUNTING role would have the privileges of both the ACCOUNTING role and the BILLINGCLERK role.
You can also define users and roles with the following SQL commands: CREATE USER, CREATE ROLE, ALTER USER, GRANT, DROP USER, and DROP ROLE.
Note:
If you manually create a role names SQLAdminRole, there is some special behavior if you would like a user with this role to issue a DROP ROLE command. In particular, you will need to give the %Admin_Secure resource to SQLAdminRole to allow users with this role to perform a DROP ROLE.
You can use %Library.SQLCatalogPrivOpens in a new tab class queries to list:
-
All roles SQLRoles()
-
All privileges granted to a specified role SQLRolePrivileges(“rolename”)
-
All roles or users granted to a specified role SQLRoleUser(“rolename”)
-
All roles granted to a specified user SQLUserRole(“username”)
