Pro Oracle Database 11g Administration (43 page)

,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_.sql created in the same directory from which you ran the script. This script contains all the SQL required to display dependencies on objects in the owner you entered. Run the script to display object dependencies. In this example, the owner is CIA: SQL> @dep_dyn_cia.sql

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.