Daily Archives: September 21, 2015

12c, CDB, Multitenant, privileges, Users

Privileges of Common Users in Oracle Multitenant Database

Leave a comment

Privileges for Common Users in Oracle 12c multitenant database architecture have unique constraints allowing for privileges at the CDB level and the PDB levels.    Common users may or may not have privileges at all containers in the multitenant database or they may be restricted to only a few.   Additionally, privileges maybe restricted to objects in individual containers.   The following are general rules governing privileges of common users and we will examine each rule in the below procedures:

 

  1. A privilege granted commonly can be used in every container and future container.
  2. Only common users can grant common privileges.
  3. Granter of common privileges must connect to root and specify CONTAINER=ALL clause.
  4. Can include system and object privilege.
  5. Privileges should never be given to public.

 

 

  • A privilege granted commonly can be used in every container and future container.

 

  1. Using the common user C##DBA we will create the user at the CDB level and grant DBA privileges with the CONTAINER clause equal to all.

 

 SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> create user c##dba identified by password;

User created.

SQL> grant dba to c##dba container=all;

Grant succeeded.

SQL>  connect c##dba/password@cdb1;

Connected.

SQL> create user c##user identified by password container=all;

User created.

SQL>

 

  1. Now alter the session to container PDB1 as the same user and create a user there. NOTE:  Permissions are good both in CDB1 and PDB1 for Common users.   This is true because container clause was set to ALL.

 

SQL> show user

USER is “C##DBA”

SQL> alter session set container=PDB1;

Session altered.

SQL> show user

USER is “C##DBA”

SQL>  create user pdb1_user identified by password;

User created.

SQL>

 

 

  • Only common users can grant common privileges.

 

  1. As the common user c##dba grant resource to common user c##user.

 

SQL> connect c##dba/password@cdb1;

Connected.

SQL> grant resource to c##user;

Grant succeeded.

SQL>

 

  • Granter of common privileges must connect to root and specify CONTAINER=ALL clause for privileges to be granted in all containers.

 

  1. This one is a little tricky because it appears that you can do it, however the key is common privilege, you can have privileges in the CDB and a few PDB and it not be common. This procedure shows an example of this scenario.
  2. Connect to PDB1 as the common user c##dba and grant dba to the user c##user.

 

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to c##user;

Grant succeeded.

SQL>

 

  1. Now a user with common privilege of DBA should be able to connect to any PDB in the CDB, however when we try to connect to pdb2, it fails. Further more when we look at the view dba_role_privs, we note that user C##USER does have DBA role, but not commonly.

 

SQL> connect c##user/password@pdb2

ERROR:

ORA-01045: user C##USER lacks CREATE SESSION privilege; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL> connect c##dba/password@pdb1

Connected.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE                        COM

—————————— —

C##USER                        NO

SQL>

 

  1. Now logon to the root container as the C##DBA user and grant the DBA role to C##USER. Then view the dba_role_privs table and attempt to connect to PDB2.

 

SQL> connect c##dba/password@cdb1

Connected.

SQL> grant dba to c##user container=all;

 

Grant succeeded.

 

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE                        COM

—————————— —

C##USER                        NO

C##USER                        NO

C##USER                        YES

 

SQL> connect c##user/password@pdb2

Connected.

SQL>

 

  • Can include system and object privilege.

 

  1. Revoke all rights from user C##USER. If you are following from the above example, the user C##USER has been granted the DBA role twice locally and once commonly. So revoke Common grant at ROOT container and local root grant.   Then connect to PDB1 to remove local grant at PDB1.

 

SQL> connect c##dba/password@cdb1

Connected.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE                        COM

—————————— —

C##USER                        NO

C##USER                        NO

C##USER                        YES

 

SQL> revoke dba from c##user container=ALL;

 

Revoke succeeded.

 

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE                        COM

—————————— —

C##USER                        NO

C##USER                        NO

 

SQL>  revoke dba from c##user container=current;

 

Revoke succeeded.

 

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE                        COM

—————————— —

C##USER                        NO

 

SQL> connect c##dba/password@pdb1

Connected.

SQL> revoke dba from c##user;

 

Revoke succeeded.

 

SQL>

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

no rows selected

SQL>

 

  1. Now reconnect to C##DBA at the root level and grant connect and select on V$SESSIONS commonly the user C##USER. To test common privilege to objects.

 

SQL> connect c##dba/password@cdb1

Connected.

SQL> grant create session to c##user container=all;

Grant succeeded.

SQL> grant select on sys.v_$session to c##user container=all;

Grant succeeded.

SQL>

 

  1. Connect the C##USER and execute a select statement on v$session and v$sql objects to validate access from root container. NOTE:  We are able to access object v$session but not v$sql.

 

SQL> connect c##user/password@cdb1

Connected.

SQL> select count(*) from v$session;

 

  COUNT(*)

———-

        37

SQL> select count(*) from v$sql;

