Veritabanı Güvenliği Notları

Veritabanı Güvenliği Notları

veritabanı Güvenliği

 

Advertisements

What are the changes in Oracle database after Oracle DB Vault Installations Part -1

After Oracle DB vault installation; there can be some changes which affect your business. Therefore; you should know these changes and you should be ready to revert these changes

Before Oracle DB Vault installation; you should note all configuration ( system parameters, privileges). And after DB vault installation; we should compare before and after values to prepare the revert script ( If it is necessary )

Let’s start with DAtabase parameters;

Changed Database Parameters after DB Vault Installation

After DB Vault installation; below database parameters change like these;

  • AUDIT_SYS_OPERATIONS  becomes TRUE
  • OS_ROLES becomes FALSE
  • RECYCLEBIN becomes OFF
  • REMOTE_LOGIN_PASSWORDFILE becomes EXCLUSIVE
  • SQL92_SECURITY becomes TRUE

You can use the below query to checks changes. You have to run and keep the result of this query before and after DB Vault installation.

Capture

Changed Privileges after DB Vault Installation

After DB Vault installation; Below privileges will be removed from some roles.

The privileges which are removed from DBA role.

BECOME USER
SELECT ANY TRANSACTION
CREATE ANY JOB
CREATE EXTERNAL JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
DEQUEUE ANY QUEUE
ENQUEUE ANY QUEUE
MANAGE ANY QUEUE

The privileges which are removed from IMP_FULL_DATABASE role

BECOME USER
MANAGE ANY QUEUE

The privileges which are removed from EXECUTE_CATALOG_ROLE role

EXECUTE ON DBMS_LOGMNR
EXECUTE ON DBMS_LOGMNR_D
EXECUTE ON DBMS_LOGMNR_LOGREP_DICT
EXECUTE ON DBMS_LOGMNR_SESSION
EXECUTE ON DBMS_FILE_TRANSFER

The privileges which are removed from PUBLIC user

EXECUTE ON UTL_FILE

The privileges which are removed from SCHEDULER_ADMIN role

ANY JOB
CREATE EXTERNAL JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER

After DB vault installation maybe these changes can affect your business. In that case, you have to use below script to revert these changes.

grant execute on UTL_FILE to Public;
grant BECOME USER to DBA;
grant SELECT ANY TRANSACTION to DBA;
grant CREATE ANY JOB to DBA;
grant CREATE EXTERNAL JOB to DBA;
grant EXECUTE ANY PROGRAM to DBA;
grant EXECUTE ANY CLASS to DBA;
grant MANAGE SCHEDULER to DBA;
grant DEQUEUE ANY QUEUE to DBA;
grant ENQUEUE ANY QUEUE to DBA;
grant MANAGE ANY QUEUE to DBA;

grant BECOME USER to IMP_FULL_DATABASE;
grant MANAGE ANY QUEUE to IMP_FULL_DATABASE;

Grant EXECUTE ON DBMS_LOGMNR to EXECUTE_CATALOG_ROLE;
Grant EXECUTE ON DBMS_LOGMNR_D to EXECUTE_CATALOG_ROLE;
Grant EXECUTE ON DBMS_LOGMNR_LOGREP_DICT to EXECUTE_CATALOG_ROLE;
Grant EXECUTE ON DBMS_LOGMNR_SESSION to EXECUTE_CATALOG_ROLE;
Grant EXECUTE ON DBMS_FILE_TRANSFER to EXECUTE_CATALOG_ROLE;

Grant CREATE ANY JOB to SCHEDULER_ADMIN;
Grant CREATE EXTERNAL JOB to SCHEDULER_ADMIN;
Grant EXECUTE ANY PROGRAM to SCHEDULER_ADMIN;
Grant EXECUTE ANY CLASS to SCHEDULER_ADMIN;
Grant MANAGE SCHEDULER to SCHEDULER_ADMIN;

You can get these all scripts from GitHub.

Thanks for reading this note.

Y. Anıl Akduygu

Oracle DB Vault New Features in Oracle 12c Release 1 – Part 4 : New Realms

Now we continue to work on changes in  Oracle Database Vault  in 12c Release 1 version. Three new Realms are added in this release . These are;

  • Oracle Default Schema Protection Realm
  • Oracle System Privilege and Role Management Realm
  • Oracle Default Component Protection Realm

 

Let’s check these new Realms with the query.

 

SELECT

*

FROM

