15.3 Inspecting Constraints, Privileges, and Comments

15.3 Inspecting Constraints, Privileges, and Comments

Maintaining database integrity and security requires thorough inspection of database constraints, assigned privileges, granted roles, and data dictionary documentation. On the Oracle Database SQL (1Z0-071) exam, questions frequently test your ability to decode constraint type codes, trace foreign key relationships, audit granted privileges, and manage object comments.

---

1. Inspecting Constraints: USER_CONSTRAINTS & USER_CONS_COLUMNS

Integrity constraints enforce business rules across database tables. Oracle separates constraint rules from their column mappings across two primary views:

                     CONSTRAINT DICTIONARY ARCHITECTURE
                                      │
          ┌───────────────────────────┴───────────────────────────┐
          │                                                       │
     USER_CONSTRAINTS                                      USER_CONS_COLUMNS
     (Constraint Rule Definition)                          (Column Association)
     - CONSTRAINT_NAME                                     - CONSTRAINT_NAME
     - CONSTRAINT_TYPE (P, R, U, C, V, O)                  - TABLE_NAME
     - TABLE_NAME                                          - COLUMN_NAME
     - SEARCH_CONDITION                                    - POSITION (1, 2, 3...)
     - R_CONSTRAINT_NAME (Parent PK/UK)
     - DELETE_RULE (CASCADE, SET NULL, NO ACTION)
     - STATUS (ENABLED, DISABLED)

Decoding CONSTRAINT_TYPE Codes

The CONSTRAINT_TYPE column in USER_CONSTRAINTS uses single-character codes to distinguish constraint categories:

Exam Trap (Crucial!): Oracle does NOT have a distinct 'N' constraint type for NOT NULL constraints. Every NOT NULL constraint is recorded in USER_CONSTRAINTS as type 'C' (Check), with the column validation rule stored in SEARCH_CONDITION as "COLUMN_NAME" IS NOT NULL.

-- Query all constraints on the EMPLOYEES table:
SELECT constraint_name, constraint_type, status, search_condition, r_constraint_name
FROM user_constraints
WHERE table_name = 'EMPLOYEES'
ORDER BY constraint_type, constraint_name;

Tracing Foreign Keys with R_CONSTRAINT_NAME & DELETE_RULE

When CONSTRAINT_TYPE = 'R', the following columns provide referential relationship details:

• R_CONSTRAINT_NAME: The identifier of the parent table's Primary Key or Unique constraint.
• R_OWNER: The schema that owns the referenced parent constraint.
• DELETE_RULE: The referential action when a parent row is deleted:
  • NO ACTION (Default: rejects deletion if child rows exist)
  • CASCADE (ON DELETE CASCADE: deletes referencing child rows)
  • SET NULL (ON DELETE SET NULL: sets child FK columns to NULL)

-- Join child and parent constraints to trace referential relationships:
SELECT c.table_name AS child_table,
       c.constraint_name AS fk_name,
       p.table_name AS parent_table,
       c.r_constraint_name AS parent_pk_name,
       c.delete_rule
FROM user_constraints c
JOIN user_constraints p 
  ON c.r_constraint_name = p.constraint_name
WHERE c.constraint_type = 'R'
  AND c.table_name = 'EMPLOYEES';

Mapping Columns with USER_CONS_COLUMNS

To see which specific columns belong to a constraint—and their order in composite keys—join USER_CONSTRAINTS with USER_CONS_COLUMNS:

SELECT uc.table_name, uc.constraint_name, uc.constraint_type,
       ucc.column_name, ucc.position
FROM user_constraints uc
JOIN user_cons_columns ucc 
  ON uc.constraint_name = ucc.constraint_name
WHERE uc.table_name = 'ORDER_ITEMS'
ORDER BY uc.constraint_name, ucc.position;

• POSITION: A 1-based integer indicating column ordering for composite primary/foreign/unique keys. For single-column constraints or CHECK constraints, POSITION is typically 1 or NULL.

---

2. Inspecting Privileges & Roles

Oracle's security model separates direct privileges from role-based privileges. Security metadata is split across specialized dictionary views:

                          SECURITY METADATA VIEWS
                                     │
        ┌────────────────────────────┼────────────────────────────┐
        │                            │                            │
   USER_SYS_PRIVS               USER_TAB_PRIVS               USER_ROLE_PRIVS
   - Direct system privileges   - Object privileges on       - Roles granted to
     granted to current user      tables/views granted         the current user
   - Columns: USERNAME,           to or by current user      - Columns: USERNAME,
     PRIVILEGE, ADMIN_OPTION    - Columns: GRANTEE, OWNER,     GRANTED_ROLE, ADMIN_OPTION
                                  TABLE_NAME, PRIVILEGE,       DEFAULT_ROLE
                                  GRANTABLE

