Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - Database Administration » User and database security » User security (roles and privileges) » Roles » User-extended roles

 

Converting a user-extended role back to a user (SQL)

Change a user-extended role back to a regular user.

Prerequisites

You must have the MANAGE ROLES system privilege, or have administrative rights on the role.

Context and remarks

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.

 Task
  1. Connect to the database.

  2. Execute a statement similar to the following:

    Option Statement
    Convert the role back to a user, if the role is not granted to any roles or users
    DROP ROLE FROM USER userid;
    Convert the role back to a user, and revoke the underlying privileges from anyone the user-extended role had been granted to
    DROP ROLE FROM USER userid WITH REVOKE;

Results

The role is converted back to a user. Any objects that were owned by the user-extended role remain with the converted user. Any users or roles that previously had the user-extended role no longer have the privileges that the converted user has.

Next

If you are changing the user-extended role to a user to delete the user, you can now delete the user.

Example

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 converts a user-extended role named Sam back to a regular user. Users and roles who had been granted Sam will have the privileges of Jack revoked.

DROP ROLE FROM USER Sam WITH REVOKE;

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;

 See also