,admin_option
from dba_sys_privs
where grantee = UPPER('&grantee')
order by privilege;
To view system privileges granted to the currently connected user, run this query: select
username
,privilege
,admin_option
from user_sys_privs;
The USERNAME column shows whether the privilege has been granted to the currently connected user or if the privilege has been granted to PUBLIC.
The ROLE_SYS_PRIVS view displays what system privileges have been assigned to a role. When querying this view, you see only roles that have been granted to the currently connected schema. Here’s an example query that lists privileges granted to a specified role:
select
role
,privilege
from role_sys_privs
where role = upper('&role');
The prior SQL displays only database system privileges that have been directly granted to a user. To view any system privileges that have been granted through a role to a user, you have to also query a view such as ROLE_SYS_PRIVS. The following query displays system privileges granted either directly to the currently connected user or through any roles granted to the user:
select
privilege
,'DIRECT GRANT'
from user_sys_privs
union
select
privilege
,'ROLE GRANT'
from role_sys_privs;
Two roles—CONNECT and RESOURCE—are commonly assigned to newly created accounts. However, Oracle recommends that you not assign these roles to users because they may not be available in future releases. Instead, Oracle advises that you create your own roles and assign privileges as required. Run the following query to view privileges assigned to these roles:
select
grantee
233
CHAPTER 10 ■ DATA DICTIONARY BASICS
,privilege
from dba_sys_privs
where grantee IN ('CONNECT','RESOURCE')
order by grantee;
Here’s the output:
ROLE PRIVILEGE
------------------------- -------------------------
CONNECT CREATE SESSION
RESOURCE CREATE CLUSTER
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE
You can use a vast array of data-dictionary views to determine what users and roles have been assigned which system and object privileges. This section has touched on only a few examples. See Table 10–4 for a description of the various privilege-related data-dictionary views and their purposes.
Table 10–4.
Privilege-Related Data-Dictionary Views
View Description
DBA_ROLES
All roles in the database
DBA_ROLE_PRIVS
Roles granted to users and roles
DBA_SYS_PRIVS
All system privileges granted to users and roles
DBA_TAB_PRIVS
All object privileges granted to users and roles
DBA_COL_PRIVS
All column object grants
ROLE_ROLE_PRIVS
Roles granted to other roles; only for roles to which the user has access ROLE_SYS_PRIVS
Privileges granted to other roles; only for roles to which the user has access ROLE_TAB_PRIVS
Table privileges granted to roles; only for roles to which the user has access ALL_TAB_PRIVS
Object grants for which the user is the object owner, grantor, or grantee; also object grants for which PUBLIC is the grantee
ALL_TAB_PRIVS_MADE
Object grants where the user is the object owner or grantor
ALL_TAB_PRIVS_RECD
Object grants where the user is the grantee or where PUBLIC is the grantee ALL_COL_PRIVS
Column object grants where the user is the object owner, grantor, or
grantee; also column grants where PUBLIC is the grantee
ALL_COL_PRIVS_MADE
Column object grants where the user is the object owner or grantor
ALL_COL_PRIVS_RECD
Column object grants where the user is the grantee or PUBLIC is the grantee 234
CHAPTER 10 ■ DATA DICTIONARY BASICS
View Description
USER_ROLE_PRIVS
Roles granted to the user
USER_SYS_PRIVS
System privileges granted to the user
USER_TAB_PRIVS
Object grants for which the user is the object owner, grantor, or grantee USER_TAB_PRIVS_MADE
Object grants where the user is the object owner
USER_TAB_PRIVS_RECD
Object grants where the user is the grantee
USER_COL_PRIVS
Column object grants where the user is the object owner, grantor, or grantee USER_COL_PRIVS_MADE
Column object grants where user is the object owner
USER_COL_PRIVS_RECD
Column object grants where the user is the grantee
Displaying Object Privileges
Object
privileges
are grants that allow you to perform DML operations (INSERT, UPDATE, and DELETE) on another user’s tables. Before you can perform DML operations on another user’s objects, you must be granted the appropriate privileges. Object privileges are managed through the GRANT and REVOKE
statements.
Sometimes, when you’re troubleshooting table-access issues, you need to view what DML privileges have been granted. The following query selects from the USER_TAB_PRIVS_RECD view to display the table privileges that have been granted to the currently connected user:
select
owner
,table_name
,grantor
,privilege
from user_tab_privs_recd;
To view privileges that the current user has granted to other users, select from the USER_TAB_PRIVS_MADE view:
select
grantee
,table_name
,grantor
,privilege
from user_tab_privs_made;
Run the following query to view table privileges that have been granted to your current user: select grantee, table_name, privilege
from user_tab_privs
where grantee = sys_context('USERENV','CURRENT_USER')
order by table_name, privilege;
In the previous lines of code, the SYS_CONTEXT function is used to extract the current username from the session. Without qualifying the GRANTEE with your current username, the query also displays object 235
CHAPTER 10 ■ DATA DICTIONARY BASICS
privileges you’ve granted and privileges that have been granted by other users to your objects. The query can alternatively prompt you for your current username. For example:
select grantee, table_name, privilege
from user_tab_privs
where grantee = UPPER('&your_user_name')
order by table_name, privilege;
This next query selects from USER_TAB_PRIVS and ROLE_TAB_PRIVS to check for any object privileges that have been granted directly to the user or granted through a role that has been granted to the user: select
grantee
,owner
,table_name
,grantor
,privilege
from user_tab_privs
union
select
role
,owner
,table_name
,'ROLE'
,privilege
from role_tab_privs
order by 2, 3;
The ROLE_TAB_PRIVS view shows table privileges that have been granted to a role to which the current user has access.
Displaying Object Dependencies
Say you need to drop a table, but before you drop it you want to display any objects that are dependent on the table. For example, you may have a table that has synonyms, views, materialized views, functions, procedures, and triggers that rely on it. Before making the changes you want to review what other objects are dependent on the object you’re modifying. You can use the DBA_DEPENDENCIES view to display object dependencies. The following query prompts you for a username and an object name: select '+' || lpad(' ',level+2) || type || ' ' || owner || '.' || name dep_tree from dba_dependencies
connect by prior owner = referenced_owner and prior name = referenced_name and prior type = referenced_type
start with referenced_owner = upper('&object_owner')
and referenced_name = upper('&object_name')
and owner is not null;
In the output, each object listed has a dependency on the object you entered. Lines are indented to show the dependency of an object on the object in the preceding line: 236
CHAPTER 10 ■ DATA DICTIONARY BASICS
DEP_TREE
------------------------------------------------------------
+ TRIGGER STAR2.D_COMPANIES_BU_TR1
+ MATERIALIZED VIEW CIA.CB_RAD_COUNTS
+ SYNONYM STAR1.D_COMPANIES
+ SYNONYM CIA.D_COMPANIES
+ MATERIALIZED VIEW CIA.CB_RAD_COUNTS
In this example, the object being analyzed is a table named D_COMPANIES. Several synonyms, materialized views, and one trigger are dependent on this table. For example, the materialized view CB_RAD_COUNTS owned by CIA is dependent on the synonym D_COMPANIES owned by CIA, which in turn is dependent on the D_COMPANIES synonym owned by STAR1.
The DBA_DEPENDENCIES view contains a hierarchical relationship between the OWNER, NAME, and TYPE columns and their referenced column names of REFERENCED_OWNER, REFERENCED_NAME, and REFERENCED_TYPE. Oracle provides a number of constructs to perform hierarchical queries. For example, START WITH and CONNECT BY allow you to identify a starting point in a tree and walk either up or down the hierarchical relationship.
The previous SQL query in this section operates on only one object. If you want to inspect every object in a schema, you can use SQL to generate SQL to create scripts that display all dependencies for a schema’s objects. The next section of code does that. For formatting and output, it uses some constructs specific to SQL*Plus, such as setting the page sizes and line size and spooling the output: UNDEFINE owner
SET LINESIZE 132 PAGESIZE 0 VERIFY OFF FEEDBACK OFF TIMING OFF
SPO dep_dyn_&&owner..sql
SELECT 'SPO dep_dyn_&&owner..txt' FROM DUAL;
--
SELECT
'PROMPT ' || '_____________________________'|| CHR(10) ||
'PROMPT ' || object_type || ': ' || object_name || CHR(10) ||
'SELECT ' || '''' || '+' || '''' || ' ' || '|| LPAD(' || '''' || ' '
|| '''' || ',level+3)' || CHR(10) || ' || type || ' || '''' || ' ' || '''' ||
' || owner || ' || '''' || '.' || '''' || ' || name' || CHR(10) ||
' FROM dba_dependencies ' || CHR(10) ||
' CONNECT BY PRIOR owner = referenced_owner AND prior name = referenced_name '
|| CHR(10) ||
' AND prior type = referenced_type ' || CHR(10) ||
' START WITH referenced_owner = ' || '''' || UPPER('&&owner') || '''' || CHR(10) ||
' AND referenced_name = ' || '''' || object_name || '''' || CHR(10) ||
' AND owner IS NOT NULL;'
FROM dba_objects
WHERE owner = UPPER('&&owner')
AND object_type NOT IN ('INDEX','INDEX PARTITION','TABLE PARTITION');
--
SELECT 'SPO OFF' FROM dual;
SPO OFF
SET VERIFY ON LINESIZE 80 FEEDBACK ON
You should now have a script named dep_dyn_
237
CHAPTER 10 ■ DATA DICTIONARY BASICS
define user1=ccim_dev
define user1_pwd=ccim_pwd
define user2=ccim_prod
define user2_pwd=abc123
define conn1=@db1
define conn2=@db2
create database link db1 connect to &&user1 identified by &&user1_pwd using 'sb-db5:1521/sb6';
create database link db2 connect to &&user2 identified by &&user2_pwd using 'db-prod1:1521/scaprd';
The CREATE DATABASE LINK statements use the easy-connect naming method to determine the location of the remote database. The USING clause specifies the database-connection information using this syntax:
'
After the database links are created, you run SQL statements that display metadata differences from the data-dictionary views. The next two statements use the MINUS set operator to determine whether there any differences between table names:
prompt ...Tables in db1 NOT IN db2
select table_name
from user_tables&&conn1
minus
select table_name
from user_tables&&conn2;
prompt ...Tables in db2 NOT IN db1
select table_name
from user_tables&&conn2
minus
select table_name
from user_tables&&conn1;
If you want to compare a local schema with a remote schema, then you need only one database link.
In this situation, you must also define one of the connection variables to be blank: define conn2=''
Now you can connect as a local user in your database and compare a remote schema to a local schema.