DROP ROLE (SQL)

Deletes a role.

Synopsis

DROP ROLE [IF EXISTS] role-name

Description

The DROP ROLE statement deletes a role. When you drop a role, InterSystems IRIS revokes it from all users and roles to whom it has been granted and removes it from the database.

You can determine if a role exists by invoking the $SYSTEM.SQL.Security.RoleExists()Opens in a new tab method. If you attempt to drop a role that does not exist (or has already been dropped), DROP ROLE issues an SQLCODE -118 error.

Privileges

The DROP ROLE command is a privileged operation. Prior to using DROP ROLE in embedded SQL, it is necessary to fulfill at least one of the following requirements:

You are the owner of the role.
You are logged in 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
You were granted the role WITH ADMIN OPTION.

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("_SYSTEM","SYS")
   &sql(      )

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, see %SYSTEM.SecurityOpens in a new tab.

Arguments

IF EXISTS

An optional argument that suppresses the error if the command is executed on a nonexistent role.

role-name

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

Examples

The following example creates a role named BkUser and then deletes it:

CREATE ROLE BkName
DROP ROLE BkName