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

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 90 91 92 93 94 95 < 96 > 97 98 99 100 101 102 .. 218 >> Next

Granting or Revoking Privileges

To grant to another user any privilege on an object the grantor does not own, the grantor must have the been explicitly granted the same privilege along with the WITH GRANT OPTION phrase.

Initially, any owner of a database has complete control over the security of data in that database. If an owner grants privileges (via the GRANT statement) to other users, the owner is sharing control. By granting and revoking privileges, each user can control access to his or her own data. For example, the immediate owner may rescind any privilege on the database that has been granted to one or more users (including the immediate owner) using the REVOKE statement.

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

An owner may even give up ownership altogether using the GIVE statement, which relinquishes control of space and its contents to another user. In this case, the original owner retains any explicit or automatic privileges it has on the space, but now these can be revoked. Unless an owner gives up ownership, he or she retains ultimate control of the database and of users and databases lower in the hierarchy.

Teradata RDBMS Database Administration

6 - 37 Chapter 6: Controlling Access

Access Control Mechanisms

Space Allocation Scenario

Suppose the following occurs:

The immediate owner of a database allocates space in the database to create user A.

User A creates a table in the permanent space allocated and grants privileges on the table to user B, WITH GRANT OPTION.

User B grants the privileges received to user C.

Users can then perform any of the following:

User A can revoke any privilege that user A has granted to user B, or that user B has granted to user C. Likewise, user B can revoke a privilege user B has granted to user C.

The immediate owner can drop user A altogether (after first dropping all user A data) to reclaim the permanent space allocated to user A.

User DBC, at the top of the database hierarchy, retains ultimate control of all

users and databases.

6 - 38

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

Limiting Data Access with Stored Procedures

Limiting Data Access with Views

Another method of controlling user access to data is through the use of exclusion views. Because a view executes a SELECT on one or more tables, you can structure views using specific column names in order to exclude columns of sensitive data (for example, salaries), and you can use the WHERE clause to exclude particular rows.

When you submit the CREATE VIEW statement, Teradata RDBMS:

Fully expands the view name (resolves the database and table names)

Verifies that the creating user has the appropriate privilege on the objects being referenced, which can be implicit or explicit privileges

The immediate owner must have WITH GRANT OPTION for these privileges if the user for the session is not the immediate owner

You need to grant the appropriate privileges to users you want to use the view.

IF you want to ... THEN you should . For more information, see .
build a virtual firewall between users and the tables they access structure task-oriented views. "Isolating Users from the Database Using Views" in Teradata RDBMS Database Design
let another user select one or more columns from the view GRANT to that user the SELECT privilege on: The view Each underlying table
let another user update one or more of the underlying tables through the view GRANT to that user the UPDATE and the SELECT privileges on: The view Each underlying table Note: If an update or delete on a table or view includes a search condition, which requires read access to find candidate rows, also grant the SELECT privilege.
restrict user access to dictionary data using views review the All_RI_Child and All_RI_Parent views and system views with the X suffix. Chapter 4: "Using Data Dictionary Tables and Views" Teradata RDBMS Data Dictionary

Teradata RDBMS Database Administration

6 - 39 Chapter 6: Controlling Access

Limiting Data Access with Stored Procedures

Updating Tables Through a View

You can grant another user the privilege of selecting or updating, adding, or deleting rows in the underlying table. A possible scenario is as follows:

IF receptionists need to ... THEN grant them .
periodically update information about employee address, home telephone, and extension just for their department Either: UPDATE privilege on the columns that are allowed to be updated. Such a view also may include WHERE constraints on the data itself. Both UPDATE and SELECT privileges on another view of the same table that includes names and extensions only for employees in their department. (In this case, the receptionist would have to be careful not to change an employee name via the view.)
Previous << 1 .. 90 91 92 93 94 95 < 96 > 97 98 99 100 101 102 .. 218 >> Next