The power of Oracle User Dictionary Views
“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,
(select tablespace_name, sum(bytes) bytes
group by tablespace_name) us,
(select tablespace_name,sum(bytes) bytes
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”