BEGIN LOGGING Syntax Elements - Teradata Vantage

DENIALS

Make entries only when statement execution fails because the user did not have the privilege set necessary to perform the request.

DENIALS is applied to only those actions listed in the BEGIN LOGGING request that contains it.

For example, two BEGIN LOGGING requests can specify the same object, user, and action, but different frequency and DENIALS options. This allows the user to log all denials, but only the first successful use of a privilege.

If this option is not specified, a log entry is made if the privilege check either fails or succeeds.

You cannot log DENIALS for DELETE, INSERT, SELECT, or UPDATE operations on row-level security-protected tables because the inability of users to access a row is due to row-level security enforcement by the constraint UDF rather than being the result of a normal database privilege check.

Also see the row-level security section for the operation variable later in this table.

WITH TEXT

The text of the request that caused the log entry is to be saved in the log.

If two BEGIN LOGGING requests specify the same user, object, and action, and one of the requests specifies the DENIALS option, then WITH TEXT can be specified on either one of the requests, on both requests, or on neither request.

• If you specify WITH TEXT only on the request that also specifies DENIALS, Vantage saves the text only when the entry is created as a result of a denial.
• If you specify WITH TEXT either without DENIALS or on both requests, Vantage saves the text.
• If you specify WITH TEXT on END LOGGING, Vantage does not save the text for the specified actions or the DENIALS specification, but the logging frequency is not affected.

logging_frequency

The frequency with which to log events.

A log entry is made for either the first, the last, the first and last, or each time during any session that the specified action is attempted against the specified object.

If logging is already initiated for an action on an object, a subsequent request to begin logging for the same object, action, user, and DENIALS specification causes only the current frequency of logging to be changed to whatever the new request specifies.

FIRST

A log entry is made the first time during any session that the specified action is attempted against the specified object.

The default is FIRST.

LAST

A log entry is made the last time during any session that the specified action is attempted against the specified object.

FIRST AND LAST

A log entry is made the first and last time during any session that the specified action is attempted against the specified object.

The only valid combination of logging frequencies is FIRST AND LAST.

EACH

A log entry is made each time during any session that the specified action is attempted against the specified object.

The only valid options for auditing row-level security enforcement are FIRST and EACH.

FOR CONSTRAINT constraint_name

The logging rule is for enforcement of the row-level security constraint_name.

You cannot log GRANT requests when you specify the FOR CONSTRAINT option.

For information on logging operations that are enforced by security constraints, see the operation entry later in this table.

ALL

A log entry is to be made when any of the operations listed in the following row of this table is attempted against the specified object.

Both the name and ID for each column are logged when you log privileges to INSERT, REFERENCES, SELECT, and UPDATE.

If logging has begun for ALL actions on an object, a request to begin or end logging for an action changes logging activity only for the specified action.

The ALL option does not include logging of the following actions, which do not apply to a specific object:

• CREATE PROFILE
• CREATE ROLE
• DROP PROFILE

The ALL option does not apply to the NONTEMPORAL privilege because Vantage defines a BEGIN LOGGING rule on that privilege by default.

operation

The types of SQL requests to be logged.

If you do not specify the ALL option, you must specify one or more operations from the following list to define which privilege checks are logged.

• ALTER EXTERNAL PROCEDURE
• ALTER FUNCTION
• ALTER PROCEDURE
• CHECKPOINT
• CREATE AUTHORIZATION
• CREATE DATABASE
• CREATE EXTERNAL PROCEDURE
• CREATE FUNCTION
• CREATE GLOP SET
• CREATE MACRO
• CREATE PROCEDURE
• CREATE PROFILE
• CREATE ROLE
• CREATE TABLE
• CREATE TRIGGER
• CREATE USER
• CREATE VIEW
• DATABASE
• DELETE
• DROP
• DROP AUTHORIZATION
• DROP DATABASE
• DROP FUNCTION
• DROP GLOP SET
• DROP MACRO
• DROP PROCEDURE
• DROP PROFILE
• DROP ROLE
• DROP TABLE
• DROP TRIGGER
• DROP USER
• DROP VIEW
• DUMP
• EXECUTE
• EXECUTE FUNCTION
• EXECUTE PROCEDURE
• GRANT (including GRANTs of row-level security administration privileges)
• INDEX
• INSERT
• MACRO
• PROCEDURE
• REFERENCES
• RESTORE
• ROLLBACK DATABASE
• ROLLFORWARD DATABASE
• SELECT
• TABLE
• TRIGGER
• UPDATE
• USER
• VIEW

The following list of operations applies to row-level security logging.

