A First look at Database Vault David Bergmeier

1
A First look atDatabase VaultDavid Bergmeier
2
Agenda

• Overview
• Installation
• Limitations
• Securing Data
• Backups
• A trigger problem

3
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

4
Overview

• Why Oracle Database Vault?
• Dont trust the DBA
• Regulatory Compliance(e.g. Sarbanes Oxley)
• Separation of duties

5
Separation of duties
connect / as sysdba create user david ... grant
dba to david select from scott.emp
6
Separation of duties
connect / as sysdba create user david ... grant
dba to david select from scott.emp
7
Separation of duties
8
Separation of duties
9
Separation of duties
10
Agenda

• Overview
• Installation
• Limitations
• Securing Data
• Backups
• A trigger problem

11
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

12
Prerequisites

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

13
Installation
14
Installation
User to receive DV_OWNER role
15
Installation
Passwords must have alpha, numeric special
16
Installation
User to receive DV_ACCTMGR role
17
Installation
18
Installation
19
Installation
20
Installation
21
Installation
22
Installation
23
Installation
24
Agenda

• Overview
• Installation
• Limitations
• Securing Data
• Backups
• A trigger problem

25
The First Problem

• Lets start the database

26
The First Problem
27
The First Problem
28
The First Problem

• I cannot login as SYDBA
• So how do I start/stop Oracle?

29
The First Problem

• connect / as SYSOPER

30
The First Problem
31
Agenda

• Overview
• Installation
• Limitations
• Securing Data
• Backups
• A trigger problem

32
Securing Some Data

• lsnrctl start
• emctl start dbconsole

33
Securing Some Data

• sqlplus system/manager
• SQLgt select from scott.emp
• ...
• 14 rows selected.
• SQLgt

34
Securing Some Data
35
Securing Some Data
36
Securing Some Data
37
Securing Some Data
38
What is a Realm?

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

39
What is a Realm?
One
Many
40
Securing Some Data
41
Securing Some Data
42
Securing Some Data
43
Securing Some Data
44
Securing Some Data
45
Securing Some Data
46
Securing Some Data
47
Securing Some Data

• SQLgt select from scott.emp
• select from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges
• SQLgt

48
Securing Some Data

• SQLgt select from scott.dept
• DEPTNO DNAME LOC
• ---------- -------------- --------
• 10 ACCOUNTING NEW YORK
• 20 RESEARCH DALLAS
• 30 SALES CHICAGO
• 40 OPERATIONS BOSTON
• SQLgt

49
Securing Some Data

• Thats the end of the tutorial.
• So now lets consider a real world application.

50
Real world Example
Application server connects to database as single
user
EMP
51
Real world Example
Support users connect with individual accounts
with read-only access
EMP
52
Real world Example
grant select insert update delete
grant role
EMP
grant select
53
Create User

• SQLgt connect system/manager
• SQLgt create user scott_app_user
• 2gt identified by tiger
• 3gt default tablespace USERS
• identified by tiger
• ERROR at line 2
• ORA-01031 Insufficient Privileges

54
Create User

• SQLgt connect dbu/manager
• SQLgt create user scott_app_user
• 2gt identified by tiger
• 3gt default tablespace USERS
• User created.
• SQLgt grant connect to
  scott_app_user

55
Create User

• SQLgt connect dbu/manager
• SQLgt create user scott_ro
• 2gt identified by tiger
• 3gt default tablespace USERS
• User created.
• SQLgt grant connect to scott_ro

56
Create Role

• SQLgt connect system/manager
• SQLgt create role scott_ro_role
• Role created.
• SQLgt grant scott_ro_role to
  scott_ro
• Grant succeeded.
• SQLgt

57
Grants

• SQLgt connect scott/tiger
• SQLgt grant select,insert,update,delete on emp to
  scott_app_user
• Grant succeeded.
• SQLgt grant select on emp to
  scott_ro_role
• Grant succeeded.
• SQLgt