select count(*) from v$sql

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

 

  1. Connect to PDB1 and repeat the above queries to validate common access privileges to objects.

 

SQL> connect c##user/password@pdb1

Connected.

SQL> select count(*) from v$session;

  COUNT(*)

———-

        35

SQL> select count(*) from v$sql;

select count(*) from v$sql

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL>

 

  • Privileges should never be given to public from common account.

 

  1. Once a privilege is granted to public, all users have access to that privilege, which is a huge security risk.    However, there is no restrictions built into Oracle to prevent this from occurring.  We will demonstrate this by the creation of new user c##dba2, grant the new user no privileges, grant dba role to public, connect to both the CDB and PDB, and finally create a user.    This is extremely dangerous, because granting privileges to public grants access to system and object privileges to every user.    Finally we will revoke DBA role from public and note that the new user c##dba2 can no longer connect.

 

SQL> connect / as sysdba

Connected.

SQL>  alter session set container=cdb$root;

Session altered.

SQL> create user c##dba2 identified by password;

User created.

SQL> grant dba to public container=all;

Grant succeeded.

SQL> connect c##dba2/password@cdb1

Connected.

SQL> connect c##dba2/password@pdb1

Connected.

SQL> create user testing identified by password;

User created.

 

SQL> connect / as sysdba

Connected.

SQL> revoke dba from public container=all;

 

Revoke succeeded.

 

SQL> connect c##dba2/password@cdb1

ERROR:

ORA-01045: user C##DBA2 lacks CREATE SESSION privilege; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>

This completes overview of Privileges for Common Users in Oracle Multitenant Databases.

 

Larry Catt

OCP

12c, security

Oracle 12c – Implement column and tablespace encryption

Leave a comment

Transparent Data Encryption (TDE) is used for both column and tablespace encryption.    It uses a software key store which is a container that stores the TDE master encryption key.   There exist one TDE master encryption key per database and the location is defined in the SQLNET.ora file.    In previous releases this key store was referred to as an Oracle Wallet.   Software key store must be open before you can create an encrypted column or tablespace and before you can store or retrieve encrypted data.  Once you open, it is open to all sessions until it is explicitly close it or database shutdown.  Transparent Data Encryption is designed to protect data stored on a disk or other media and protects data from access by other means outside of the database.  It protects backups on tape and from accessing data via the OS directly to datafiles.  TDE supports the encryption types:  3DES168, AES128, AES192, AES256.  You specify the encryption algorithm when creating encrypted column or tablespace.  By default TDE used AES192.   TDE adds a salt to plaintext before encrypt to increase security.   TDE also uses Message Authentication Code (MAC) to data for integrity checking and uses SHA-1 by default.

 

Column Encryption

Columns are encrypted and decrypted at the SQL layer and any service that bypasses the SQL layer cannot use TDE column encryption.  TDE column encryption cannot be used with the following features:

  1. Index other than B-TREE
  2. Range scan through indexes.
  3. Synchronous change data capture.
  4. Transportable tablespaces.
  5. Columns used in foreign key constraints.

BUT you can encrypt these columns using DBMS_CRYPTO package.  You can encrypt the following datatypes: Note some size restrictions exist.

  1. BINARY_DOUBLE
  2. BINARY_FLOAT
  3. CHAR
  4. DATA
  5. INTERVAL DAY TO SECOND
  6. INTERVAL YEAR TO MONTH
  7. NCHAR
  8. NUMBER
  9. NVARCHAR2
  10. RAW
  11. TIMESTAMP
  12. VARCHAR2

Example of encrypted column with AES192:

 

CREATE TABLE emp (

ssn number,

fname varchar(100),

lname varchar(100),

salary  number encrypt);

 

Never salt a column that is an index and being encrypted.  Example:

 

CREATE TABLE emp (

ssn number encrypt no salt,

fname varchar(100),

lname varchar(100),

salary  number encrypt);

 

Change encrypt type by the USING clause. Example

 

CREATE TABLE emp (

ssn number encrypt no salt,

fname varchar(100),

lname varchar(100),

salary  number encrypt USING ‘3DES168’);

 

You can use the ALTER TABLE ADD to add an encrypted column to existing table.  You can use the ALTER TABLE MODIFY to make an existing column encrypted.

 

 

Tablespace Encryption

You can encrypt a permanent tablespace to protect the data.  This encrypts all data blocks of any segment type.  Data of an encrypted tablespace is also encrypted in undo, redo and all temp space.   There is no overhead in encrypting tablespace.  Restricting of encrypting tablespaces:

  1. You cannot encrypt an existing tablespace.
  2. Encrypted tablespace cannot be moved to another database.
  3. You must open key store after mount and before open to recover an encrypted tablespace.

 

Creation of encrypted tablespace example

 

CREATE TABLESPACE data_secret

DATAFILE ‘/u01/oradata/data_secret01.dbf’ size 1g

ENCRYPTION

DEFAULT STORAGE(ENCRYPT);

 

 

Larry Catt

OCP