If the log rule is for row-level security enforcement (a FOR CONSTRAINT constraint_name specification), but you do not specify an operation type, Vantage includes all of the row-level security operation types in the logging specification.

• DELETE
• INSERT
• OVERRIDE DELETE
• OVERRIDE DUMP
• OVERRIDE INSERT
• OVERRIDE RESTORE
• OVERRIDE SELECT
• OVERRIDE UPDATE
• SELECT
• UPDATE

If you do not want to log all of these operation types (excluding OVERRIDE DUMP and OVERRIDE RESTORE, which are not logged by default), you must specify the each operation type you want to log in the BEGIN LOGGING request.

The system enters one row in the log for each affected operation. Individual refusals of row access are not logged.

If you specify the logging of row-level security operations, then the only valid object types you can specify are row-level security tables, databases or users.

• If you specify a database or user, then Vantage logs all of the tables contained within the specified database or user that contain the constraint specified by constraint_name.
• If you specify a table, then that table must contain a row-level security column that matches constraint_name.
• If you do not specify an object, the system logs privilege checks on the specified operations for all tables in which the constraint_name appears.

When you specify DENIALS for one of the OVERRIDE privileges, Vantage generates a log entry only if a user attempts to select from a row-level security-protected table, when both of the following conditions are true:

• The user does not have a session constraint value for the audited constraint.
• The users does not have the OVERRIDE SELECT CONSTRAINT privilege.

The effects of OVERRIDE DELETE, INSERT, and UPDATE are similar.

If you submit the following request, Vantage generates a log entry if the user does not have the OVERRIDE DUMP CONSTRAINT privilege and tries to archive a row-level security table that has the audited constraint.

BEGIN LOGGING DENIALS
ON EACH OVERRIDE DUMP CONSTRAINT
FOR CONSTRAINT levels;

The same applies to RESTORE and not having the OVERRIDE RESTORE CONSTRAINT privilege.

GRANT

All requests which GRANT privileges on the objects specified in the ON clause are logged.

Because many users get GRANT privileges by default on objects they own or create, monitor privileges granted to users on sensitive objects and data.

BY user_name

The optional list of users to which the logging rules in this BEGIN LOGGING statement apply.

For logging of users authorized privileges by Vantage, the user name must be an existing user.

For logging of users authorized privileges in an LDAP directory (that is, AuthorizationSupported=yes in the authentication mechanism):

• If the user is mapped to a permanent database user object in the directory, specify the mapped database user name.
• If the user is not mapped to a permanent database user object in the directory, logging is not supported.

Absence of the BY user_name option specifies all users already defined to the system and any defined while this logging directive is in effect.

If neither BY user_name nor ON keyword object_name are specified, then the specified action is logged for all users and objects throughout the system.

ON item_list

One or more database objects for which access is to be logged.

If you do not specify the ON keyword object_name option in a BEGIN LOGGING statement, the system applies the other logging rules in the statement to all objects.

If you use the ON keyword option in a BEGIN LOGGING statement, you can specify logging on up to 20 objects.

The ON keyword object_name option does not apply to the CREATE PROFILE, DROP PROFILE, CREATE ROLE, or DROP ROLE operation because profiles and roles are system-level objects with no containing user or database.

Each object in the ON clause must be preceded by the keyword that indicates the object type: DATABASE, FUNCTION, FUNCTION MAPPING, MACRO, PROCEDURE, TABLE, TYPE, USER, or VIEW, followed by the object name.

ON DATABASE qualified_object_name

You must specify a qualified object name that includes the name of the containing database or user. For example, database_name.object_name or user_name.object_name.

ON FUNCTION qualified_object_name

You must specify a qualified object name that includes the name of the containing database or user. For example, database_name.object_name or user_name.object_name.

ON FUNCTION MAPPING qualified_object_name

You must specify a qualified object name that includes the name of the containing database or user. For example, database_name.object_name or user_name.object_name.

ON MACRO qualified_object_name

You must specify a qualified object name that includes the name of the containing database or user. For example, database_name.object_name or user_name.object_name.

ON PROCEDURE qualified_object_name

You must specify a qualified object name that includes the name of the containing database or user. For example, database_name.object_name or user_name.object_name.

ON TABLE qualified_object_name

You must specify a qualified object name that includes the name of the containing database or user. For example, database_name.object_name or user_name.object_name.

ON USER qualified_object_name

You must specify a qualified object name that includes the name of the containing database or user. For example, database_name.object_name or user_name.object_name.

ON VIEW qualified_object_name

You must specify a qualified object name that includes the name of the containing database or user. For example, database_name.object_name or user_name.object_name.