Lock It Down

Limit authorization to only those who need it, and remove all-powerful superuser roles with Oracle Database Vault.

March/April 2016

John, the principal database architect at Acme Bank, is no stranger to occasionally having irate visitors in his office, but as he looks through the steam of his fresh coffee, the tension today seems magnified. In view of various laws, regulations, and mandates such as Sarbanes-Oxley, HIPAA, GLBA, and PCI, Audrey, the IT auditor at the bank, wants to ensure that the privileges assigned to people are limited to only what they actually need—nothing more. For example, production support DBAs who manage the database infrastructure, take backups, and so on should have privileges to do all that but not have access to data such as account information.

It’s a fair request, everyone concedes. However, the “DBA” database role, which is granted to Acme’s DBAs for managing the database infrastructure, also includes other powerful privileges such as the ability to select and delete from any table from any schema, including the audit trails of these activities. Those privileges can’t just be stripped from that role; they are integral parts of it. Audrey wants the DBAs to lose those privileges, but the DBAs can’t do their job without the DBA role, explains Debbie, the DBA manager. It’s not that DBAs actually use those privileges to select from sensitive tables or delete from audit trails, so having those privileges makes no difference, argues Debbie.

But that’s the point, explains Audrey, whose patience seems to be on the brink of collapse; it’s the possibility of the use of these privileges—not the actual use—that is unacceptable. There have been many instances of a malicious attacker using DBA privileges to steal sensitive data and even of rogue DBAs stealing data and erasing the audit trails, using the all-powerful DBA role privileges. And isn’t it usually the DBAs who are most likely to be investigated when a data breach occurs? asks Audrey. Without those privileges, she explains, their liability will be dramatically reduced. Debbie considers this logic and immediately sees the value of Audrey’s request but explains that without the DBA role, the DBAs can’t do their job. Stalemate. So here they are, wondering if John has a solution.

Yes, John assures, the needs of both Debbie and Audrey can be met with an extra-cost option called Oracle Database Vault in Oracle Database 12c.

To be clear, Audrey responds, various kinds of users and activities need to be addressed. She identifies five distinct types of database users and their activities:

1. DBA. User who performs database infrastructure management, such as startup/shutdown, backup, and so on.
2. Account manager. User who performs activities such as creating users and changing passwords. Currently the DBAs do account management, and Audrey would like someone in IT security, not a DBA, to manage it.
3. Auditor. User who sets up the separation of duties and checks on the activities performed by various users. This should be performed by the IT Compliance department, insists Audrey.
4. Business schema user. User account that holds actual data tables and other objects that support the business.
5. Regular user. User account connected to the database from applications or a named human user allowed to see and manipulate data in schemas but not own them.

It’s vital, Audrey insists, that the privileges enjoyed by these users have no overlap. For example, the DBA user should be able to start and stop the database but not create users or select from any table in business schemas. Similarly, the account manager user should be able to create users but not start and stop the database and not select any data from a table (unless, of course, explicitly authorized to do so). The auditor user should be the only one to see the audit data but not be able to create users. In other words, everyone should be granted precisely the privileges they need in order to do their job and not a bit more—whether or not it’s their intention to use the privileges.

It’s a breeze to separate the users and activities with Oracle Database Vault, assures John, as he starts setting up a demo for his office visitors. First, he chooses users for various types of activities. For the first category, the DBA users such as SYS, SYSTEM, and other named DBAs are already present. To create user types 4 and 5, the business schema and regular users, he executes the script in setup.sql. The schema that holds all the bank user data is named ACME. The user connecting to the database to perform transactions is WEBAPP1. The SAVINGS table in the ACME schema stores the data on savings accounts. John breaks the remaining setup into nine steps, to cover situations where Oracle Database Vault may or may not be currently installed or configured and where it may be used in conventional and pluggable databases.

Step 1. For the other two user categories, account manager and auditor, John creates two special users for use by Oracle Database Vault, named DVACCMGR and DVADMIN, respectively. The DVADMIN user will manage the entire Oracle Database Vault setup.

-- As SYSDBA
create user dvadmin identified by dvadmin;
create user dvaccmgr identified by dvaccmgr;
grant create session to dvaccmgr, dvadmin;

Step 2. In case Oracle Database Vault hasn’t been configured, John checks by using the following SQL:

select * from dba_dv_status;
NAME                STATUS
——————————————————— ——————
DV_CONFIGURE_STATUS FALSE
DV_ENABLE_STATUS    FALSE

Step 3. The output confirms that the option hasn’t been configured. While installing some databases, the DBAs may have installed the Oracle Database Vault option but never configured it. For those databases where Oracle Database Vault was never even installed, John uses the following command to not only install the Oracle Database Vault option but also to configure it in one step.

