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


You can also use a macro to insert records in an audit trail table to monitor system use, and to record this use for accounting purposes.

For more information on macros, see:

Teradata RDBMS SQL Reference, Volume 1 and Volume 4

Teradata RDBMS Data Dictionary

Teradata RDBMS Database Administration

6 - 39 Chapter 6: Controlling Access

Limiting Data Access with Stored Procedures

Limiting Data Access with Stored Procedures

Another method of controlling user access to data is through the structure of stored procedures. You can use stored procedures to limit the actions a user can take on table and view columns and rows.

In order to create a stored procedure, you must be explicitly granted the CREATE PROCEDURE privilege on the space in which they will reside (even your own database), and you must have every privilege needed to process the statements and access the target entities in the procedure body. You receive automatically the EXECUTE PROCEDURE and DROP PROCEDURE privileges on any procedure you create.

When a stored procedure is processed, Teradata RDBMS checks the immediate owner of the procedure for access rights on all SQL statements and target objects. If you want another user to execute stored procedures you create in your own space, you must explicitly grant to that user the EXECUTE PROCEDURE privilege, and you yourself must be explicitly granted every privilege, each including the WITH GRANT OPTION, needed to process the statements and access the target entities in the procedure body.

The rules governing the use of types of SQL statements within a stored procedure depend on whether the user is the immediate owner of the stored procedure being created or executed. The basic rules depend on whether the creator is also the immediate owner, as follows:

When the creator is . THEN .
also the immediate owner (the user or database where the procedure was created) some DDL and DCL statements and dynamic SQL statements are supported within the stored procedure during the procedure creation.
not the immediate owner DDL, DCL and dynamic SQL statements are not supported within a stored procedure. Specifying such statements results in compilation errors and the stored procedure is not created.

To use stored procedures to control data access, you need to know about:

Supporting client interfaces and administrative considerations (see "Stored Procedures" on page 2-75).

Privilege requirements, statement syntax, rules of creation, use, and execution, control statements, condition handling, and applications (see "Stored Procedures" in Teradata RDBMS SQL Reference, Volume 6).

Options and use of the GRANT statement (see "SQL Data Control Language Statement Syntax" in Teradata RDBMS SQL Reference, Volume 4).

6 - 44

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

Logging Access Attempts

Logging Access Attempts

Teradata RDBMS supports C2 security. Access Logging can check SQL requests submitted by users who attempt to access or execute data objects. Information can be tracked by:

Type of access

Type of request

Requesting user

Referenced object

Frequency of access or attempted access

Note: Access checking and logging is very resource-intensive. Initialize this feature only if necessary, and define rules on an as-needed basis.

For more information on setting up a secure database environment, a security administration user, and logging access rules, and see:

Teradata RDBMS Security Administration

Teradata RDBMS Data Dictionary

Teradata RDBMS SQL Reference, Volume 1

Enabling Access Logging

For logging of access checks to occur, the DBC.AccLogRule macro must exist. (if it does not, run the DIP utility and execute the DIPACC script; for instructions, see Teradata RDBMS Utilities) and you must have EXECUTE privilege on it as explained in Teradata RDBMS Security Administration.

You activate access checking by defining the rules in one or more BEGIN LOGGING statements. The rules you specify are stored in system table DBC.AccLogRuleTbl. You can review all the rules currently in force by querying the DBC.AccLogRules view.

Every time a user defined for logging attempts to access an object, Teradata generates at least one entry in system table DBC.AccLogTbl. To review the access activity of current users, query the DBC.AccessLog view.

Note: Access logging and index analysis are not allowed on the same object at the same time. If logging has been enabled for a database and that database is referenced in an INDEX ANALYSIS statement, the statement returns:

*** Failure 6818 Index Analysis is not allowed when access logging is enabled.

Teradata RDBMS Database Administration

6 - 45 Chapter 6: Controlling Access

Logging Access Attempts

Disabling Access Logging

When you want to disable access checking, it is important you perform the following procedure:

Step Action
Previous << 1 .. 92 93 94 95 96 97 < 98 > 99 100 101 102 103 104 .. 218 >> Next