Initial privileges on the diagnostics table

When the START VIOLATIONS TABLE statement creates the diagnostics table, the set of access privileges granted on the target table are a basis for granting privileges on the diagnostics table. The database server follows different rules, however, when it grants each type of privilege.

The following table explains the circumstances under which the database server grants each privilege on the diagnostics table.

Privilege: Condition for Granting the Privilege
Insert: User has the Insert privilege on the diagnostics table if the user has the Insert, Delete, or Update privilege on any column of the target table.
Delete: User has the Delete privilege on the diagnostics table if the user has the Insert, Delete, or Update privilege on any column of the target table.
Select: User has the Select privilege on the diagnostics table if the user has the Select privilege on any column in the target table.
Update: User has the Update privilege on the diagnostics table if the user has the Update privilege on any column in the target table.
Index: User has the Index privilege on the diagnostics table if the user has the Index privilege on the target table.
Alter: Alter privilege is not granted on the diagnostics table.
(Users cannot alter the schema of diagnostics tables.)
References: References privilege is not granted on the diagnostics table.
(Users cannot define referential constraints on diagnostics tables.)

The following rules concern access privileges on the diagnostics table:

When the diagnostics table is created, the owner of the target table becomes the owner of the diagnostics table.
The owner of the diagnostics table automatically receives all table-level privileges on the diagnostics table, including the Alter and References privileges. The database server, however, prevents the owner of the diagnostics table from altering the diagnostics table or adding a referential constraint to the diagnostics table.
You can use the GRANT and REVOKE statements to modify the initial set of privileges on the diagnostics table.
For INSERT, DELETE, or UPDATE operations on a target table that has a filtering-mode unique index or constraint defined on it, you must have the Insert privilege on the violations and diagnostics tables.
If you do not have the Insert privilege on the violations and diagnostics tables, the database server executes the INSERT, DELETE, or UPDATE statement on the target table, provided you have the necessary privileges on the target table. The database server does not return an error concerning the lack of Insert privilege on the violations and diagnostics tables unless an integrity violation is detected during execution of the INSERT, DELETE, or UPDATE statement.

Similarly, when you issue a SET Database Object Mode statement to set a disabled constraint or disabled unique index to the enabled or filtering mode, and a violations table and diagnostics table exist for the target table, you must have the Insert privilege on the violations and diagnostics tables.

If you do not have the Insert privilege on the violations and diagnostics tables, the database server executes the SET Database Object Mode statement, provided you have the necessary privileges on the target table. The database server does not return an error concerning the lack of Insert privilege on the violations and diagnostics tables unless an integrity violation is detected during the execution of the SET Database Object Mode statement.
The grantor of the initial set of privileges on the diagnostics table is the same as the grantor of the privileges on the target table. For example, if the user jenny was granted the Insert privilege on the target table by both the user wayne and the user laurie, both user wayne and user laurie grant the Insert privilege on the diagnostics table to user jenny.
Once a diagnostics table is started for a target table, revoking a privilege on the target table from a user does not automatically revoke the same privilege on the diagnostics table from that user. Instead you must explicitly revoke the privilege on the diagnostics table from the user.
If you have fragment-level privileges on the target table, you have the corresponding table-level privileges on the diagnostics table.

The next example illustrates how the initial set of privileges on a diagnostics table is derived from the current privileges on the target table. Assume that you have a table called cust_subset that holds customer data. This table consists of the following columns: ssn (social security number), fname (first name), lname (last name), and city (city in which the customer lives). The following set of access privileges exists on the cust_subset table:

User alvin is the owner of the table.
User barbara has the Insert and Index privileges on the table. She also has the Select privilege on the ssn and lname columns.
User danny has the Alter privilege on the table.
User carrie has the Update privilege on the city column. She also has the Select privilege on the ssn column.

Now user alvin starts a violations table named cust_subset_viols and a diagnostics table named cust_subset_diags for the cust_subset table:

START VIOLATIONS TABLE FOR cust_subset
   USING cust_subset_viols, cust_subset_diags;

The database server grants the following set of initial privileges on the cust_subset_diags diagnostics table:

User alvin is the owner of the diagnostics table, so he has all table-level privileges on the table.
User barbara has the Insert, Delete, Select, and Index privileges on the diagnostics table.
User carrie has the Insert, Delete, Select, and Update privileges on the diagnostics table.
User danny has no privileges on the diagnostics table.