dbca -silent -configureDatabase -sourceDB ACMEDB 
-addDBOption OMS,DV -olsConfiguration true -dvConfiguration true 
-dvUserName dvadmin -dvUserPassword dvadmin -dvAccountManagerName 
dvaccmgr -dvAccountManagerPassword dvaccmgr

John makes appropriate changes to the options such as the database name in the databases on which he runs this command. Here is the output:

Preparing to Configure Database
1% complete
3% complete
18% complete
Adding Oracle Label Security
19% complete
20% complete
21% complete
54% complete
Adding Oracle Database Vault
90% complete
Completing Database Configuration
100% complete
Look at the log file "C:\app\oracle\cfgtoollogs\dbca\ACMEDB\
ACMEDB.log" for further details.

The last line shows the location of the file where the details of the output will be recorded. If the option had already been installed in the database, John explains, the command would have exited without doing anything and the output would have referenced it.

Step 4. Some databases already had Oracle Database Vault installed but not configured. For them, John sets up the two special users to manage Oracle Database Vault and the DVADMIN and DVACCMGR user accounts, by running the following SQL as the SYS user:

begin
  dvsys.configure_dv (
    dvowner_uname   => 'dvadmin',
    dvacctmgr_uname => 'dvaccmgr'
  );
end;
/

Step 5. Next, John executes the utlrp.sql script in the rdbms/admin directory under Oracle Home as SYS.

SQL> @utlrp.sql 

Step 6. For those databases where Oracle Database Vault was installed but not configured, John connects as the DVADMIN user and enables Oracle Database Vault.

SQL> exec dbms_macadm.enable_dv

Step 7. John restarts each database.

Step 8. He confirms that the Oracle Database Vault option is configured and enabled, by executing the following SQL:

select * from dba_dv_status;
NAME                STATUS
——————————————————— ——————
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS    TRUE

Step 9. For multitenant databases, John executes all the previous steps on the root container (the container database). He executes steps 4, 5, and 6 on each pluggable database where Oracle Database Vault is needed, and he closes and reopens all the Oracle Database Vault–enabled pluggable databases.

With Oracle Database Vault enabled, John demonstrates the first effect to Audrey and Debbie. As the SYS user (John emphasizes that SYS should be used only when demonstrating the controls, not on a day-to-day basis), he tries to create a user called TEST:

create user test identified by test;
                              *
ERROR at line 1:
ORA-01031: insufficient privileges

The SYS user, which had all the privileges to create a new user earlier, fails with an ORA-1031 error. SYS can now perform typical database administration activities but not manage any user. To manage users, John logs in as the Oracle Database Vault user for account management—DVACCMGR—and executes this SQL:

SQL> conn dvaccmgr/dvaccmgr
SQL> create user test identified by test;
User created.

In addition, the DVACCMGR user can perform other user management functions such as changing passwords and granting CREATE SESSION privileges. However, DVACCMGR can’t select from any table or shut the database down. This DVACCMGR user is to be controlled by the account manager team and not the DBA team, so the DBAs can’t manage users—exactly what Audrey wanted. One requirement down, several more to go.

Users with the DBA role have the SELECT (and UPDATE, DELETE, or INSERT) ANY TABLE privilege and therefore can still can manipulate data in the SAVINGS table, Audrey observes, wondering if there is a way to prevent that. There is, answers John. It involves creating a special “protective ring” known as a realm in Oracle Database Vault and placing the table inside it. Only the Oracle Database Vault administrator user created earlier—DVADMIN—can create realms. John logs in as DVADMIN and executes the SQL shown in Listing 1 to create a realm named ACME Schema Realm. The AUDIT_OPTIONS parameter shows what level of auditing should be enabled for operations on the realm. John chooses to create audit trails only for failed attempts to control the amount of audit trail information generated. He mentions that he will explain the details of auditing later.

Code Listing 1: Creating a Realm

-- As DVADMIN
begin
 dbms_macadm.create_realm(
  realm_name    => 'ACME Schema Realm',
  description   => 'Realm for entire ACME schema',
  enabled       => dbms_macutl.g_yes,
  audit_options => dbms_macutl.g_realm_audit_fail,  
  realm_type    => 1
 ) ;
end;
/

Code Listing 2: Adding an Object to a Realm

-- As DVADMIN
begin
  dbms_macadm.add_object_to_realm (
    realm_name   => 'ACME Schema Realm',
    object_owner => 'ACME',
    object_name  => 'SAVINGS',
    object_type  => 'TABLE'
  );
end;
/

