InterSystems IRIS Data Platform 2019.2  /  Using InterSystems SQL

Using InterSystems SQL
Users, Roles, and Privileges
Previous section           Next section
InterSystems: The power behind what matters   

InterSystems SQL provides security through the use of users and their granted privileges. InterSystems SQL enforces privilege checking for ODBC, JDBC, Dynamic SQL, and the SQL Shell interface on the InterSystems IRIS Data Platform™. Embedded SQL statements do not perform privilege checking; it is assumed that applications using Embedded SQL will check for privileges before using Embedded SQL statements.
This chapter discusses the following topics:
An InterSystems SQL user is the same as a user defined for InterSystems security. You can define a user using either SQL commands or the Management Portal.
If a user has SQL table privileges, or general SQL privileges, then roles granted or revoked on the user’s Roles tab do not affect a user’s access to tables through SQL-based services, such as ODBC. This is because, in the SQL-based services, table-based privileges take precedence over resource-based privileges.
You can use %Library.SQLCatalogPriv class queries to list:
The following example lists the privileges granted to the current user:
   SET statemt=##class(%SQL.Statement).%New()
   SET cqStatus=statemt.%PrepareClassQuery("%Library.SQLCatalogPriv","SQLUserPrivs")
     IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
   SET rset=statemt.%Execute($USERNAME)
   WRITE "Privileges for ",$USERNAME
   DO rset.%Display()
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.”
You should define privileges using roles and associate specific users with these roles. There are two reasons for this:
  1. 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.
  2. 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.
You can use %Library.SQLCatalogPriv class queries to list:
Privileges are assigned to a user or role. InterSystems SQL supports two types of privileges: administrative and object.
Administrative privileges cover the creation, altering, and deleting of types of objects, such as the permission to create tables. They also determine whether a user can apply %NOCHECK, %NOINDEX, %NOLOCK, or %NOTRIGGER restrictions when performing an INSERT, UPDATE, INSERT OR UPDATE, or DELETE. Assigning the %NOTRIGGER administrative privilege is required for a user to perform a TRUNCATE TABLE.
Object privileges cover access to specific named objects (in the SQL sense of the word: a table, a view, a column, or a stored procedure). Table-level object privileges provide access (%ALTER, DELETE, SELECT, INSERT, UPDATE, EXECUTE, REFERENCES) to the data in all columns of a table or view, both those columns that currently exist and any subsequently added columns. Column-level object privileges provide access to the data in only the specified columns of a table or view. You do not need to assign column-level privileges for columns with system-defined values, such as RowID and Identity. Stored procedure object privileges permit the assignment of EXECUTE privilege for the procedure to specified users or roles. For further details, refer to the GRANT command.
You can grant privileges in the following ways:
Privileges are namespace-specific.

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-10-22 04:57:16