|
Removes a role from the database, or converts a user-extended role back to a regular user.
DROP ROLE [ FROM USER ] role-name [ WITH { REVOKE | DROP OBJECTS } ]
Specify the name of the role you are dropping or converting.
Specify this clause to convert a user-extended role back to a regular user. The user retains any login privileges, system privileges, and roles they had.
Specify WITH REVOKE when there are other users who have been granted role-name.
Specify WITH DROP OBJECTS to drop the objects owned by role-name. If any of the objects cannot be dropped, for example because the object is currently in use, then the statement returns an error. You cannot specify this clause if role-name is a user-extended role.
A user-defined role can be dropped from the database, and a user-extended role can be converted back to a regular user, as long as all dependent roles meet the minimum required number of administrative users with active passwords, as set by the min_role_admin database option.
When you convert a user-extended role back to a regular user, ownership of objects remains with the user that is being converted back to a regular user.
When you convert a user-extended role back to a regular user, any privileges that were granted to role-name remain with the user after they have been converted.
If you convert a user-extended role back to a regular user and any other roles and/or users were granted the user-extended role, the WITH REVOKE clause must be specified or else the statement returns an error message and fails.
If any objects impacted by the drop operation are in use, the statement returns an error message and the statement fails.
You must have administrative rights for the role being dropped.
Automatic commit
Not in the standard.
The following statement converts a user-extended role named Joe back to a regular user. Objects owned by the user-extended role are now owned by the regular user, Joe. Users or roles that had been granted Joe retain the underlying privileges associated with the role.
DROP ROLE FROM USER Joe;
The following statement drops a user-extended role named Jack from the database. If the role Jack owned any objects, ownership of the object reverts to user Jack. Users or roles that were granted Jack retains the underlying privileges associated with the role Jack.
DROP ROLE Jack;
The following statement converts a user-extended role named Sam back to a regular user. Users and roles who had been granted Sam has the privileges of Sam revoked.
DROP ROLE FROM USER Sam WITH REVOKE;
The following statement drops a role named Sales1. Users or roles that were granted Sales1 retain the underlying privileges associated with the Sales1.
DROP ROLE Sales1;
The following statement drops a role named Sales2. Users or roles that had been granted Sales2 lose all underlying privileges associated with Sales2.
DROP ROLE Sales2 WITH REVOKE;
The following statement converts a user-extended role named Marketing1 to a regular user named Marketing1, and drops any objects that it owned.
DROP ROLE FROM USER Marketing1 WITH DROP OBJECTS;
The following statement drops a role named Marketing2, drops the objects it owned, and revokes its underlying system privileges from those who had been granted the role.
DROP ROLE Marketing2 WITH REVOKE WITH DROP OBJECTS;