Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Sep 3, 2009

Oracle 11g Security - Securing the Data Dictionary

By James Koopmann

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(*)
----------
        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, 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.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date