SELECT ANY DICTIONARY in Oracle 12c

As part of new Oracle 12c Database security implementation, they have stripped the system privilege ” SELECT ANY DICTIONARY ” from accessing the following SYS tables: USER$, ENC$,DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS.

The reason they removed access from these tables, is just in case you grant “select any dictionary” to a non-dba account, this account wont be able to see the “hashed passwords”. Only the SYS account will be able to query these tables.

in 12c if you simulate that:

sqlplus

SQL> grant select any dictionary to account1;

exit

sqlplus account1/password@DB1

SQL> select * from SYS.USER$;

ORA-01031: insufficient privileges

Bingo……..you can’t query USER$ table although you are granted the “select any dictionary” privilege.

Oracle Database 12c Multitenant Architecture – CDB & PDB

The New Oracle Database release named “12c” is based on Multitenant Architecture. Moreover, you have the option either to upgrade with this architecture with CDB (Container Database) and PDB (Pluggable Databases) OR you can you use the traditional Oracle Database Architecture.

In this article I will talk about the overview of the architecture.

The 12c CDB database will consists of the following major elements:

CDB$ROOT, which has the data dictionary views for the root and all pluggable databases.

PDB$SEED is a template database that a DBA can use to create other databases.

PDB is a pluggable database, which you can have zero,one, or more within a CDB. The maximum number of PDB in one CDB is “252 pluggable databases”. Each pluggable database has “CON_ID” which is a unique container identification.

High level architecture overview:

 Image

CDB level Architecture Image:

 Image

The Following Points will describe different 12c CDB architecture information:

  • Container Database(CDB$ROOT) will host all metadata for all PDB within CDB.

 

  • Each PDB will have the following tablespaces: SYSTEM, SYSAUX.  TEMP tablespace can be “optionally” created under a PDB, otherwise PDB will use the TEMP tablespace for the root container.

 

  • In 12c Character Set is set at CDB level, and will be applied to all associated PDB.

 

  • Using SYS to access root CDB, the DBA can stop/start CDB instance. This is not applicable if connecting to PDB.

 

  • All pluggable databases use a common UNDO tablespace under root CDB.

 

  • There is a centralized (common) CDB alert log, so all error/informative messages for each PDB are being written to one common alert log and set of trace files.

 

Final Remark: what I like about Multitenant Architecture from “Security” perspective is that applications will be deployed on database-level instead of a schema-level which gives you more “data isolation”, freedom of using public synonyms, provisioning, and portability.

In 11g if you use an excessive permission such as “SELECT ANY TABLE” the user can access the data from any schema within the database, which means other applications data. So when you deploy your application on database-level this is not applicable since all objects within the database is primarily owned by your application.

 

 

 

 

 

References: http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdbovrvw.htm#CNCPT89340