a first look at database vault david bergmeier l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
A First look at Database Vault David Bergmeier PowerPoint Presentation
Download Presentation
A First look at Database Vault David Bergmeier

Loading in 2 Seconds...

play fullscreen
1 / 119

A First look at Database Vault David Bergmeier - PowerPoint PPT Presentation


  • 310 Views
  • Uploaded on

A First look at Database Vault David Bergmeier. Agenda. Overview Installation Limitations Securing Data Backups A trigger problem. About me. Senior Oracle DBA Worked for MGA nearly 2 years Background as an Analyst/Programmer 12 years in financial services industry

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'A First look at Database Vault David Bergmeier' - dericia


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
agenda
Agenda
  • Overview
  • Installation
  • Limitations
  • Securing Data
  • Backups
  • A trigger problem
about me
About me
  • Senior Oracle DBA
  • Worked for MGA nearly 2 years
  • Background as an Analyst/Programmer
  • 12 years in financial services industry
  • Started using Oracle in 1996
overview
Overview

Why Oracle Database Vault?

  • Don’t trust the DBA
  • Regulatory Compliance(e.g. Sarbanes Oxley)
  • Separation of duties
separation of duties
Separation of duties

connect / as sysdba

create user david ...

grant dba to david;

select * from scott.emp;

separation of duties6
Separation of duties

connect / as sysdba

create user david ...

grant dba to david;

select * from scott.emp;

agenda10
Agenda
  • Overview
  • Installation
  • Limitations
  • Securing Data
  • Backups
  • A trigger problem
prerequisites
Prerequisites
  • Oracle 10.2.0.3
  • 1024 MB of Physical RAM
  • Swap space (1.5 times RAM)
  • 400 MB in /tmp
  • 270 MB for database vault binaries
  • 10 MB additional for database files
prerequisites12
Prerequisites

Installation

  • Assumes one instance per Oracle home
  • But can support more
installation14
Installation

User to receive DV_OWNER role

installation15
Installation

Passwords must have alpha, numeric & special

installation16
Installation

User to receive DV_ACCTMGR role

agenda24
Agenda
  • Overview
  • Installation
  • Limitations
  • Securing Data
  • Backups
  • A trigger problem
the first problem
The First Problem

Let’s start the database

the first problem28
The First Problem

I cannot login as SYDBA

So how do I start/stop Oracle?

the first problem29
The First Problem

connect / as SYSOPER

agenda31
Agenda
  • Overview
  • Installation
  • Limitations
  • Securing Data
  • Backups
  • A trigger problem
securing some data
Securing Some Data

$ lsnrctl start

$ emctl start dbconsole

securing some data33
Securing Some Data

$ sqlplus system/manager

SQL> select * from scott.emp;

...

14 rows selected.

SQL>

what is a realm
What is a Realm?

A realm is a functional grouping of schemas and roles that are secured.

what is a realm39

Realm

Authorizations

Secured Objects

What is a Realm?

One

Many

securing some data47
Securing Some Data

SQL> select * from scott.emp;

select * from scott.emp

*

ERROR at line 1:

ORA-01031: Insufficient Privileges

SQL>

securing some data48
Securing Some Data

SQL> select * from scott.dept;

DEPTNO DNAME LOC

---------- -------------- --------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL>

securing some data49
Securing Some Data

That’s the end of the tutorial.

So now let’s consider a real world application.

real world example

application user

SCOTT

Real world Example

Application server connects to database as single user

EMP

real world example51

application user

support users

SCOTT

Real world Example

Support users connect with individual accounts with read-only access

EMP

real world example52

scott_app_user

scott_ro_role

scott_ro

SCOTT

Real world Example

grant select insert

update delete

grant role

EMP

grant select

create user
Create User

SQL> connect system/manager

SQL> create user scott_app_user

2> identified by tiger

3> default tablespace USERS;

identified by tiger

*

ERROR at line 2:

ORA-01031: Insufficient Privileges

create user54
Create User

SQL> connect dbu/manager

SQL> create user scott_app_user

2> identified by tiger

3> default tablespace USERS;

User created.

SQL> grant connect to scott_app_user;

create user55
Create User

SQL> connect dbu/manager

SQL> create user scott_ro

2> identified by tiger

3> default tablespace USERS;

User created.

SQL> grant connect to scott_ro;

create role
Create Role

SQL> connect system/manager

SQL> create role scott_ro_role;

Role created.

SQL> grant scott_ro_role to scott_ro;

Grant succeeded.

SQL>

grants
Grants

SQL> connect scott/tiger

SQL> grant select,insert,update,delete on emp to scott_app_user;

Grant succeeded.

SQL> grant select on emp to scott_ro_role;

