A missing row is useful evidence only after you know the scope of the query. Two users can look for the same existing table and receive different metadata results because ownership, privileges, and the current container differ.
Oracle DBA Lesson 30 Part 1 — Choose a Dictionary View for the Right Scope
What the dictionary describes
Oracle's data dictionary stores descriptions of database objects, including names, owners, columns, and privileges. Oracle maintains these descriptions as objects change. Use the documented views to inspect this information. Do not modify Oracle's dictionary base tables. See dictionary content and the USER_ and ALL_ families.
The view families answer these questions:
| View | Useful question | Scope condition |
|---|---|---|
USER_TABLES | Which relational tables do I own? | Current user and current container. OWNER is implied. |
ALL_TABLES | Which relational tables can I access? | Owned tables plus accessible tables. Grants and enabled roles matter. |
DBA_TABLES | Which relational tables are in this container's catalog? | Required catalog privilege. A PDB query is local to that PDB. |
CDB_TABLES | Which tables are represented across visible containers? | Common user in root, view privilege, CONTAINER_DATA, and eligible container states. CON_ID identifies the container. |
Other USER_ views describe information pertinent to that user. Not every dictionary family has every prefix. Choose the documented view for the exact question.
One table, two users and two questions
APP_OWNER owns ORDERS in the intended application PDB. APP_READER owns no table named ORDERS and already has a narrow SELECT grant on APP_OWNER.ORDERS. Both connections are in that same PDB.
As the owner:
SELECT table_name
FROM user_tables
WHERE table_name = 'ORDERS'
ORDER BY table_name;
The ownership view should include ORDERS. Run the same query as the reader. That owner's table is outside the reader's ownership scope. See USER_TABLES.
Now inspect accessible metadata as the reader:
SELECT owner, table_name
FROM all_tables
WHERE owner = 'APP_OWNER'
AND table_name = 'ORDERS'
ORDER BY table_name;
With the stated access, this query should include the description APP_OWNER / ORDERS. Read OWNER and TABLE_NAME together. The view changes the question from ownership to access. The table remains owned by APP_OWNER. ALL_ results depend on the current set of enabled roles as well as direct and public grants and other applicable privileges. Merely changing an owner filter does not grant access. See ALL_TABLES.
Broader catalogs still have a container boundary
With an explicitly supplied SELECT privilege on SYS.DBA_TABLES, an administrator can inspect tables across owners in the current PDB:
SELECT owner, table_name
FROM dba_tables
WHERE owner = 'APP_OWNER'
AND table_name = 'ORDERS';
That PDB's catalog is local. Catalog access permits the metadata query. It does not automatically grant SELECT on every application's table data. A privileges error means the view query was not permitted, and must not be interpreted as an empty inventory. See DBA_TABLES.
For a cross-container report, a suitably privileged common account can use the corresponding CDB_ view from CDB$ROOT:
SELECT con_id, owner, table_name
FROM cdb_tables
WHERE owner = 'APP_OWNER'
AND table_name = 'ORDERS'
ORDER BY con_id, owner, table_name;
CON_ID identifies the container represented by each row. CON_ID 3 and 4 may each contain APP_OWNER.ORDERS. The name alone is insufficient to distinguish them. Resolve IDs using the actual deployment's permitted container inventory. Do not assume these numbers are your PDBs.
View privilege and container visibility are separate conditions. The common user's applicable CONTAINER_DATA attribute controls which containers a container-data view can expose. Oracle 19c documents default root-only visibility for a root-connected user. Rows are returned from open PDBs within the permitted scope, excluding PDBs open in restricted mode. A CDB_ query issued inside a PDB shows objects visible through the corresponding local DBA_ view. Root login or a familiar role alone is insufficient to conclude a complete estate inventory. A missing container's rows require a coverage and state investigation. See CDB_ views.
When a row is missing
Confirm the connected user and current container, the exact owner and object name, spelling and filters, existing privileges, and enabled roles. For a root report, also confirm view access, CONTAINER_DATA coverage, and eligible open states. Preserve the scope with your report so its reader knows what was observed. Check quoted case-sensitive identifiers where applicable. See viewing information about CDBs and PDBs.
Discover views and columns
DICTIONARY gives names and descriptions of dictionary tables and views:
SELECT table_name, comments
FROM dictionary
WHERE table_name IN
('USER_TABLES', 'ALL_TABLES', 'DBA_TABLES', 'CDB_TABLES')
ORDER BY table_name;
DICT_COLUMNS describes their columns:
SELECT table_name, column_name, comments
FROM dict_columns
WHERE table_name = 'ALL_TABLES'
AND column_name IN ('OWNER', 'TABLE_NAME')
ORDER BY column_name;
These descriptions help select the appropriate scope and fields. Consult the actual release documentation for behavior and privileges. See DICTIONARY and DICT_COLUMNS.
No comments:
Post a Comment