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 .. 151 152 153 154 155 156 < 157 > 158 159 160 161 162 163 .. 241 >> Next

REFERENCES Not applicable on a table or column of the table
INDEX Not applicable on a table

The same privilege is required when a macro references an object owned by a user other than the macro’s creator. For example, assume that the macro Bobby.MacroB deletes rows from Allen.ViewA. Bobby must have the GRANT OPTION for the EXECUTE privilege on Allen.ViewA in order to grant the EXECUTE privilege on Bobby.MacroB to another user.

Teradata RDBMS for UNIX SQL Reference

8-159
Teradata SQL Syntax Guide

GRANT Statement (SQL)

Case Example (Views)

Assume that the view Bobby.ViewB is derived from the view Allen.ViewA, which in turn is derived from Allen.BaseTable. When Bobby enters the following statement:

GRANT INSERT, DELETE ON Bobby.ViewB TO Chuck ;

the system checks that the following privileges exist.

Stage Process
1 Bobby has INSERT WITH GRANT OPTION on Bobby.ViewB
2 Bobby has DELETE WITH GRANT OPTION on Bobby.ViewB
3 Bobby has INSERT WITH GRANT OPTION on Allen.ViewA
4 Bobby has DELETE WITH GRANT OPTION on Allen.ViewA
5 Allen has INSERT WITH GRANT OPTION on Allen.BaseTable
6 Allen has DELETE WITH GRANT OPTION on Allen.BaseTable

Of these privileges, two are automatic privileges, and two are explicitly granted privileges.

Ownership privileges cannot be revoked. For any other type, however, the Teradata RDBMS could fail to find one or more of the necessary privileges. In this case, an error message is returned to the user submitting the GRANT statement and the statement is not executed.

The breakdown of privilege types for this example is as follows.

FOR this category of privilege . . . THIS individual . . . HAS this privilege . . . ON this object . . .
Automatic Bobby INSERT WITH GRANT OPTION Bobby.ViewB
DELETE WITH GRANT OPTION
Allen INSERT WITH GRANT OPTION Allen.BaseTable
DELETE WITH GRANT OPTION

8-160

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

GRANT Statement (SQL)

FOR this category of privilege . . . THIS individual . . . HAS this privilege . . . ON this object . . .
Explicit Bobby INSERT WITH GRANT OPTION Allen.ViewA
DELETE WITH GRANT OPTION

Assume that Allen creates the following macro:

Scenario (Macros)

v 7 CREATE MACRO Allen.MacroA AS

(INSERT INTO Allen.BaseTable...

DELETE FROM Allen.BaseTable...) ;

and that Bobby then creates this macro:

CREATE MACRO Bobby.MacroB AS (EXECUTE Allen.MacroA) ;

If Bobby now tries to grant the EXECUTE privilege on MacroB to Chuck, as follows:

GRANT EXECUTE, DROP MACRO ON Bobby.MacroB TO Chuck ;

the Teradata RDBMS checks that the following rights exist:

THIS individual . . . HAS this privilege . . . ON this object . . .
Bobby EXECUTE WITH GRANT OPTION Bobby.MacroB
DROP MACRO WITH GRANT OPTION
EXECUTE WITH GRANT OPTION Allen.MacroA
Allen INSERT WITH GRANT OPTION Allen.BaseTable
DELETE WITH GRANT OPTION

Again, if the Teradata RDBMS fails to find one or more of the required automatic or explicit privileges, the statement is not executed and returns an error.

Teradata RDBMS for UNIX SQL Reference

8-161
Teradata SQL Syntax Guide

GRANT Statement (SQL)

Examples

Example 1

Example 2: PUBLIC

Example 3: SELECT on Any Object

Example 4: SELECT, INSERT, UPDATE, and DELETE on Any Object

Example 5: CREATE, DROP Users

Example 6: GRANT SELECT To ALL

Example 7: WITH GRANT OPTION

The following examples illustrate the use of GRANT:

The following statement grants privileges to a group of users. In this example, all users created under the Finance database are granted the right to SELECT data from the Department table, which is in the Personnel database:

GRANT SELECT ON Personnel.Department TO ALL Finance ;

The PUBLIC keyword grants privileges to all users of the Teradata RDBMS:

GRANT SELECT ON Personnel.Department TO PUBLIC ;

The following statement allows Moffit to retrieve information from any object in the Personnel database:

GRANT SELECT ON Personnel TO Moffit ;

The following statement allows Peterson to perform select, insert, update, and delete operations on any object in the Personnel database:

GRANT SELECT, INSERT, UPDATE, DELETE ON Personnel TO Peterson ;

The following statement allows Phan to create and drop users in the Personnel database:

GRANT USER ON Personnel TO Phan ;

The following statement allows all current and future users created under the Personnel database to select data from the Department table:

GRANT SELECT ON Personnel.Department TO ALL Personnel ;

The WITH GRANT OPTION extends the GRANT option for only the indicated privileges. The following statement gives Marston all privileges (that the grantor has) on the space created under his user name which are grantable at the user level, and which the grantor has WITH GRANT OPTION and allows him to grant those privileges (optionally, WITH GRANT OPTION) to other users:

GRANT ALL ON Marston TO Marston WITH GRANT OPTION ;
Previous << 1 .. 151 152 153 154 155 156 < 157 > 158 159 160 161 162 163 .. 241 >> Next