Direct System & Object Privilege Views

-- 1. Check direct system privileges (e.g., CREATE TABLE, CREATE VIEW):
SELECT privilege, admin_option
FROM user_sys_privs;

-- 2. Check direct object privileges granted to the current user:
SELECT owner, table_name, privilege, grantor, grantable
FROM user_tab_privs
WHERE grantee = USER;

-- 3. Check object privileges granted BY the current user to others:
SELECT grantee, table_name, privilege, grantable
FROM user_tab_privs
WHERE owner = USER;

• ADMIN_OPTION: 'YES' if the user can grant the system privilege to other users, else 'NO'.
• GRANTABLE: 'YES' if the object privilege was granted WITH GRANT OPTION, else 'NO'.

Inspecting Role Privileges: ROLE_SYS_PRIVS & ROLE_TAB_PRIVS

Because production environments grant permissions primarily via Roles, checking only USER_SYS_PRIVS and USER_TAB_PRIVS will often miss privileges inherited through roles. Developers inspect role definitions using ROLE_SYS_PRIVS and ROLE_TAB_PRIVS:

-- Check roles granted to the current user:
SELECT granted_role, admin_option, default_role
FROM user_role_privs;

-- Inspect system privileges contained inside granted roles:
SELECT role, privilege, admin_option
FROM role_sys_privs
WHERE role IN (SELECT granted_role FROM user_role_privs);

-- Inspect table privileges contained inside granted roles:
SELECT role, owner, table_name, privilege
FROM role_tab_privs
WHERE role IN (SELECT granted_role FROM user_role_privs);

---

3. Adding and Querying Comments: USER_TAB_COMMENTS & USER_COL_COMMENTS

Oracle SQL supports adding descriptive documentation directly into the data dictionary for tables and columns using the COMMENT DDL statement.

+-------------------------------------------------------------------------+
|                         COMMENT DDL SYNTAX                              |
+-------------------------------------------------------------------------+
| Table Comment:                                                          |
|   COMMENT ON TABLE [schema.]table_name IS 'Descriptive text string';    |
|                                                                         |
| Column Comment:                                                         |
|   COMMENT ON COLUMN [schema.]table.column IS 'Descriptive text string'; |
|                                                                         |
| Remove Comment (Set to NULL):                                           |
|   COMMENT ON TABLE table_name IS '';                                    |
+-------------------------------------------------------------------------+

Adding Comments Example

-- Add documentation to a table:
COMMENT ON TABLE employees 
IS 'Stores core personnel, compensation, and department assignment records.';

-- Add documentation to specific columns:
COMMENT ON COLUMN employees.salary 
IS 'Monthly base compensation in USD before bonuses and commissions.';

COMMENT ON COLUMN employees.commission_pct 
IS 'Commission percentage (0.00 to 0.50) applicable to Sales representatives.';

-- Remove a comment by setting it to an empty string:
COMMENT ON TABLE departments IS '';

Querying Table & Column Comments

-- Query table comments in the current schema:
SELECT table_name, table_type, comments
FROM user_tab_comments
WHERE table_name IN ('EMPLOYEES', 'DEPARTMENTS');

-- Query column comments for a specific table:
SELECT column_name, comments
FROM user_col_comments
WHERE table_name = 'EMPLOYEES'
  AND comments IS NOT NULL
ORDER BY column_name;

Comment Views Overview

---

Comprehensive Constraint, Privilege, and Comment Inspection Guide

-- Full schema diagnostic query: List all table columns, comments, and NOT NULL flags
SELECT c.column_id,
       c.column_name,
       c.data_type || 
         CASE 
           WHEN c.data_type = 'NUMBER' AND c.data_precision IS NOT NULL 
             THEN '(' || c.data_precision || ',' || c.data_scale || ')'
           WHEN c.data_type LIKE '%CHAR%' 
             THEN '(' || c.data_length || ')'
         END AS formatted_datatype,
       c.nullable,
       cm.comments
FROM user_tab_columns c
LEFT JOIN user_col_comments cm 
  ON c.table_name = cm.table_name 
 AND c.column_name = cm.column_name
WHERE c.table_name = 'EMPLOYEES'
ORDER BY c.column_id;