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? Let’s go through a quick scenario. First,
let’s 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(*)
———-
14SQL> 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/user01SQL> select count(*) from scott.emp;
COUNT(*)
———-
14SQL> 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 existSQL> 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, I’ll
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.