Grant succeeded.

SQL>

real world example58
Real world Example

Now to test it...

testing scott ro
Testing scott_ro

SQL> connect scott_ro/tiger

SQL> select * from scott.emp;

14 rows selected.

SQL> delete from scott.emp;

delete from scott.emp

*

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing scott ro60
Testing scott_ro

SQL> connect scott_ro/tiger

SQL> select * from scott.emp;

14 rows selected.

SQL> delete from scott.emp;

delete from scott.emp

*

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing scott app user
Testing scott_app_user

SQL> connect scott_app_user/tiger

SQL> select * from scott.emp;

14 rows selected.

SQL> delete from scott.emp;

14 rows deleted.

SQL> rollback;

testing scott app user62
Testing scott_app_user

SQL> connect scott_app_user/tiger

SQL> select * from scott.emp;

14 rows selected.

SQL> delete from scott.emp;

14 rows deleted.

SQL> rollback;

testing system
Testing system

SQL> connect system/manager

SQL> select * from scott.emp;

14 rows selected.

SQL> delete from scott.emp;

delete from scott.emp

*

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing system64
Testing system

SQL> connect system/manager

SQL> select * from scott.emp;

14 rows selected.

SQL> delete from scott.emp;

delete from scott.emp

*

ERROR at line 1:

ORA-01031: Insufficient Privileges

what went wrong
What went wrong?

SQL> connect system/manager

SQL> select * from session_roles;

ROLE---------------------------DV_PUBLICDBA...SCOTT_RO_ROLE

14 rows selected.

SQL>

what went wrong66
What went wrong?

How did SYSTEM get SCOTT_RO_ROLE?

what went wrong67
What went wrong?

SQL> connect system/manager

SQL> create role foo;

Role created.

SQL> set role all;

Role set.

SQL> select * from session_roles;

ROLE---------------------------DV_PUBLIC...FOO

what went wrong68
What went wrong?

So now we have a problem!

If we only revoke the role, SYSTEM can grant it again.

How do we prevent this?

remove the role
Remove the Role

SQL> connect system/manager

SQL> drop role scott_ro_role;

Role dropped.

SQL> select * from session_roles;

ROLE---------------------------DV_PUBLIC...MGMT_USER

13 rows selected.

SQL>

problem with dv acctmgr
Problem with DV_ACCTMGR

DV_ACCTMGR has

  • create/drop user
  • alter user account lock/unlock
  • alter user password expire
  • grant/revoke CONNECT role
problem with dv acctmgr71
Problem with DV_ACCTMGR

DV_ACCTMGR needs

  • create role
  • alter any role
  • drop any role
  • SELECT_CATALOG_ROLE

To get these, we need to login as SYSDBA

allow sysdba
Allow SYSDBA

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwmozart password=mozart entries=20 force=y nosysdba=n

$ sqlplus sys/mozart as sysdba

SQL> startup

SQL> alter user sys identified by mozart;

grants to dv acctmgr
Grants to DV_ACCTMGR

SQL> connect sys/mozart as sysdba

SQL> grant create role to DV_ACCTMGR;

SQL> grant alter any role to DV_ACCTMGR;

SQL> grant drop any role to DV_ACCTMGR;

create role as dv acctmgr
Create Role as DV_ACCTMGR

SQL> connect dbu/manager

SQL> create role scott_ro_role;

Role created.

SQL>

At this stage we delay granting scott_ro_role

granting scott ro role
Granting SCOTT_RO_ROLE

SQL> connect dbu/manager

SQL> grant scott_ro_role to scott_ro;

grant scott_ro_role to scott_ro

*

ERROR at line 1:

ORA-47401: Realm violation for grant role privilege on SCOTT_RO_ROLE

granting scott ro role83
Granting SCOTT_RO_ROLE

So who can/shoulddo the grant of SCOTT_RO_ROLE ?

granting scott ro role84
Granting SCOTT_RO_ROLE

So who can/shoulddo the grant of SCOTT_RO_ROLE ?

Answer: SCOTT

granting scott ro role85
Granting SCOTT_RO_ROLE

Answer: SCOTT

Provided SCOTT can only grant SCOTT_RO_ROLE and not other roles like DBA.

granting scott ro role86
Granting SCOTT_RO_ROLE

One more grant as SYSDBA

SQL> connect sys/mozart as sysdba

SQL> grant grant any role to scott;

Grant succeeded.

SQL>

granting scott ro role87
Granting SCOTT_RO_ROLE

SQL> connect scott/tiger

SQL> grant scott_ro_role to scott_ro;

Grant succeeded.

SQL> revoke scott_ro_role from dbu;

Revoke succeeded.

SQL>

