CREATE ROLE (SQL)

Creates a role.

Synopsis

CREATE ROLE role-name

Description

The CREATE ROLE command creates a role. A role is a named set of privileges that may be assigned to multiple users. A role may be assigned to multiple users, and a user may be assigned multiple roles. A role is available system-wide, it is not limited to a specific namespace.

A role-name can be any valid identifier of up to 64 characters. A role-name must follow identifier naming conventions. A role name can contain Unicode characters. Role names are not case-sensitive. A role-name can be a delimited identifier enclosed in quotation marks, if the Support Delimited Identifiers configuration option is checked (the default). If a delimited identifier, role-name can be an SQL reserved word. It can contain a period (.), caret (^), and the two-character arrow sequence (->). It cannot contain a comma (,) or a colon (:) character. It may begin with any valid character, except the asterisk (*).

When initially created, a role is just a name; it has no privileges. To add privileges to a role, use the GRANT command. You can also use the GRANT command to assign one or more roles to a role. This permits you to create a hierarchy of roles.

If you invoke CREATE ROLE to create a role that already exists, SQL issues an SQLCODE -118 error. You can determine if a role already exists by invoking the $SYSTEM.SQL.Security.RoleExists()Opens in a new tab method:

  WRITE $SYSTEM.SQL.Security.RoleExists("%All"),!
  WRITE $SYSTEM.SQL.Security.RoleExists("Madmen")

This method returns 1 if the specified role exists, and 0 if the role does not exist. Role names are not case-sensitive.

To delete a role, use the DROP ROLE command.

Privileges

The CREATE ROLE command is a privileged operation. Before using CREATE ROLE in embedded SQL, you must be logged in as a user with one of the following:

The %Admin_Secure administrative resource with USE permission.
The %Admin_RoleEdit administrative resource with USE permission.
Full security privileges on the system.

If you are not, the CREATE ROLE command results in an SQLCODE -99 error (Privilege Violation). Use the $SYSTEM.Security.Login()Opens in a new tab method to assign a user with appropriate privileges:

   DO $SYSTEM.Security.Login(username,password)
   &sql(      )

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login() method. For further information, refer to %SYSTEM.SecurityOpens in a new tab in the InterSystems Class Reference.

Arguments

role-name

The name of the role to be created, which is an identifier. Role names are not case-sensitive.

Examples

The following examples attempt to create a role named BkUser. The user “FRED” in the first example does not have create role privileges. The user “_SYSTEM” in the second example does have create role privileges.

  DO $SYSTEM.Security.Login("FRED","FredsPassword")
  &sql(CREATE ROLE BkUser)
  IF SQLCODE=-99 {
    WRITE !,"You don't have CREATE ROLE privileges" }
  ELSEIF SQLCODE=-118 {
    WRITE !,"The role already exists" }
  ELSE {
    WRITE !,"Created a role. Error code is: ",SQLCODE }

  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
Main
  &sql(CREATE ROLE BkUser)
  IF SQLCODE=-99 {
    WRITE !,"You don't have CREATE ROLE privileges" }
  ELSEIF SQLCODE=-118 {
    WRITE !,"The role already exists" }
  ELSE {
    WRITE !,"Created a role. Error code is: ",SQLCODE }
Cleanup
   SET toggle=$RANDOM(2)
   IF toggle=0 { 
     &sql(DROP ROLE BkUser)
     WRITE !,"DROP USER error code: ",SQLCODE
   }
   ELSE { 
     WRITE !,"No drop this time"
     QUIT 
   }

(The $RANDOM toggle is provided so that you can execute this example program repeatedly.)