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 .. 91 92 93 94 95 96 < 97 > 98 99 100 101 102 103 .. 218 >> Next

access the company telephone extensions of all employees SELECT privilege on a view that includes only employee name and telephone extension from a table of information about company employees.

For example, if the extension numbers to be updated ranged from 105 to 130 and did not include 123 or 127, the view definition could include the following clause to prevent the receptionist from mistakenly entering an extension for another department:

WHERE Extension BETWEEN 105 AND 13 0

AND Extension NOT IN (123, 127) WITH CHECK OPTION

Example of an Exclusion View

The following statement defines a view that excludes Salary information:

CREATE VIEW Employee_Info

AS SELECT EmpNo, Name, JobTitle, DeptNo FROM Employee;

Adding a WHERE clause restricts the view even further to show only data about employees in the Manufacturing department:

WHERE DeptNo = 600;

If you want to show all departments but restrict updates or inserts through this view to department 600, you can use the following condition:

WHERE DeptNo = 600 WITH CHECK OPTION

Example of Renaming Table Columns in a View

The following example illustrates a view limiting access to relevant and non-sensitive data and replacing each column name in the table with a title suitable for a report. The GRANT statement gives read access to all users (PUBLIC).

6 - 40

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

Limiting Data Access with Stored Procedures

For example, assume you create a new view on Employee called Dept_401 to display non-sensitive data on employees of Department 401 only. The structure might look something like this:

CREATE VIEW Payroll.Dept_4 01 AS

SELECT EmpNo (TITLE 'EMPLOYEE//NUMBER')

,Name (TITLE 'FULL NAME'(FORMAT ~X(35)'))

,HireDate (TITLE 'HIRE//DATE'(FORMAT 1YYYY-MM-DD'))

FROM Payroll.Employee

WHERE DeptNo = 401;

GRANT SELECT ON

Payroll.Dept_4 01 TO PUBLIC ;

When end users access the view, they see what looks like a complete table. They do not know that the underlying base table contains more columns. For example, this query:

SELECT * FROM PAYROLL.DEPT_4 01;

returns the following:

EMPLOYEE FULL NAME HIRE

NUMBER DATE

1006 Stein, John 861015
1008 Kanieski, Carol 870201
1005 Ryan, Loretta 861015
1004 Johnson, Darlene 861015
1007 Villegas, Arnando 870102
1003 Trader, James 860731

Using Nested Views

Views that reference other views are nested views. Access to the underlying

objects is as follows:

Nested views are fully expanded (database and all underlying tables resolved) at creation time

The executing user needs privileges on all objects accessed by the nested view.

Teradata validates the nested view privileges of the creator at creation time

Teradata validates nested view privileges of the executing user at execution time

Teradata RDBMS Database Administration

6 - 39 Chapter 6: Controlling Access

Limiting Data Access with Stored Procedures

Example of Nested View Privilege Checking

View Y -> View X -> Table A

User 1 accesses View Y.

User 2 owns View Y.

Database VMDB owns View X.

Database DBX owns Table A.

Privileges checked are:

User 1 privileges on View Y

User 2 privileges on View X WITH GRANT OPTION

Database VMDB privileges on Table A WITH GRANT OPTION

If you REVOKE an explicit privilege from any user in the chain, the system issues the following message:

3523 An owner referenced by the user does not have [privilege] access to [databasename.tablename].

For more information about views, see:

Teradata RDBMS SQL Reference, Volume 1

"CREATE VIEW" in Teradata RDBMS SQL Reference, Volume 4

Teradata RDBMS Performance Optimization

6 - 42

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

Limiting Data Access with Stored Procedures

Limiting Data Access with Macros

Another method of controlling user access to data is through the structure of macros. Macros limit those actions a user can perform. The actions can include DDL and DML.

For other users to access a macro, an owner (or someone with the privilege WITH GRANT OPTION) must grant other users the EXECUTE privilege on the macro plus the appropriate privileges on the target objects.

In addition, the immediate owner of the macro must have the appropriate privilege with the WITH GRANT OPTION. For objects referenced by the macro, WITH GRANT OPTION is not needed only if the user associated with the session is the same as the immediate owner of the macro.

For example, assume a user creates a macro called NewEmp that updates the Personnel.Employee table. If the creator then grants a personnel clerk the EXECUTE privilege on that macro, the clerk can enter new employee data as parameters to the NewEmp MACRO statement rather than using the INSERT statement. Thus, the clerk need not be aware of the database being accessed, the tables affected, or even the result.
Previous << 1 .. 91 92 93 94 95 96 < 97 > 98 99 100 101 102 103 .. 218 >> Next