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 .. 19 20 21 22 23 24 < 25 > 26 27 28 29 30 31 .. 218 >> Next


1 - 10

Teradata RDBMS Database Administration Chapter 1: Database Hierarchy

Databases versus Users

Ownership versus Creator Privileges

When you create a new database or user, you automatically gain certain privileges to the associated space but also are subject to certain rules:

As the creator of an object, you have the right to modify, alter, or drop that object. If the object is a table, you can retrieve, update, or delete data rows. If the object is a database, user, or stored procedure, you must have been explicitly granted the associated CREATE privilege by the administrative user (unless you log on as user DBC), even to create a user, database, or stored procedure in your own space. As a newly created user, you are automatically given the CREATE privilege to create a table, view, index, and macro in your own space.

A user does not own itself; therefore, creating a user does not grant to the newly created user any ownership rights on his or her own space.

EXECUTE privileges usually involve access privileges to the target objects, in addition to the privilege on the object to be executed. Requirements and rules are given in the appropriate statement in Teradata RDBMS SQL Reference, Volume 6.

Explicit, Automatic, and Implicit Privileges

Privileges (also called rights) can be explicitly, automatically, or implicitly granted. Implict/automatic/explicit privileges are always in the context of a specific combination of:

GRANT privilege ON object BY user [with or without GRANT OPTION] Explicitly and Automatically Granted Privileges

Explicit and automatic privileges are granted when an SQL GRANT statement is executed. Both types of privileges are logged in the DBC.AccessRights system table and both types can be revoked. (Sometimes the term explicit means all the privileges in DBC.AccessRights, whether placed there automatically, such as by the system via a DIP script, or explicitly by a user, since you cannot really tell which method was used to generate the entry.) |

Entries in DBC.AccessRights can be retrieved through the DBC.AllRights view. This view returns all users who have been explicitly and automatically granted privileges, and the objects on which the privileges were granted. (For details, see "Dictionary Views and Tables for the Administrator" on page 4-7.)

An explicit or automatic privilege is the right to take action on (access) an object or another database or user, as granted by one user (the grantor) to another; for example:

GRANT SELECT ON UserA.TestTable TO UserB; The grantor must already have the right to issue the GRANT option.

1 - 10 Teradata RDBMS Database Administration

Chapter 1: Database Hierarchy

Databases versus Users

The right to confer the GRANT option to a third or subsequent user can be conferred using the WITH GRANT OPTION of the GRANT statement.

For example, if UserA creates TestTable in his own space and then grants UserB the right to select data from that table, UserA can also grant to UserB the right to grant the SELECT privilege to other users. For example:

GRANT SELECT ON UserA.TestTable TO UserB WITH GRANT OPTION ;

Now UserB has the right to grant SELECT on UserA.TestTable to UserC or to UserD, and so on.

Automatic privileges are privileges that are given automatically to a user or database when an object is created (some are given to the creator and some to the created user/database) .

For example, a newly created user is automatically given the right to create a table in his or her own space, and the creator of a table is automatically given the right to alter or drop that table.

Implicit Privileges

Implicit privileges are privileges a user has on an object because he or she owns that object, either directly (the immediate owner) or indirectly. Sometimes implicit privileges are called ownership privileges.

Implicit privilege cannot be revoked and they are not logged in DBC.AccessRights.

Implicit rights are used to allow certain actions to be taken under certain conditions, without having to be explicitly granted the right to do so. For example:

The recipient of a GRANT ... WITH GRANT OPTION statement implictly gains the right to REVOKE any privilege he or she may grant.

A user granted the DUMP privilege implicitly gains the right to RESTORE the data he or she archives.

In the case of ownership, explicit rights do not have to exist except when the owned object targets objects that are owned by others.

For example, you may own a stored procedure, but if the procedure accesses tables owned by another user, you must have been explicitly granted the appropriate privileges (SELECT, INSERT, and/or UPDATE, and so forth) on those tables.

For more information on privileges and the GRANT statement, see Teradata RDBMS SQL Reference, Volume 6.

1 - 10

Teradata RDBMS Database Administration Chapter 1: Database Hierarchy

Changing the Hierarchy with GIVE

Changing the Hierarchy with GIVE
Previous << 1 .. 19 20 21 22 23 24 < 25 > 26 27 28 29 30 31 .. 218 >> Next