Download (direct link):
• Removes for the object, if revoking a right from PUBLIC, one row in DBC.AccessRights for each public right.
Note: All users are equal members of PUBLIC; no one can be selectively denied a public right. Once a right on an object is revoked from PUBLIC, only an owner (for some rights), or a user or role who has been or is later individually granted the right, can perform that function on the object. (Also, for some rights, an owner can re-grant itself that right explicitly.)
The REVOKE statement can remove privileges (and thus rows from DBC.AccessRights table) for the object-privilege pair, based on the following:
IF you REVOKE FROM ... THEN the specified privileges are removed from .
username the specified user.
rolename each member of this role (every user and/or other role to whom this role has been granted).
PUBLIC • For releases prior to V2R5.0, every user other than DBC in Teradata RDBMS. (An object owner still has the privilege to re-grant privileges to specific users or roles.) Processing is the same as if the privilege was revoked from all the descendants of DBC in the hierarchy; that is, every user except DBC (because DBC owns everything). • For release V2R5.0 and later, username PUBLIC, which has one row per object-privilege pair.
Teradata RDBMS Database Administration
6 - 11Chapter 6: Controlling Access
IF you REVOKE FROM . THEN the specified privileges are removed from .
ALL username the specified user and all its descendents in the hierarchy.
ALL DBC every user in Teradata RDBMS. Use this form of REVOKE to remove those old PUBLIC rows from DBC.AccessRights after upgrading to V2R5.0 or later. (On pre-V2R5.0 releases, a grant to PUBLIC caused one object-privilege row to be inserted for every user in Teradata RDBMS.
It is important to note that the REVOKE statement:
• Is not automatically issued for privileges granted by a grantor dropped from the system.
• Does not cascade through the hierarchy unless you specify ALL.
• Can remove the ADMIN OPTION from the creator of a role, so thereafter the creator will not be allowed to grant, revoke, or drop the role he created.
• Cannot be used to revoke implicit rights due to ownership.
• Removes, for a privilege granted at the database or user level, all rows of that privilege for all objects within the database or user.
Note: A GRANT statement issued for a database or user does not insert rows in DBC.AccessRights for the individual tables, views, macros, triggers, indexes, or stored procedures below that database or user. Therefore, you cannot revoke specific privileges at the object level for any of the tables, views, macros, triggers, join indexes, or stored procedures.
If any user issues a GRANT ALL ON . . . TO PUBLIC statement on a database or user lower in the hierarchy than DBC, all other users have privileges on that database/user, including users created after the GRANT statement was issued.
If user DBC then issues a REVOKE ALL ON . . . FROM DBC, users created after the REVOKE statement is issued do not have privileges on that object.
However, all previously created users retain the privileges until user DBC issues a REVOKE ALL ON . . . FROM PUBLIC.
For a complete discussion of access rights and privileges and the ramifications I of using GRANT, WITH GRANT OPTION, WITH ADMIN OPTION, and
REVOKE statements, see Teradata RDBMS Security Administration.
6 - 14
Teradata RDBMS Database Administration 6 - 11Chapter 6: Controlling Access
Controlling Inherited and Group Privileges
Controlling Inherited and Group Privileges
Inherited privileges depend on placement of user space in the hierarchy and the use of ALL.
If you grant privileges to a user on his or her space with the ALL option, then a child user (created below that user in the hierarchy) inherits those privileges and any privileges granted in the future. Otherwise, a new user created by a user in his or her own space only has automatically granted rights.
When rights are inherited via ALL, DBC.AccessRights is updated with a row for the privilege-user pair of each inherited right.
Note: Inheritance applies to users only when they are created. It does not apply when using GIVE.
You can control user privileges at the group level using the following process:
1 Create one or more roles.
2 Grant the appropriate access privileges on the appropriate objects to each role.
3 Grant one or more roles to one or more users (enabling each grantee user to acquire the granted role's access privileges during a session).
4 Define a granted role as the default for one or more grantee users.
Any user who logs on with a valid default role has all the privileges that have been granted to that role on the specified object.
You define a default role for a user in a CREATE/MODIFY USER statement. For instructions, see "Implementing Roles" on page 5-13.