Skip to main content
Previous sectionNext section

DROP ROLE

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 InterSystems SQL.

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.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(      )
Copy code to clipboard

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" }
Copy code to clipboard

See Also