DROP ROLE (SQL)

Deletes a role.

Synopsis

DROP ROLE role-name

Arguments

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

Description

The DROP ROLE statement deletes a role. When you drop a role, Caché 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.RoleExists() 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 must have %Admin_Secure:USE privilege.
You are the owner of the role.
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() 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, refer to %SYSTEM.Security in the InterSystems Class Reference.

Examples

The following embedded SQL example creates a role named BkUser and later deletes it:

  DO $SYSTEM.Security.Login("MyName","SecretPassword")
  &sql(CREATE ROLE BkName)
  IF SQLCODE=-99 {
  WRITE !,"You don't have CREATE ROLE privileges" }
  ELSE { WRITE !,"Created a role"}
  /* Use role */
  &sql(DROP ROLE BkName)
  IF SQLCODE=-99 {
  WRITE !,"You don't have DROP ROLE privileges" }
  ELSE { WRITE !,"Dropped the role" }