DVSYS.DBA_DV_REALM;

null

What objects are protected in  these new Realms ? Let’s start with Oracle Default Schema Protection Realm;

SELECT

*

FROM

DVSYS.DBA_DV_REALM_OBJECT

WHERE

REALM_NAME = ‘Oracle Default Schema Protection Realm’;

null

This realm protects roles and schemas in Oracle OLAP, Oracle Spatial, and Oracle Text. (CTXSYS, MDDATA and MDSYS)

 

SELECT

*

FROM

DVSYS.DBA_DV_REALM_AUTH

WHERE

REALM_NAME = ‘Oracle Default Schema Protection Realm’;

null

Oracle System Privilege and Role Management realm. This realm protects sensitive roles which are given below. Some import and export roles , Java roles , audit management roles, catalog operation roles..

null

 

Oracle Default Component Protection Realm ;  This realm protects the SYSTEM and OUTLN schemas.

null

You can get the SQL queries in this note at github

 

Oracle DB Vault New Features in Oracle 12c Release 1 – Part 4 : New Realms

Now we continue to work on changes in Oracle Database Vault in 12c Release 1 version. Three new Realms are added in this release . These are;

  • Oracle Default Schema Protection Realm
  • Oracle System Privilege and Role Management Realm
  • Oracle Default Component Protection Realm

Let’s check these new Realms with the query.

SELECT

*

FROM

DVSYS.DBA_DV_REALM;

What objects are protected in these new Realms ? Let’s start with Oracle Default Schema Protection Realm;

 

FROM

dvsys.DBA_DV_REALM_OBJECT

WHERE

REALM_NAME = ‘Oracle Default Schema Protection Realm’;

 

This realm protects roles and schemas in Oracle OLAP, Oracle Spatial, and Oracle Text. (CTXSYS, MDDATA and MDSYS)

SELECT

*

FROM

DVSYS.DBA_DV_REALM_AUTH

WHERE

REALM_NAME = ‘Oracle Default Schema Protection Realm’;

Oracle System Privilege and Role Management realm. This realm protects sensitive roles which are given below. Some import and export roles , Java roles , audit management roles, catalog operation roles..

Oracle Default Component Protection Realm ; This realm protects the SYSTEM and OUTLN schemas.

My Book About Oracle Database Security

My book about the Oracle Database security “ORACLE VERİTABANI GÜVENLİĞİ” is on the sale. You can find it D&R stores at Turkey. At the web;  you can get it from ABAKUS bookstore Web-site.

http://www.abakuskitap.com/oracle-veritabani-guvenligi

This book is about general Oracle database security and includes Oracle 11g and Oracle 12c  versions. It is a Turkish book.

 

oracle_guvenlik

Main Topics on the book

  • Oracle User Management
  • Enterprise User Security
  • Secure External Password Store
  • Authorization
  • Privilege Analysis
  • Application Context
  • Virtual Private Database
  • Security at Oracle Container Databases
  • Data Reduction
  • Auditing
  • New Security Features at Oracle 12c
  • Transperant Data Reduction
  • Encryption on the network
  • Security for Network Services

 

oracle_guvenlik-1

If you are interested to translate it to Engish , Please Contact to me.

 

Anıl Akduygu

 

yusufanilakduygu@gmail.com

Adding Oracle Label Security and DB Vault Options to Oracle 12c Database

Now at this note; I will show you how you can install Oracle Label Security and DB vault to Oracle 12c database.

Please be sure that Oracle Label Security( OLS ) and DB Vault is not installed yet.

Below query should return no rows at this case.

SQL> select comp_id,status from dba_registry where comp_id in (‘OLS’,’DV’);

no rows selected

If Oracle Label Security and DB Vault is already installed. You just need to register DB Vault.In that case follow below link.

https://yusufanilakduygu.wordpress.com/2016/07/28/installing-db-vault-to-an-oracle-12c-non-container-database/

Now  Let me introduce the environment;

Host : Oracle 7 Linux virtual  machine on Oracle VM Virtual Box

DB : Oracle 12c 12.1.0.2.0 RAC non-container database with four nodes.

Start  Database configuration assitant to install OLS and DB Vault.

$ dbca

Choose “Configure Database Options”

12c DB Vault-01 (1)

 

Now Select the database to install OLS and DB Vault and enter sys password.

12c DB Vault-02

 

Skip Directory Service tab ( you are not installing OID )12c DB Vault-03

 

