Users

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. The newly created user has no roles. 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 select Security, then select Users. Click the Create New User button at the top of the page. This takes you to the 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.

You can use %Library.SQLCatalogPrivOpens in a new tab class queries to list:

All users SQLUsers()
All privileges granted to a specified user SQLUserPrivs(“username”)
All system privileges granted to a specified user SQLUserSysPrivs(“username”)
All roles granted to a specified user SQLUserRole(“username”)

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

User Name as Schema Name

Under some circumstances, a username can be implicitly used as an SQL schema name. This may pose problems if the username contains characters that are forbidden in an SQL identifier. For example, in a multiple domain configuration the username contains the “@” character.

InterSystems IRIS handles this situation differently depending on the setting of the Delimited Identifiers configuration parameter:

If the use of delimited identifiers is enabled, no special processing occurs.
If the use of delimited identifiers is disabled, then any forbidden characters are removed from the username to form a schema name. For example, the username “documentation@intersystems.com” would become the schema name “documentationintersystemscom”.

This does not affect the value returned by the SQL CURRENT_USER function. It is always the same as $USERNAME.

Roles

%Admin_UserEdit Permission