Determining the Object Level for a BEGIN LOGGING Request - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

You should consider the object level used to grant access when specifying the object level for a logging request. For example, when a BEGIN LOGGING request specifies logging at the database level, all the tables in the database are candidates for log entries.

Use Case 1

Assume that:
  • database_a contains several tables
  • PUBLIC has the INSERT privilege on table_1 only
  • Logging is specified as follows:
    BEGIN LOGGING DENIALS ON FIRST INSERT ON DATABASE database_a”;
When an INSERT request is submitted against a table in database_a, the log entries are as follows:
  • A granted entry for the first INSERT into table_1.
  • A denied entry for the first attempt ed INSERT into any other table in the database.

Because logging is specified at the database level, additional INSERTs against other tables in the database as part of a multistatement request log the first INSERT into each table.

Use Case 2

Assume that:
  • database_a contains table_1 and table_2
  • User access privileges are at the database level
  • Logging is specified as follows:
    BEGIN LOGGING ON FIRST INSERT ON DATABASE database_a”

If rows are inserted into both table_1 and table_2 in the same request, the system makes a single log entry identifying first insert into each table.

If the access privilege is at the database level and the logging specification is at the table level, only actions against the table are considered for logging entries (the absence of an access privilege at the table level does not necessarily result in a log entry).

A single log entry is generated for the table according to the results of privilege checks at the table level and the database level.

Privilege Checks at Table Level and Database Level Result
Either check succeeds. System generates a “granted” log entry.
Neither check succeeds. System does not generate a “denied” log entry because the keyword DENIALS was not specified in the logging rule.

Use Case 3

A logging request specifying FIRST SELECT ON database_a.table_1 causes a log entry to be generated only if an access request is directly on the table, for example:

SELECT … FROM table_1
The following actions do not log access on database_a.table_1 because the user does not require direct access to table_1 to perform the request.
  • SELECT ... FROM view1_of_table_1
  • EXECUTE macro_1

where macro_1 contains the request SELECT … FROM table_1.