58
Real world Example

• Now to test it...

59
Testing scott_ro

• SQLgt connect scott_ro/tiger
• SQLgt select from scott.emp
• 14 rows selected.
• SQLgt delete from scott.emp
• delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

60
Testing scott_ro

• SQLgt connect scott_ro/tiger
• SQLgt select from scott.emp
• 14 rows selected.
• SQLgt delete from scott.emp
• delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

61
Testing scott_app_user

• SQLgt connect scott_app_user/tiger
• SQLgt select from scott.emp
• 14 rows selected.
• SQLgt delete from scott.emp
• 14 rows deleted.
• SQLgt rollback

62
Testing scott_app_user

• SQLgt connect scott_app_user/tiger
• SQLgt select from scott.emp
• 14 rows selected.
• SQLgt delete from scott.emp
• 14 rows deleted.
• SQLgt rollback

63
Testing system

• SQLgt connect system/manager
• SQLgt select from scott.emp
• 14 rows selected.
• SQLgt delete from scott.emp
• delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

64
Testing system

• SQLgt connect system/manager
• SQLgt select from scott.emp
• 14 rows selected.
• SQLgt delete from scott.emp
• delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

65
What went wrong?

• SQLgt connect system/manager
• SQLgt select from session_roles
• ROLE---------------------------DV_PUBLICDBA..
  .SCOTT_RO_ROLE
• 14 rows selected.
• SQLgt

66
What went wrong?

• How did SYSTEM get SCOTT_RO_ROLE?

67
What went wrong?

• SQLgt connect system/manager
• SQLgt create role foo
• Role created.
• SQLgt set role all
• Role set.
• SQLgt select from session_roles
• ROLE---------------------------DV_PUBLIC...FO
  O

68
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?
69
Remove the Role

• SQLgt connect system/manager
• SQLgt drop role scott_ro_role
• Role dropped.
• SQLgt select from session_roles
• ROLE---------------------------DV_PUBLIC...MG
  MT_USER
• 13 rows selected.
• SQLgt

70
Problem with DV_ACCTMGR

• DV_ACCTMGR has
• create/drop user
• alter user account lock/unlock
• alter user password expire
• grant/revoke CONNECT role

71
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

72
Allow SYSDBA

• cd ORACLE_HOME/dbs
• orapwd fileorapwmozart passwordmozart
  entries20 forcey nosysdban
• sqlplus sys/mozart as sysdba
• SQLgt startup
• SQLgt alter user sys identified
  by mozart

73
Grants to DV_ACCTMGR

• SQLgt connect sys/mozart as sysdba
• SQLgt grant create role to
  DV_ACCTMGR
• SQLgt grant alter any role to
  DV_ACCTMGR
• SQLgt grant drop any role to
  DV_ACCTMGR

74
SELECT_CATALOG_ROLE
75
SELECT_CATALOG_ROLE
76
Fixing DV_ACCTMGR
77
Fixing DV_ACCTMGR
78
Fixing DV_ACCTMGR
79
Create Role as DV_ACCTMGR

• SQLgt connect dbu/manager
• SQLgt create role scott_ro_role
• Role created.
• SQLgt

At this stage we delay granting scott_ro_role
80
Securing SCOTT_RO_ROLE
81
Securing SCOTT_RO_ROLE
82
Granting SCOTT_RO_ROLE

• SQLgt connect dbu/manager
• SQLgt 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

83
Granting SCOTT_RO_ROLE

• So who can/shoulddo the grant of SCOTT_RO_ROLE ?

84
Granting SCOTT_RO_ROLE

• So who can/shoulddo the grant of SCOTT_RO_ROLE ?
• Answer SCOTT

85
Granting SCOTT_RO_ROLE

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

86
Granting SCOTT_RO_ROLE

• One more grant as SYSDBA

SQLgt connect sys/mozart as sysdba SQLgt grant
grant any role to scott Grant succeeded. SQLgt
87
Granting SCOTT_RO_ROLE

