Caché SQL Reference
CREATE ROLE
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Creates a role.
Synopsis
CREATE ROLE role-name
Arguments
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 30 characters. A role name must follow identifier naming conventions. If the role name is a delimited identifier enclosed in quotation marks, it 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() 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() 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.Security 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