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 forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 150 151 152 153 154 155 < 156 > 157 158 159 160 161 162 .. 241 >> Next


Also, a REVOKE statement at the object level cannot remove from that object a privilege that was granted at the database or user level.

For those reasons, it is advisable to grant privileges at the object level rather than at the database level.

• Any owner implicitly has the WITH GRANT OPTION on an owned database, user, or object. Therefore, a user can explicitly grant any or all privileges on an owned database, user, or object to any other user. The exception to this is an object that is a view or macro that references a non-owned object on which the user does not have the required WITH GRANT OPTION.

Any automatically or explicitly granted privilege can be revoked using the REVOKE statement. Implicit ownership privileges cannot be revoked.

If it is necessary to maintain a security log of access attempts, see the “BEGIN/END LOGGING” statement.

When a user explicitly grants privileges to another user, certain rules determine whether, how, and on what object the requested privilege is implemented. The restrictions that apply to explicitly granted privileges are detailed in Table 8-3.

The first column of the table lists the privilege type, the second column describes restrictions if the privilege is granted on a database or user, and the third describes restrictions if the privilege is granted on a table, view, or macro.

When a CREATE VIEW statement is submitted, the Teradata RDBMS verifies that the creator has the SELECT privilege on the underlying tables and views. When a CREATE MACRO statement is submitted, the Teradata RDBMS verifies that the creator has the privileges needed to execute the statements in the macro body.

Teradata RDBMS for UNIX SQL Reference

8-157
Teradata SQL Syntax Guide

GRANT Statement (SQL)

The Teradata RDBMS also verifies that the appropriate privileges exist on the target objects for any user who attempts to access a view or execute a macro. This ensures that a change to a target object does not result in a violation of access rights when the view or macro referencing that object is invoked.

Additionally, in order to grant to another user any privilege on a view or macro that references objects owned by a third user, the grantor must have the required privilege WITH GRANT OPTION on those objects.

Assume Allen creates two objects: a table named Allen.BaseTable, and a view derived from that table named Allen.ViewA. The system verifies that Allen has SELECT right on Allen.BaseTable when ViewA is created.

Also assume Allen grants the SELECT privilege on ViewA to Bobby. Bobby then creates Bobby.ViewB, which is derived from (and thus references) Allen.ViewA. The system verifies that Bobby has SELECT right on Allen.ViewA when Bobby.ViewB is created.

Bobby now wants to grant the SELECT privilege on ViewB to Chuck. Before Bobby can do this, he must receive from Allen the SELECT privilege WITH GRANT OPTION on Allen.ViewA.

Table 8-3

Restrictions on Granted Privileges

Privilege Object (Database or User) Table, View, Macro
CREATE DATABASE CREATE USER CREATE granted for the specified space. Not applicable.
CREATE MACRO CREATE TABLE CREATE VIEW CREATE granted for the object type for the specified space. Not applicable.
DROP DATABASE DROP USER DROP granted for the specified space. Not applicable.
DROP MACRO DROP TABLE DROP VIEW DROP granted for the object type for the specified space. DROP granted for the specified macro, table, or view.
DATABASE MACRO TABLE USER VIEW CREATE and DROP granted for the type for the specified space. Not applicable.

Example

8-158

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

GRANT Statement (SQL)

Privilege Object (Database or User) Table, View, Macro
DELETE INSERT SELECT UPDATE Privilege applies to all tables or views in the specified database. UPDATE applies to table or column of the table. The owner of a view must have appropriate privileges on the underlying tables of the view in order for a grantee to use the granted rights on a view. Privilege applies only to the specified table or view. The owner of the view must have appropriate privileges on the underlying tables of the view.
EXECUTE Privilege applies to all macros in the specified database. Privilege applies to the specified macro only.
The owner of the macro must have appropriate privileges on the objects referenced by the macro in order for the grantee to use the privilege on the macro. The owner of the macro must have the appropriate privileges on the objects referenced by the macro.
ALL All privileges granted. Grants EXECUTE and DROP on a macro; DROP, DELETE,INDEX, INSERT, REFERENCES, SELECT, UPDATE, RESTORE, and DUMP on a data table; DROP, DELETE, INSERT, SELECT, and UPDATE on a view; INSERT, DUMP, RESTORE, and CHECKPOINT on a journal table.
DUMP RESTORE Privilege applies to all tables in the specified database. Privilege applies to the named data table or journal table only.
CHECKPOINT Privilege applies to the journal table in the specified database. Privilege applies to the named journal table.
Previous << 1 .. 150 151 152 153 154 155 < 156 > 157 158 159 160 161 162 .. 241 >> Next