Choose Oracle Label Security and Oracle Database Vault at Database Components tab.

12c DB Vault-04

 

Now ; enter DB Vault owner and Account Manager  usernames and passwords separately

12c DB Vault-06

 

Select the mode of Oracle database . Normally Dedicated Server.

12c DB Vault-07

 

Now a summary page will appear.

12c DB Vault-08

 

Select Yes at confirmation window

12c DB Vault-09

 

And the installation will start. You will see the progress

12c DB Vault-10

 

At the end the configuration has completed.

12c DB Vault-11

 

Now OLS and DB vault installed and DB Vault is registered as well.

You can check it with below queries.

SQL> select comp_id,status from dba_registry where comp_id in (‘OLS’,’DV’);

COMP_ID STATUS
—————————— ———–
DV VALID
OLS VALID

SQL> column parameter format a25
SQL> column value format a10
SQL>
SQL> SELECT * FROM gv$OPTION WHERE PARAMETER in ( ‘Oracle Database Vault’,’Oracle Label Security’);

INST_ID PARAMETER VALUE CON_ID
———- ————————- ———- ———-
1 Oracle Label Security TRUE 0
1 Oracle Database Vault TRUE 0
4 Oracle Label Security TRUE 0
4 Oracle Database Vault TRUE 0
3 Oracle Label Security TRUE 0
3 Oracle Database Vault TRUE 0
2 Oracle Label Security TRUE 0
2 Oracle Database Vault TRUE 0

 

Thanks in advance

 

Anıl Akduygu.

DB Vault Installation to Oracle 12c Container Database

12c-architecture

At my latest post I talked about Oracle DB Vault Installation to Oracle 12c non-container database. At this post I want to show you how you can install  Oracle DB Vault to Oracle 12c Container database.

Actually the task is very similar. But for the container databases; you should first install the root database; and then you can install to any pluggable database.

To continue this post ; you should have basic knowledge about Oracle Container databases.

https://oracle-base.com/articles/12c/multitenant-overview-container-database-cdb-12cr1

Before stating installation process; I wil show you my configuration

Host : Oracle 7 Linux

DB : Oracle 12c  12.1.0.2.0 with two pluggable databases  pdb1 and pdb2 and the database name is CDB3

During the installation I will connect to  the root container and pluggable  database by using below tns settings. As you know when you create a pluggable database a service is created by the name of pluggable database automatically.

at your tnsnames.ora file there should be tns entries   like that;
CDB3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.200.11.9)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDB3)
)
)

pdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.200.11.9)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)

pdb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.200.11.9)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2)
)
)

at this post; first I will install DB vault to root container and then pdb1 pluggable database.

Installing DB Vault to root container

1.  First check If DB Vault is alreday installed
SQL> connect SYSTEM@CDB3
Enter password:
Connected.
SQL> show con_name

CON_NAME
——————————
CDB$ROOT
SQL> column parameter format a25
SQL> column value format a10
SQL> SELECT parameter,value FROM gv$OPTION WHERE PARAMETER in
2 ( ‘Oracle Database Vault’,’Oracle Label Security’);

PARAMETER VALUE
————————- ———-
Oracle Label Security FALSE
Oracle Database Vault FALSE

After DBVault installation all these values become TRUE

 

2. Take  copy of some views about privileges;

At the SYSTEM user take some copy of privilege views at the root container. To compare privileges after DB vault installation.

SQL> create table a_cdb_network_acls as select * FROM cdb_network_acls;

Table created.

SQL> create table a_cdb_network_acl_privileges as select * from cdb_network_acl_privileges;

Table created.

SQL> create table a_cdb_tab_privs as Select * from cdb_tab_privs;

Table created.

SQL> create table a_cdb_sys_privs as Select * from cdb_sys_privs;

Table created.

SQL> create table a_cdb_role_privs as Select * from cdb_role_privs;

Table created.

SQL> create table a_cdb_objects as select owner,object_name,object_type from cdb_objects where status=’INVALID’ and object_type <> ‘SYNONYM’ ;

Table created.

SQL> create table a_cdb_registry as select * from cdb_registry;

Table created.

SQL>

3. Create DV Owner and DV  Account Manager User

DV owner user administers  DB Vault and DV Account Manager user administers all Oracle users. Because of the separation of duties these two users must be different.

for container databases we create common users .

connect sys as sysdba
SQL> create user c##dvowner identified by oracle CONTAINER=ALL;

User created.

