The power of Oracle User Dictionary Views

September 4, 2009 by 4 Comments 

“What’s my Oracle user default tablespace? How much space do my database objects occupy? Did you move all my indexes during the user schema copy?…”

Being a full-time Oracle DBA I frequently answer these same questions of software developers.

Most of the database information that a non-powerful Oracle database user requires is accessible via Dictionary Views without any extra privileges granted. Furthermore, that information is powerful enough to be a starting point for a security attack.

Experienced Oracle DBA probably grants as few privileges to typical database users as possible. Thus, a DBA tries keeping the Oracle database dictionary away from non-powerful DB users. In particular neither SELECT_CATALOG_ROLE role nor SELECT ANY DICTIONARY system privileges are given away to Oracle end-users. Additionally, a DBA enables data dictionary protection (O7_DICTIONARY_ACCESSIBILITY is false) to prevent users having ANY system privileges from using them on the data dictionary.

Nevertheless some powerful dictionary views are still accessible to all Oracle users!

Even a typical user having only one CREATE SESSION privilege can use them to find out most of the required information about own user schema and database instance.

Having that scenario in mind, let’s examine Oracle data dictionary views in Oracle 10gR2 with the following prefixes:

USER_ – User’s view (what is in the user schema)
ALL_   – Expanded user’s view (what the user can access)

Views with the prefix USER usually exclude the column OWNER. This column is implied in the USER views to be the user issuing the query.

Below I give a handy set of Oracle dictionary views accessible to a typical Oracle database user.

My user schema level

select * from user_users;         -- my username, default and temporary tablespace
select * from user_tablespaces;          -- tablespaces accessible to the current user
select * from user_ts_quotas;             -- tablespace quotas for the current user + segments allocation size
select * from user_free_space;           -- free extents in the tablespaces accessible to the current user
select * from user_objects;                -- user objects
select * from user_segments;             -- user segments with segment size
select * from user_extents;                -- user extents
select * from user_source;                 -- user PL/SQL code

…a few more powerful ones

select * from table_privileges                  -- all rights tables accessible to the current user
select * from session_privs;                    -- privileges that are currently available to the user
select * from session_roles;                    -- roles

Database server level

If you want to explore the outside of your user schema you need to use ALL_ dictionary views.

Below are a few examples:
select * from all_users;                               -- all users of the database visible to the current user
select * from all_objects;                           -- all objects accessible to the current user
select * from all_catalog;                           -- all database dictionary views accessible to the current user
select * from all_tables;                              -- tables accessible to the current user

Powerful views with system information

select global_name from global_name;         -- global database name
select * from all_registry_banners;                -- valid oracle components loaded into the database
select * from v$version;                              -- version numbers of core library components + OS
select * from v$option;                               -- database options and features
select * from database_compatible_level;  -- DB compatibility level (compatible init parameter)
select * from database_properties;                -- DB properties and default settings
select * from v$nls_parameters;                    -- NLS parameters
select * from loader_file_ts;                          -- DB files
select * from v$session_longops;                   -- all long running DB operations
select * from v$session_connect_info;         -- all user network connections including SID and OS name.

To find more dictionary views with their descriptions query the following view

select * from dictionary;                               -- descriptions of data dictionary tables and views

From that long list of powerful dictionary views you can derive even a lot more than you would expect. See the next SQL examples.

select created db_creation_time from all_users where username = 'SYS';
-- User tablespace report
select fs.tablespace_name, round((us.bytes+fs.bytes)/1024/1024,3) size_Mb,
round(us.bytes/1024/1024,3) used_MB,
round(fs.bytes/1024/1024,3) free_MB,
round(fs.bytes*100/(us.bytes+fs.bytes),2) free_perc
from
(select tablespace_name, sum(bytes) bytes
from user_segments
group by tablespace_name) us,
(select tablespace_name,sum(bytes) bytes
from user_free_space
group by tablespace_name) fs
where us.tablespace_name (+)= fs.tablespace_name
/

I’ll continue reviewing the powerful PUBLIC packages in the next posts…

See a relevant article: “The power of Oracle CREATE SESSION privilege”

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

4 Responses to “The power of Oracle User Dictionary Views”
  1. Sankar says:

    Good information to be put in use. Thanx Kirill.

  2. Luigi says:

    Hello, just a question:
    are all V$ views accessible by all users of the database?

  3. Kirill Loifman says:

    No, you need to be sys user or access rights to be granted on data dictionary
    — Kirill

  4. Kirill Loifman says:

    No, only if a user has a privilege to read data dictionary

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email