Skip to main content

CREATE ROLE

Creates a role.

Synopsis

CREATE ROLE role-name

Arguments

Argument Description
role-name The name of the role to be created, which is an identifier. Role names are not case-sensitive. For further details see the “Identifiers” chapter of Using Caché SQL.

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, with the following restriction. If the role name is a delimited identifier enclosed in quotation marks, it cannot contain a comma (,) or a colon (:) character. A delimited identifier can be an SQL reserved word. A role name can contain Unicode characters. Role names are not case-sensitive.

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.RoleExists()Opens in a new tab method:

  WRITE $SYSTEM.SQL.RoleExists("%All"),!
  WRITE $SYSTEM.SQL.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, it is necessary to be logged in as a user with %Admin_Secure:USE privilege. Failing to do so 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.

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

See Also

FeedbackOpens in a new tab