Whether known to you or not, Oracle maintains a set of internal structures, the V$, X$ and views, that keep track of various performance statistics, database structures and application execution information. Without these internal structures, there would be no database to store our precious information. In addition, each of these internal structures holds key information to a hacker about the makeup of our database.
So what are the parts that compose an Oracle data dictionary? Stripped straight from the 2 Day + Security Guide Oracle is quite clear to point out that a data dictionary is composed of the following contents:
- The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
- The amount of space allocated for, and is currently used by, the schema objects
- Default values for columns
- Integrity constraint information
- The names of Oracle Database users
- Privileges and roles granted to each user
- Auditing information, such as who has accessed or updated various schema objects
- Other general database information
Many would call this the meta data of a database or the data about data that contains the structural information about the objects within our databases. It is in effect the schema designed by database modelers but also contains the privileges, auditing, and information about the database. To get a glimpse of the actual data dictionary Oracle mentions that you can query the DICTIONARY view. Below is a small snippet of the over 2,000 rows of information contained in the DICTIONARY view.
SQL> SELECT * FROM dictionary; TABLE_NAME COMMENTS ----------------------- --------------------------------------------------- DBA_CONS_COLUMNS Information about accessible columns in constraint definitions DBA_LOG_GROUP_COLUMNS Information about columns in log group definitions DBA_INDEXES Description for all indexes in the database DBA_SYNONYMS All synonyms in the database DBA_TABLES Description of all relational tables in the database DBA_OBJECT_TABLES Description of all object tables in the database DBA_ALL_TABLES Description of all object and relational tables in the database USER_CONS_COLUMNS Information about accessible columns in constraint definitions USER_LOG_GROUP_COLUMNS Information about columns in log group definitions USER_INDEXES Description of the user's own indexes USER_SYNONYMS The user's private synonyms USER_TABLES Description of the user's own relational tables USER_OBJECT_TABLES Description of the user's own object tables USER_ALL_TABLES Description of all object and relational tables owned by the user's ALL_CONS_COLUMNS Information about accessible columns in constraint definitions ALL_LOG_GROUP_COLUMNS Information about columns in log group definitions ALL_INDEXES Descriptions of indexes on tables accessible to the user ALL_SYNONYMS All synonyms for base objects accessible to the user and session ALL_TABLES Description of relational tables accessible to the user ALL_OBJECT_TABLES Description of all object tables accessible to the user ALL_ALL_TABLES Description of all object and relational tables accessible to the user AUDIT_ACTIONS Description table for audit trail action type codes. COLUMN_PRIVILEGES Grants on columns for which the user is the grantor, grantee, owner, or DBMS_ALERT_INFO DICTIONARY Description of data dictionary tables and views DICT_COLUMNS Description of columns in data dictionary tables and views DUAL GLOBAL_NAME global database name V$GCSHVMASTER_INFO Synonym for V_$GCSHVMASTER_INFO V$GCSPFMASTER_INFO Synonym for V_$GCSPFMASTER_INFO V$GC_ELEMENT Synonym for V_$GC_ELEMENT V$GES_BLOCKING_ENQUEUE Synonym for V_$GES_BLOCKING_ENQUEUE V$GES_ENQUEUE Synonym for V_$GES_ENQUEUE GV$DATABASE Synonym for GV_$DATABASE GV$DATAFILE Synonym for GV_$DATAFILE DICT Synonym for DICTIONARY USER_HISTOGRAMS Synonym for USER_TAB_HISTOGRAMS USER_SQLSET_DEFINITIONS Synonym for USER_SQLSET IND Synonym for USER_INDEXES
As you can see, a wide variety of information is contained in the dictionary. Protecting the data dictionary is quite easy. In fact, we saw glimpses of this in the last security article where we enabled the Oracle 11g default security settings. Specifically, the 07_DICTIONARY_ACCESSIBILITY parameter will help protect the data dictionary by preventing users who have the ANY system privilege from using that privilege on the data dictionary (objects within the SYS schema). So what does this really mean? Lets go through a quick scenario. First, lets check the 07_DICTIONARY_ACCESSIBILITY parameter.
SQL> show parameter O7_dictionary_accessibility NAME TYPE VALUE ------------------------------------ ----------- ------ O7_DICTIONARY_ACCESSIBILITY boolean TRUE
If you remember from the last article, when 07_DICTIONARY_ACCESSIBILITY is set to TRUE, this is the value when Oracle 11g default security settings are NOT set. Therefore, the data dictionary is accessible. If I create a user, call that user user01, and grant user01 SELECT ANY TABLE, user01 will be able to SELECT rows from any table in the database. Here is the quick stream of events:
SQL> create user user01 identified by user01; User created. SQL> grant connect to user01; Grant succeeded. SQL> grant resource to user01; Grant succeeded. SQL> grant select any table to user01; Grant succeeded. SQL> connect user01/user01 Connected. SQL> select count(*) from scott.emp; COUNT(*) ---------- 14 SQL> select count(*) from dba_tables; COUNT(*) ---------- 2524
Very simply, user01 can SELECT from ANY table including the data dictionary object DBA_TABLES. So now, when we reset the 07_DICTIONARY_ACCESSIBILITY to FALSE (shutting down access to the data dictionary) we will limit the ANY privilege to objects outside the SYS schema (data dictionary). Here is the stream of events:
SQL> connect sys/xxxxxxxx as sysdba SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=FALSE scope=spfile; System altered. SQL> exit [oracle@ludwig ~]$ dbshut [oracle@ludwig ~]$ dbstart [oracle@ludwig ~]$ sqlplus user01/user01 SQL> select count(*) from scott.emp; COUNT(*) ---------- 14 SQL> select count(*) from dba_tables; select count(*) from dba_tables * ERROR at line 1: ORA-00942: table or view does not exist
Now user01, even though user01 has the ANY privilege, cannot SELECT from the data dictionary.
And just if you were wondering, this works for the V$ and X$ objects as well:
SQL> connect user01/user01 Connected. SQL> select * from v$instance; select * from v$instance * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from x$obj; select * from x$obj * ERROR at line 1: ORA-00942: table or view does not exist
For those select users whom you might want to grant privilege to view the data dictionary, there is the SELECT ANY DICTIONARY privilege that will allow them to bypass the setting of the 07_DICTIONARY_ACCESSIBILITY setting and access the data dictionary.
SQL> connect sys/xxxxxxxx as sysdba SQL> grant select any dictionary to user01; Grant succeeded. SQL> connect user01/user01 Connected. SQL> select count(*) from dba_tables; COUNT(*) ---------- 2524
General and global privileges are great but should be used with great caution as well. The ANY privilege, especially when tied to UPDATE ANY or DROP ANY can cause tremendous havoc and pain within a database. It is my suggestion and tip to not grant every user the ANY privilege. Instead, stick with explicit GRANTs that can be mass distributed through ROLEs. If you must use the ANY privilege, Ill sleep better knowing that at least Oracle has given us an init.ora parameter in the form of 07_DICTIONARY_ACCESSIBILITY to limit the effects on the data dictionary.