SQL> create user c##dvacctmngr identified by oracle CONTAINER=ALL;

User created.

SQL> grant SET CONTAINER,CREATE SESSION to c##dvowner;

Grant succeeded.

SQL> grant SET CONTAINER,CREATE SESSION to c##dvacctmngr;

Grant succeeded.

4. Configure DB Vault

SQL>
SQL> BEGIN
2 DVSYS.CONFIGURE_DV (
3 dvowner_uname => ‘c##dvowner’,
4 dvacctmgr_uname => ‘c##dvacctmngr’);
5 END;
6 /

PL/SQL procedure successfully completed

And compile invalid objects

@?/rdbms/admin/utlrp.sql

…Database user “SYS”, database schema “APEX_040200”, user# “98” 16:45:10
…Compiled 0 out of 3014 objects considered, 0 failed compilation 16:45:10
…271 packages
…263 package bodies
…452 tables
…11 functions
…16 procedures
…3 sequences
…457 triggers
…1320 indexes
…211 views
…0 libraries
…6 types
…0 type bodies
…0 operators
…0 index types
…Begin key object existence check 16:45:10
…Completed key object existence check 16:45:11
…Setting DBMS Registry 16:45:11
…Setting DBMS Registry Complete 16:45:11
…Exiting validate 16:45:11

PL/SQL procedure successfully completed.

5. Enable DB Vault

SQL> connect c##dvowner
Enter password:
Connected.
SQL> show con_name

CON_NAME
——————————
CDB$ROOT
SQL> EXEC DBMS_MACADM.ENABLE_DV;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

6. Resart the Database 

Bingo DB Vault is ready now at container database.

SQL> connect sys as sysdba
Enter password:
Connected.

SQL> startup force
ORACLE instance started.

Total System Global Area 977272832 bytes
Fixed Size 2931520 bytes
Variable Size 645924032 bytes
Database Buffers 322961408 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.

SQL> alter pluggable database all open;

Pluggable database altered.
SQL> column parameter format a25
SQL> column value format a10
SQL> SELECT parameter,value FROM gv$OPTION WHERE PARAMETER in
2 ( ‘Oracle Database Vault’,’Oracle Label Security’);

PARAMETER VALUE
————————- ———-
Oracle Label Security TRUE
Oracle Database Vault TRUE

 

Now our aim is to install Db Vault one of the our pluggable database. For the demonstration I will install DB Vault to PDB1 pluggable database.

7. Give grants common users to connect PDB1

 

SQL> connect sys@pdb1 as sysdba
Enter password:
Connected.
SQL> show con_name

CON_NAME
——————————
PDB1
SQL> grant SET CONTAINER,CREATE SESSION to c##dvowner;

Grant succeeded.

SQL> grant SET CONTAINER,CREATE SESSION to c##dvacctmngr;

Grant succeeded.

8. Configure DB Vault at PDB1

SQL> connect sys@pdb1 as sysdba
Enter password:
Connected.
SQL> show con_name

CON_NAME
——————————
PDB1

SQL> BEGIN
2 DVSYS.CONFIGURE_DV (
3 dvowner_uname => ‘c##dvowner’,
4 dvacctmgr_uname => ‘c##dvacctmngr’);
5 END;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

now compile invalid objects

@?/rdbms/admin/utlrp.sql

…Database user “SYS”, database schema “APEX_040200”, user# “98” 16:59:40
…Compiled 0 out of 3014 objects considered, 0 failed compilation 16:59:41
…271 packages
…263 package bodies
…452 tables
…11 functions
…16 procedures
…3 sequences
…457 triggers
…1320 indexes
…211 views
…0 libraries
…6 types
…0 type bodies
…0 operators
…0 index types
…Begin key object existence check 16:59:41
…Completed key object existence check 16:59:41
…Setting DBMS Registry 16:59:41
…Setting DBMS Registry Complete 16:59:41
…Exiting validate 16:59:41

PL/SQL procedure successfully completed.

8. Enable DB Vault at PDB1

 

SQL> connect c##dvowner@pdb1
Enter password:
Connected.
SQL> EXEC DBMS_MACADM.ENABLE_DV;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete

 

9. Restart PDB1 pluggable database

SQL> startup force;
ORACLE instance started.

Total System Global Area 977272832 bytes
Fixed Size 2931520 bytes
Variable Size 645924032 bytes
Database Buffers 322961408 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

 

Now DB Vault is intalled to PD1 pluggable database