Next, John adds the SAVINGS table to the realm he just created, using the SQL shown in Listing 2, again as the DVADMIN user. After that, he logs in as the WEBAPP1 user and tries to select from the table:

-- As WEBAPP1
SQL> select * from acme.savings;
select * from acme.savings
                   *
ERROR at line 1:
ORA-01031: insufficient privileges

Debbie is perplexed. Referring to the setup.sql script, she points out that the WEBAPP1 user does indeed have SELECT privileges on the table, so the SELECT statement shouldn’t have failed. The reason is simple, explains John: the table is protected by the realm, which takes precedence over the typical Oracle Database privileges. John then adds the WEBAPP1 user as an authorized user of the realm, using the SQL shown in Listing 3.

Code Listing 3: Adding an Authorized User

begin
  dbms_macadm.add_auth_to_realm(
    realm_name    => 'ACME Schema Realm',
    grantee       => 'WEBAPP1',
    auth_options  => dbms_macutl.g_realm_auth_participant
  );
end;
/

With the WEBAPP1 user allowed inside the realm, the user’s typical Oracle Database privileges, such as SELECT on the table, will be honored. Because even SYSDBA privileges are not allowed inside the realm, the powerful SELECT ANY TABLE system privilege is useless for tables inside that realm, and hence its SELECT statement fails with an “insufficient privileges” error—exactly what Audrey wanted.

Audrey and Debbie are now visibly happy; they both got what they wanted. But they have more questions: How do we know the various realms, who are the authorized users, and so on? There are several data dictionary views, John explains. He points out a few important ones that are owned by a schema called DVSYS, which is created when Oracle Database Vault is activated:

• DVSYS.DBA_DV_REALM shows all the realms created.
• DVSYS.DBA_DV_REALM_OBJECT shows the objects inside a realm.
• DVSYS.DBA_DV_REALM_AUTH shows all the users authorized to access objects in realms.

Audrey reminds everyone that one very important requirement is to capture the history of changes and violations—audit trails—in such a way that the security administrator, but not the DBA user, will be able to view them. It’s possible, John continues, via two primary kinds of audit trails:

• Configuration audit. This audit trail records the changes to various configuration items such as realms created, users authorized, and objects placed in the realms. The trail is available as a DV$CONFIGURATION_AUDIT view in the DVSYS schema. This audit policy cannot be turned off. All changes to the Oracle Database Vault configuration will be captured.
• Enforcement audit. This audit trail records the activities made in the database related to Oracle Database Vault. It’s available via the DV$ENFORCEMENT_AUDIT view in the DVSYS schema. Listing 4 shows the SQL statement John uses to view the actions that caused a realm violation in this audit trail. Pointing to the first record in the output, he shows that the WEBAPP1 user issued the statement

  SELECT * FROM ACME.SAVINGS 

  on 17-DEC-15 at 08.11.31.123646 PM and received an ORA-1031 (insufficient privileges) error. This is when the WEBAPP1 user was not yet authorized for the realm and tried to select from the SAVINGS table. This view has several valuable columns, including OS_USERNAME, USERHOST, and INSTANCE_NUMBER, which show the operating system username that issued the statement, the client system name, and the Instance ID (for an Oracle Real Application Clusters [Oracle RAC] database), respectively.

  Code Listing 4: Checking an Audit Trail for Oracle Database Vault Realm Violations

  -- As DVADMIN
  select extended_timestamp, username, action_command, returncode
  from dvsys.dv$enforcement_audit
  where action_name = 'Realm Violation Audit'
  and action_object_name = 'ACME Schema Realm'
  order by 1,2,3
  /
  EXTENDED_TIMESTAMP                  USERNAME ACTION_COMMAND                      RETURNCODE
  ——————————————————————————————————— ———————— ——————————————————————————————————— ——————————
  17-DEC-15 08.11.31.123646 PM -05:00 WEBAPP1  SELECT * FROM ACME.SAVINGS                1031
  17-DEC-15 08.13.14.557721 PM -05:00 SYS      SELECT * FROM ACME.SAVINGS                1031
  17-DEC-15 08.19.56.999066 PM -05:00 ACME     SELECT * FROM SAVINGS                     1031
  17-DEC-15 08.20.09.400967 PM -05:00 ACME     SELECT * FROM ACME.SAVINGS                1031
  17-DEC-15 08.20.25.520461 PM -05:00 ACME     SELECT * FROM ACME.SAVINGS                1031
  17-DEC-15 08.49.22.008874 PM -05:00 WEBAPP2  SELECT * FROM ACME.SAVINGS                1031
  17-DEC-15 08.49.24.995166 PM -05:00 WEBAPP2  SELECT * FROM ACME.SAVINGS                1031
  17-DEC-15 08.53.41.351545 PM -05:00 WEBAPP2  SELECT COUNT(1) FROM ACME.SAVINGS         1031
  17-DEC-15 08.57.40.700805 PM -05:00 WEBAPP2  SELECT COUNT(1) FROM ACME.SAVINGS         1031
  17-DEC-15 09.33.18.166497 PM -05:00 SYS      AUDIT ALL ON ACME.SAVINGS                47401

