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

Change a user to a user-extended role, which can then be granted to other users and roles. User-extended roles are convenient when you have a user with a desired set of system privileges and roles that you also want to grant to another user.

Prerequisites

You must have the MANAGE ROLES system privilege.

Context and remarks

You can specify administrators for the role during the conversion. If you do not specify administrators, by default any user with MANAGE ROLE system privilege can administer the role.

The user being extended to be a user-extended role cannot be specified as an administrator, nor be granted administrative rights over the role after the conversion is complete. However, if the user being extended has the MANAGE ROLES system privilege, and no administrators are specified at conversion time, then they can administer the role.

Task

Connect to the database.

Execute a CREATE ROLE statement similar to the following:

Description

Statement

Extended the user to be a role that any user with the MANAGE ROLES system privilege can administer.

CREATE ROLE FOR USER userid;

User is extended to a role that any user in list-of-administrator-ids can administer.

CREATE ROLE FOR USER userid 
WITH ADMIN list-of-administrator-ids;

Results

The user is extended into a role.

Next

You can grant the new role to other users and roles. When you grant a user-extended role to a user or another role, the grantee inherits all the system and object-level privileges that the user-extended role has, including any administrative rights.

Example

The following statement extends user JaneSmith to become a role that can be assigned to others.

CREATE ROLE FOR USER JaneSmith;

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

Task

See also