• SQLgt connect scott/tiger
• SQLgt grant scott_ro_role to
  scott_ro
• Grant succeeded.
• SQLgt revoke scott_ro_role
  from dbu
• Revoke succeeded.
• SQLgt

88
Granting SCOTT_RO_ROLE

• SQLgt connect scott/tiger
• SQLgt 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.

89
Granting SCOTT_RO_ROLE

• WHY?

90
Granting SCOTT_RO_ROLE

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

91
Granting SCOTT_RO_ROLE

• Now to test it...
• Again

92
Testing scott_ro again

• SQLgt connect scott_ro/tiger
• SQLgt select from scott.emp
• 14 rows selected.
• SQLgt delete from scott.emp
• delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

93
Testing scott_ro again

• SQLgt connect scott_ro/tiger
• SQLgt select from scott.emp
• 14 rows selected.
• SQLgt delete from scott.emp
• delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

94
Testing scott_app_user

• SQLgt connect scott_app_user/tiger
• SQLgt select from scott.emp
• 14 rows selected.
• SQLgt delete from scott.emp
• 14 rows deleted.
• SQLgt rollback

95
Testing scott_app_user

• SQLgt connect scott_app_user/tiger
• SQLgt select from scott.emp
• 14 rows selected.
• SQLgt delete from scott.emp
• 14 rows deleted.
• SQLgt rollback

96
Testing system again

• SQLgt connect system/manager
• SQLgt select from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges
• SQLgt delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

97
Testing system again

• SQLgt connect system/manager
• SQLgt select from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges
• SQLgt delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

98
Testing SYSDBA

• SQLgt connect sys/mozart as sysdba
• SQLgt select from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges
• SQLgt delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

99
Testing SYSDBA

• SQLgt connect sys/mozart as sysdba
• SQLgt select from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges
• SQLgt delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

100
Testing DV_ACCTMGR

• SQLgt connect dbu/manager
• SQLgt select from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges
• SQLgt delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

101
Testing DV_ACCTMGR

• SQLgt connect dbu/manager
• SQLgt select from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges
• SQLgt delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

102
Testing DV_ADMIN

• SQLgt connect dbv/manager
• SQLgt select from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges
• SQLgt delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

103
Testing DV_ADMIN

• SQLgt connect dbv/manager
• SQLgt select from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges
• SQLgt delete from scott.emp
• ERROR at line 1
• ORA-01031 Insufficient Privileges

104
Separation of Duties

• Lets review the actions performed by each of the
  different users/roles

105
Separation of Duties

• SYS as SYSDBA
• Grant role privileges to DV_ACCTMGR(one time)
• Grant grant any role to SCOTT(once per
  application)

106
Separation of Duties

• DV_ADMIN (user dbv)
• Realm authorizations (once per application)
• Command Rules(one time)

107
Separation of Duties

• DV_ACCTMGR (user dbu)
• Create user (ongoing)
• Grant connect (ongoing)
• Create role (once per app)

108
Separation of Duties

• Schema owner (SCOTT)
• Grant object privileges(once per application)
• Grant SCOTT_RO_ROLE (ongoing)

109
Separation of Duties

• DBA (user system)
• Nothing

110
Agenda

• Overview
• Installation
• Limitations
• Securing Data
• Backups
• A trigger problem

111
Backups

• Impact of Backups
• Export
• Data Pump
• RMAN

112
Backups

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

113
Backups

• Data Pump
• Not tested

114
Backups

• RMAN
• Requires SYSDBA access
• May need to hardcode SYS password or use wallet
• Works successfully

115
Agenda

• Overview
• Installation
• Limitations
• Securing Data
• Backups
• A trigger problem

116
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

117
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

118
Conclusion

• You probably dont need Database Vault
• Its 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

119
The End
Thank you for your attendance
dbergmeier_at_mga-it.com http//www.mga.com.au