granting scott ro role88
Granting SCOTT_RO_ROLE

SQL> connect scott/tiger

SQL> grant DBA to scott;

grant DBA to scott

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-47401: Realm violation for grant role privilege on UNLIMITED TABLESPACE.

granting scott ro role90
Granting SCOTT_RO_ROLE

The DBA role is protected by the “Oracle Data Dictionary” Realm.

granting scott ro role91
Granting SCOTT_RO_ROLE

Now to test it...

Again

testing scott ro again
Testing scott_ro again

SQL> connect scott_ro/tiger

SQL> select * from scott.emp;

14 rows selected.

SQL> delete from scott.emp;

delete from scott.emp

*

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing scott ro again93
Testing scott_ro again

SQL> connect scott_ro/tiger

SQL> select * from scott.emp;

14 rows selected.

SQL> delete from scott.emp;

delete from scott.emp

*

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing scott app user94
Testing scott_app_user

SQL> connect scott_app_user/tiger

SQL> select * from scott.emp;

14 rows selected.

SQL> delete from scott.emp;

14 rows deleted.

SQL> rollback;

testing scott app user95
Testing scott_app_user

SQL> connect scott_app_user/tiger

SQL> select * from scott.emp;

14 rows selected.

SQL> delete from scott.emp;

14 rows deleted.

SQL> rollback;

testing system again
Testing system again

SQL> connect system/manager

SQL> select * from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

SQL> delete from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing system again97
Testing system again

SQL> connect system/manager

SQL> select * from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

SQL> delete from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing sysdba
Testing SYSDBA

SQL> connect sys/mozart as sysdba

SQL> select * from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

SQL> delete from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing sysdba99
Testing SYSDBA

SQL> connect sys/mozart as sysdba

SQL> select * from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

SQL> delete from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing dv acctmgr
Testing DV_ACCTMGR

SQL> connect dbu/manager

SQL> select * from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

SQL> delete from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing dv acctmgr101
Testing DV_ACCTMGR

SQL> connect dbu/manager

SQL> select * from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

SQL> delete from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing dv admin
Testing DV_ADMIN

SQL> connect dbv/manager

SQL> select * from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

SQL> delete from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

testing dv admin103
Testing DV_ADMIN

SQL> connect dbv/manager

SQL> select * from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

SQL> delete from scott.emp;

ERROR at line 1:

ORA-01031: Insufficient Privileges

separation of duties104
Separation of Duties

Let’s review the actions performed by each of the different users/roles

separation of duties105
Separation of Duties

SYS as SYSDBA

  • Grant role privileges to DV_ACCTMGR(one time)
  • Grant “grant any role” to SCOTT(once per application)
separation of duties106
Separation of Duties

DV_ADMIN (user = dbv)

  • Realm authorizations (once per application)
  • Command Rules(one time)
separation of duties107
Separation of Duties

DV_ACCTMGR (user = dbu)

  • Create user (ongoing)
  • Grant connect (ongoing)
  • Create role (once per app)
separation of duties108
Separation of Duties

Schema owner (SCOTT)

  • Grant object privileges(once per application)
  • Grant SCOTT_RO_ROLE (ongoing)
separation of duties109
Separation of Duties

DBA (user = system)

  • Nothing
agenda110
Agenda
  • Overview
  • Installation
  • Limitations
  • Securing Data
  • Backups
  • A trigger problem
backups
Backups

Impact of Backups

  • Export
  • Data Pump
  • RMAN
backups112
Backups

Export

  • Lots of ORA-01031
  • Will be unable to Import
  • Not viable
backups113
Backups

Data Pump

  • Not tested
backups114
Backups

RMAN

  • Requires SYSDBA access
  • May need to hardcode SYS password or use wallet
  • Works successfully
agenda115
Agenda
  • Overview
  • Installation
  • Limitations
  • Securing Data
  • Backups
  • A trigger problem
trigger problem
Trigger Problem

Error creating trigger

  • Minor changes to whitespace in trigger source caused compile success/failure
  • Known Bug: 5630439
  • ORA-47999: internal Database Vault error: create trigger
trigger problem117
Trigger Problem

Workaround available

  • Login as dv_owner account
  • alter trigger dvsys.DV_BEFORE_DDL_TRG disable
  • Login as SCOTT and create trigger
  • Login as dv_owner account
  • alter trigger dvsys.DV_BEFORE_DDL_TRG enable
conclusion
Conclusion
  • You probably don’t need Database Vault
  • It’s a trade off between more security with more bureaucracy
  • It seems to work okay but there are some bugs
  • Typical work arounds involve deactivating Database Vault
the end
The End

Thank you for your attendance

dbergmeier@mga-it.com

http://www.mga.com.au