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