Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 81 82 83 84 85 86 < 87 > 88 89 90 91 92 93 .. 218 >> Next

privileges the requesting user has been granted, either automatically or explicitly DBC.UserRights
all roles directly granted to the requesting user DBC.RoleMembersX
Each role and every user or role to whom it has been granted DBC.RoleMembers
all rights granted to each role DBC.AllRoleRights
All rights grated to each role for the requesting user DBC.UserRoleRights
For more information about privileges, see the following references.
IF you want more information about ... THEN see ...
the GRANT statement, privilege types, using roles, and how to control user privileges "GRANT Statement" on page 6-11 "Controlling Inherited and Group Privileges" on page 6-15 Teradata RDBMS Security Administration
how to set up and maintain a secure database environment Teradata RDBMS Security Administration
views that return information about privileges Teradata RDBMS Data Dictionary
authorization for CREATE PROCEDURE Teradata RDBMS SQL Reference, Volume 4.

6 - 10 Teradata RDBMS Database Administration

Chapter 6: Controlling Access

Controlling Inherited and Group Privileges

IF you want more information about ... THEN see ...
authorization for and how to use the performance monitoring tools Appendix D: "Tools for Monitoring Performance" Teradata RDBMS PM/API Reference Performance Optimization
authorization for and how to set up and use the resource usage monitoring tools Teradata RDBMS Resource Usage Macros and Tables.

6 - 10

Teradata RDBMS Database Administration Chapter 6: Controlling Access

REVOKE Statement

GRANT Statement

You can use the GRANT statement to explicitly give users and roles privileges on a database, user, table, view, macro, join index, trigger, or stored procedure, and to grant a role to a user or another role.

To grant a role to a user or another role, you must have the WITH ADMIN OPTION on the role.

To grant a privilege on an object to a user or role, you must:

Have the privilege itself and have the WITH GRANT OPTION on the privilege

Have that privilege on, or be the owner of, the same object you are granting the privilege on

The WITH GRANT OPTION confers on the recipient "Grant Authority." The recipient (grantee) holding this authority then may grant the privilege to other users or databases. The recipient of an explicitly granted privilege may be:

Recipient of WITH GRANT OPTION Description
username The individual user or users named. Up to 25 can be specified.
PUBLIC Every user in the DBC system.
All username The named user and all descendants in the hierarchy. If username is DBC, then the statement is translated internally to PUBLIC.

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.

When using GRANT:

When you grant rights to PUBLIC, only a row for each right-object pair is inserted in DBC.AccessRights.

Granting privileges at the database level instead of at the object (table, view, macro, etc.) level can reduce the size of DBC.AccessRights.

Revoking object level privileges when there is database privilege for containing database can also reduce the size of DBC.AccessRights.

Teradata RDBMS Database Administration

6 - 11 Chapter 6: Controlling Access

REVOKE Statement

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 its tables, views, macros, triggers, join indexes, or stored procedures.

For more information on roles and the WITH ADMIN OPTION, see "Implementing Roles and Profiles" on page 5-12.For a complete discussion about the ramifications of using GIVE, GRANT, and the WITH GRANT OPTION, see Teradata RDBMS Security Administration.

6 - 12

Teradata RDBMS Database Administration 6 - 11 Chapter 6: Controlling Access

REVOKE Statement

REVOKE Statement

You use the REVOKE statement to take away (from a user, role, PUBLIC, or ALL) a granted privilege on a database, user, role, profile, table, view, macro, join index, trigger, or stored procedure.

To revoke a privilege, you must either:

Be an owner of the object

The same privilege you are revoking, plus have GRANT authority

Affects on DBC.AccessRights System Table

REVOKE removes the following rows from DBC.AccessRights, if they exist:

Automatic privileges inserted in DBC.AccessRights as the result of a CREATE statement

Explicitly granted privileges inserted in DBC.AccessRights as the result of a GRANT statement
Previous << 1 .. 81 82 83 84 85 86 < 87 > 88 89 90 91 92 93 .. 218 >> Next