Using InterSystems SQL
Users, Roles, and Privileges
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
In SQL you use the CREATE USER
statement to create a user. This simply creates a user name and user password. You must use the GRANT
statement to assign privileges and roles to the user. You can use the ALTER USER
and DROP USER
statements to modify existing user definitions.
In the Management Portal
Select System Administration
, then select Users
. At click the Create New User
button at the top of the page. This takes you to the General
tab of the [System] > [Security Management] > [Users] > [Edit User]
page where you can specify the user name, user password, and other parameters. Once you create a user, the other tabs become available, where you can specify which roles a user holds, which general SQL privileges
the user holds, which table-level privileges the user holds, which views are available, and which stored procedures can be executed.
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.
The [System] > [Security Management] > [Roles]
page provides a list of role definitions for an InterSystems IRIS instance. To view or change details on a particular role, select the Edit
link for the role. On the [System] > [Security Management] > [Roles] > [Edit Role]
page that appears, there is information regarding the roles privileges and which users or roles hold it.
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:
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.
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
, 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
You can grant privileges in the following ways:
Privileges are namespace-specific.