16.20 - REVOKE (Role Form) - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ SQL Data Control Language

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1149-162K

Purpose

Revokes a role from users or other roles.

Required Privileges

To revoke a role, you must have the WITH ADMIN OPTION privilege on it. The following users can revoke role membership:

  • User DBC.
  • A user who was granted the specified role WITH ADMIN OPTION.

    A role is automatically granted to the creator of the role WITH ADMIN OPTION.

  • A user who has an active role to which the specified role was granted WITH ADMIN OPTION. An active role can be either a current role or a nested role of a current role.

Syntax



Syntax Elements

ADMIN OPTION FOR
that the roles and users who were granted this role lose the privilege to use GRANT, REVOKE, and DROP ROLE statements to administer the specified role.
If you do not specify ADMIN OPTION FOR in the REVOKE request, the system revokes the specified role from the roles or users to which it was granted.
role_name
one or more comma-separated names of roles that are being revoked.
You can specify a maximum of 25 names per REVOKE request.
The system ignores duplicate role names.
TO
FROM
user_name
role_name
the names of roles or users or both from which the role or the ability to administer the role is being revoked.
The system does not return errors for users or roles that were not previously granted the specified role.
The TO keyword is a Teradata extension to ANSI/ISO SQL:2011.

Example of Revoking One Role and Granting Another

If user marks gets a promotion from sales to management, the DBA can use the following statements to revoke the sales role and grant the management role:

     REVOKE sales
     FROM marks;
     
     GRANT management
     TO marks;

The Effects of Revoking A Role

Roles define privileges on database objects. A user who activates a role inherits all the privileges for the role and its nested roles. A user can only activate a role that has been granted to that user.

Users can undergo role changes within their organization. An administrator can revoke a role when users no longer require access to the objects that the role has privileges to. An administrator can also revoke the WITH ADMIN OPTION privilege on a role when users no longer require the privilege to grant the role to users or other roles.

Authorized users can revoke the WITH ADMIN OPTION privilege on a role from the creator of the role.

The effect of revoking a role is immediate. Users who are logged on with the revoked role as the current role or a nested role of the current role lose the privileges that the role defines.

Users who have a default role set to the revoked role do not receive errors or warnings the next time they log on. However, the system does not use the obsolete default role for privilege validation. If the role is again granted to users, the default role again becomes the current role the next time users log on.