These two audit trail views are accessible to the DVADMIN user alone. Therefore DBAs can neither examine nor alter the audit trails. This is also precisely what Audrey wanted to enforce.

One of the standing policies of the IT operation at Acme is to disallow any data definition language (DDL) operation during weekdays, to avoid performance issues as well as possible attacks against the database. Currently this policy is enforced through schema-level after-DDL triggers that simply raise an exception when DDL statements are executed during weekdays. But these triggers are owned by SYS, Audrey observes, so the DBAs can potentially manipulate the effect of these triggers, including even suppressing them. To reduce the possibility that a powerful account such as the DBA will cause an attack, and in the spirit of segregation of duties, Audrey wonders if it would be possible to enforce this policy not via SYS-owned triggers and therefore make it impossible for the policy to be influenced by the DBAs.

Code Listing 5: Setting Rules in Oracle Database Vault

-- As DVADMIN
-- First create a rule
begin
   dvsys.dbms_macadm.create_rule (
      rule_name => 'Weekday',
      rule_expr => 'to_char(sysdate,''DY'') not in 
             (''MON'',''TUE'',''WED'',''THU'',''FRI'')'
          );
end;
/
-- Then create a rule set 
begin
   dvsys.dbms_macadm.create_rule_set(
      rule_set_name => 'WEEKDAY_RULE_SET',
      description   => 'Weekday', 
      audit_options => dbms_macutl.g_ruleset_audit_fail + 
      enabled       => dbms_macutl.g_yes,
      eval_options  => dbms_macutl.g_ruleset_eval_any,
      fail_options  => dbms_macutl.g_ruleset_fail_silent,
      fail_message  => 'Security Doesn''t Allow This Operation on a Weekday', 
      fail_code        => 20001,
      handler_options  => dbms_macutl.g_ruleset_handler_off,
      handler          => ''
   );
   dvsys.dbms_macadm.add_rule_to_rule_set(
      rule_set_name => 'WEEKDAY_RULE_SET',
      rule_name     => 'Weekday',
      rule_order    => 1
   );
end;
/
-- Create a command rule that prevents the Truncate Table
-- command when the rule set evaluates to true
begin 
   dvsys.dbms_macadm.create_command_rule(
       command=> 'TRUNCATE TABLE', 
       rule_set_name => 'WEEKDAY_RULE_SET', 
       object_owner => 'ACME', 
       object_name => '%',
       enabled => 'Y'
   );
end;    
/

It is, assures John, with rules in Oracle Database Vault. John sets up the rule shown in Listing 5, using the following steps:

1. Create a rule that shows the allowable period. For the example, John specifies that the period is not a weekday.
2. Create a rule set. John specifies that when the rule is violated, the user should get an ORA-20001 error with this custom message: “Security Doesn’t Allow This Operation on a Weekday.”
3. Add the rule to the rule set.
4. Create a “command rule” in Oracle Database Vault that prevents a specific action. For the example, John chooses to prevent TRUNCATE TABLE on all tables of the ACME schema.

After setting up the rule, John logs in as the WEBAPP1 user. Today’s meeting is on a weekday, so he issues the following SQL:

SQL> truncate table acme.t1;
truncate table acme.t1
                    *
ERROR at line 1:
ORA-47306: 20001: Security Doesn't Allow This Operation on a Weekday

The operation fails, and the output message provides a pretty clear explanation. The enforcement of this rule is done through Oracle Database Vault, not triggers, so DBA users cannot alter the configuration or bypass this rule. Only the Oracle Database Vault administrators can change it. Rules can be defined for any type of check, John explains, such as checking for a specific IP address. Audrey couldn’t be happier.

In today’s business environment, most organizations must ensure that system users do not have more privileges than they need, such as DBAs being able to select sensitive business data or erase audit trails to hide their tracks. Using Oracle Database Vault, you can create an environment where the roles are truly segregated by the precise privileges assigned to each role. DBAs can’t see the business data, but they can perform the normal tasks they are supposed to do, such as start/stop and backup. Account managers can manage users but not actually see the data. Oracle Database Vault administrators can set up and check audit trails but can’t see the data. This allows the creation of a finely tuned environment with perfect checks and balances.

Next Steps LEARN more about Oracle Database Vault. READ Oracle Database Vault documentation.