mopdf.com

Administering Database UsersOracle Database Vault, optionally, separates the user administration task into a different role called DatabaseAccounts Management (DV ACCTMGR). DBAs can no longer create or manage database users by default. This isto eliminate ad hoc accounts creation and to prevent audit findings.Managing Users and RolesA Database Accounts Manager is a user who has been granted the DV ACCTMGR role. The first DatabaseAccounts Manager is created during Oracle Database Vault installation. As a best practice, the customer shouldcreate additional dedicated Database Accounts Managers and grant them the DV ACCTMGR role.The Database Accounts Manager can: create new users, grant the CONNECT role, manage existing users, andcreate and manage Oracle Database profiles. Note that, for security reasons, database accounts manager is notallowed to change the password for the Oracle Database Vault administrators (security administrators). Each OracleDatabase Vault administrator can change his/her own password only.Once users are created, a dedicated senior DBA account can grant them system privileges and roles as needed. Asenior DBA is a DBA who has been granted the necessary system privileges and roles with ADMIN OPTION. OracleDatabase Vault controls require the senior DBA to be authorized as OWNER to the Oracle Data Dictionary realmbefore granting other users system privileges and roles.Database roles can be protected by Oracle Database Vault realms. Therefore the grantor, in addition to havingadmin option on these roles, needs to be authorized as OWNER to the realm that protects these roles. Note thatdefault database roles are protected by the Oracle Data Dictionary realm.Oracle Database Vault related roles can only be granted by the Oracle Database Vault administrator’s account thatwas created during Oracle Database Vault installation. Similarly, the DV ACCTMGR role can only be granted bythe Database Accounts Manager account that was created during Oracle Database Vault installation.7 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

Managing Users using Oracle Enterprise ManagerDatabase users can be managed from Oracle Enterprise Manager. This provides the database accounts managerwith a nice user interface. The database accounts manager needs to have the Oracle Database VaultDV ACCTMGR role. In addition, a senior DBA should grant the database accounts manager the SELECT ANYDICTIONARY privilege. Once this is done, the database accounts manager can login to Oracle Enterprise Manager;click on Server tab, then on the Users link. This is where the user management screen is located. Note that thedatabase accounts manager can manage database users and profiles but cannot grant system privileges.Figure 7 Screen shows database accounts manager managing database users from Oracle Enterprise ManagerCreating and Modifying Database ObjectsA database user with the proper system privileges can create and modify database objects in his/her schema.However, if the user’s schema is protected by a realm, then the user needs to be authorized as owner in the realm.This allows the user to be able to execute Data Definition Language (DDL) SQL statements such as CREATETABLE and TRUNCATE TABLE on his/her own objects. Note that Data Manipulation Language (DML) SQLstatements such as SELECT and UPDATE are not affected in this case.Oracle Database Vault Command Rules can also affect the database user’s ability to create or modify databaseobjects. For example, if needed and for added security, a command rule can be created to prevent a user fromtruncating a table he/she owns. Oracle Database Vault Command Rules can be applied to almost any of the OracleDatabase SQL commands. A set of default Command Rules are created when Oracle Database Vault is installed.They are described in the Oracle Database Vault Administrator’s Guide.Database Backup and RecoveryThis section discusses backup and recovery in an Oracle Database with Oracle Database Vault. It covers OracleData Pump and security best practices for using Oracle Recovery Manager (RMAN).8 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

Oracle Data PumpUsing Oracle Data Pump in an Oracle Database with Oracle Database Vault requires additional operational controls.This prevents ad hoc export of data while allowing authorized users to do so.For example, let us assume the HR application is protected by a realm, and a DBA needs to export a table or theentire HR application. In this case, Oracle Database Vault operational controls are required and the DBA needs tobe authorized to export the particular table or the entire HR application. The following figure shows the OracleDatabase Vault administrator (SECURITY ADMIN) authorizing DBA JSMITH to export the HR.EMPLOYEES table:Figure 8 Authorize DBA JSMITH to do Oracle Data Pump export on HR.EMPLOYEESThe DBA can be authorized to export the entire HR application:Figure 9 Authorize DBA JSMITH to do Oracle Data Pump export on the entire HRNote: the DBA still needs the appropriate privileges such as EXP FULL DATABASE to be able to use Oracle DataPump. Refer to the Oracle Database Utilities manual and the Oracle Database Vault Administrator’s Guide for moreinformation.The DBA can be authorized to export the entire database. This would include Oracle Database Vault schemasDVSYS and DVF. The DBA, in this case, should be authorized to do so and should be granted the DV OWNERrole. The figure below shows how to authorize a DBA to export the entire database:Figure 10 Authorize DBA JSMITH to do Oracle Data Pump for the entire database9 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

After the DBA finishes the Oracle Data Pump export operation, the Oracle Database Vault administrator can revokethe authorization as follows:Figure 11 Revoke DBA JSMITH privilege to do Oracle Data Pump export on HR.EMPLOYEESFigure 12 Revoke DBA JSMITH privilege to do Oracle Data Pump export on HRFigure 13 Revoke DBA JSMITH privilege to do Oracle Data Pump export on the entire databaseAs a best practice, Oracle recommends encrypting Oracle Data Pump exports using Oracle Advanced Security. Formore information on Oracle Data Pump, please refer to the Oracle Database Utilities manual.10 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

Security Best Practices for using Oracle RMANOracle RMAN requires the DBA to have operating system access to do backups and to login to the database withSYSDBA privilege. As a security best practice, Oracle recommends creating dedicated operating system accountsfor DBAs who use Oracle RMAN. This enables customers to audit DBA operations using SYS AUDIT. It alsoalleviates the need for the DBA to login to the operating system as the Oracle software owner account. Thefollowing figure shows an audit record where DBA JSMITH has logged in to the database using Oracle RMAN asSYSDBA to do database backup.Figure 14 Audit record of DBA JSMITH When logged in to the database using Oracle RMAN as SYSDBAThe added benefit of having dedicated operating system accounts for DBAs is they would not be able to turn offOracle Database Vault protections.Another security best practice for using Oracle RMAN is to encrypt database backups with Oracle AdvancedSecurity. For more information on Oracle RMAN, please refer to the Oracle Backup and Recovery Reference.Flashback TableFlashback of a table to an earlier SCN or timestamp works as usual. However, if the table is protected by an OracleDatabase Vault realm, then the DBA needs to be authorized to the realm for the duration of the Flashback operation.In addition, Flashback dropped tables requires RECYCLEBIN to be enabled. Oracle Database Vault installationturns RECYCLEBIN off. This is because if a table protected by a realm is dropped, it gets moved to the recycle binwhere it is not protected. Therefore, unless the customer explicitly turns on RECYCLEBIN to be able toFLASHBACK dropped tables, Flashback dropped tables would not work. Note that this is being considered asenhancement for a future release.11 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

Managing Database Storage StructuresFor the DBA to be able to manage the database storage structures, the DBA is typically granted privileges such asCREATE TABLESPACE, DROP TABLESPACE, and ALTER TABLESPACE. In an Oracle Database Vaultenvironment, the DBA also needs to be authorized as PARTICIPANT or OWNER to the Oracle Data Dictionaryrealm.Database ReplicationIn an Oracle Database Vault environment, Oracle Database cloning works as before with no change. But thedatabase should be always cloned to an Oracle Home where Oracle Database Vault is enabled. This is to ensurethat Oracle Database Vault protections persist in the cloned database environment. For other replication activities,such as Streams and Data Guard, proper Oracle Database Vault authorizations should be granted at the sourcedatabase. The target databases should also be enabled with Oracle Database Vault for the protections to persistthere.Oracle Data GuardThere are three types of Oracle Data Guard: Data Guard Logical Standby, Data Guard Physical Standby, andOracle Active Data Guard. Data Guard Physical Standby and Oracle Active Data Guard are both supported withOracle Database Vault. Support note number 754065.1 provides step-by-step instructions on how to configureOracle Data Guard in an Oracle Database with Oracle Database Vault. Oracle Data Guard Logical Standby iscurrently not supported with Oracle Database Vault. Support for Oracle Data Guard Logical Standby is planned for afuture release.Oracle StreamsOracle Streams can replicate data from a realm-protected schema. However, Oracle Database Vault controlrequires the DV STREAMS ADMIN role to be granted to the DBA who configures Oracle Streams. This enablesthe tight management of Oracle Streams’ processes using Oracle Database Vault, but does not change the way aDBA would normally configure Oracle Streams.Figure 15 Grant DV STREAMS ADMIN role to a DBA to be able to configure Oracle StreamsDatabase TuningIn this section, we will go over some of the tools and techniques DBAs use to tune the Oracle Database such asEXPLAIN PLAN and ANALYZE TABLE and what Oracle Database Vault operational controls are required. The goalis to protect sensitive application data while enabling the DBA to tune the database.EXPLAIN PLANFor a DBA to be able to run EXPLAIN PLAN on a realm protected table, the PLAN TABLE needs to exist in aschema where the DBA has INSERT and SELECT privileges to it. The screen below shows how a DBA can run the12 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

EXPLAIN PLAN command on a realm-protected table successfully. In this case, the PLAN TABLE was created inthe DBA JSMITH schema. So, DBA JSMITH has INSERT and SELECT privileges to the PLAN TABLE.Figure 16 DBA running EXPLAIN PLAN successfully on a realm-protected tableIn this case, the DBA running EXPLAIN PLAN does not need to be authorized to the realm and would not haveaccess to realm-protected data.ANALYZE TABLEA DBA can run the ANALYZE TABLE command on a realm-protected table successfully without being authorized tothe realm. However, to be able to LIST CHAINED ROWS, the DBA needs to create the table CHAINED ROWS in aschema where he has INSERT and SELECT privileges. As shown in the screen below, DBA JSMITH runsANALYZE TABLE and lists the chained rows into the CHAINED ROWS table that he created in his own schema.So, DBA JSMITH has INSERT and SELECT privileges to the CHAINED ROWS table.Figure 17 DBA running ANALYZE TABLE successfully on a realm-protected tableIn this case, the DBA running ANALYZE TABLE does not need to be authorized to the realm and would not haveaccess to realm-protected data.Maintaining IndexesTo allow a DBA to maintain indexes for realm-protected tables, a separate realm needs to be created for all theirindex types: Index, Index Partition, and Indextype. The DBA needs to be authorized as OWNER to this realm. Thefollowing shows an example of how to do this.13 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

In our example, in addition to the “HR Application Protection Realm” that protects the entire HR schema, we create asecond realm we call “Index Maintenance realm for HR Application” that protects all HR objects of types Index,Index Partition, and INDEXTYPE. Then we authorize DBA JSMITH to this realm as OWNER.Figure 18 Indexes are separated in their own realm where DBAs can be authorized to maintain themNow, as we see from the screen below, the DBA can rebuild an index for a protected table.Figure 19 DBA is able to alter the HR index once authorized to the “Index Maintenance realm for HR Application”This allows the DBA to maintain indexes without having access to application data and gives control over who canmaintain indexes. A database role can also be authorized to the index realm to manage the maintenance of theapplication indexes.Database Patching and UpgradeA DBA can patch the database without turning off Oracle Database Vault protection. However, the Oracle DatabaseVault role DV PATCH ADMIN needs to be granted to the DBA before the DBA can patch the database.14 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

Figure 20 Grant DV PATCH ADMIN role to DBA JSMITH to patch the databaseThe DBA then logs in to the database as SYS with the SYSDBA privilege to patch the database. Oracle DatabaseVault protections continue to be effective during database patching.Figure 21 DBA can patch the database without having access to realm-protected application dataOnce patching is complete, the DV PATCH ADMIN role should be revoked from the DBA.Figure 22 Revoke DV PATCH ADMIN role from DBA after database patching is completed15 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

E-Business Suite patching can be done without turning off Oracle Database Vault protection. For more informationon patching E-Business Suite and other certified applications with Oracle Database Vault, refer to the relevantcertification notes on the Oracle Support site.Database upgrade, however, requires Oracle Database Vault protection to be turned off for the duration of theupgrade. During that process, Oracle recommends customers monitor all protected data using database audit.Once upgrade is done, Oracle Database Vault protection can be turned back on and database monitoring can goback to normal. Future release of Oracle Database will allow database upgrade without turning off Oracle DatabaseVault protection.Oracle Enterprise ManagerMost of the tasks in Oracle Enterprise Manager do not require Oracle Database Vault controls. In this section wewill cover adding Administrators to Oracle Enterprise Manager.Adding administrators to Oracle Enterprise ManagerAdding an administrator to Oracle Enterprise Manager Database Control involves grantingSELECT CATALOG ROLE to the new administrator. Oracle Data Dictionary realm protects this role and unless theuser performing the task is authorized as OWNER to the realm, an error will occur. In the screen below userSYSMAN tries to add DBA JSMITH as an administrator but gets an error.Figure 23 Screen shows an error when SYSMAN tries to add an administrator in Database Control16 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

In the Database Vault Administration screen below, SYSMAN is authorized as OWNER to the Oracle DataDictionary realm:Figure 24 SYSMAN is added as OWNER to the Oracle Data Dictionary realmNow, SYSMAN can login to Oracle Enterprise Manager add a new administrator to the Oracle Enterprise ManagerAdministrators:Figure 25 SYSMAN is able to add DBA JSMITH as an administrator to Oracle Enterprise Manager AdministratorsManaging Oracle Database VaultWith the increased sophistication of attacks on data, the need to put more operational controls on the database isgreater than ever. Given the fact that most customers have a small number of DBAs to manage their databases, it isvery important to keep database security related tasks separate in their own dedicated database accounts. Creatingdedicated database accounts to manage database security helps customers prevent privileged DBA accounts fromaccessing application data, restricts ad hoc database changes, and enforces controls over how, when and where17 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

application data can be accessed. Oracle Database Vault provides security benefits to customers even when theyhave a single DBA by:1.Preventing hackers from using privileged users’ accounts to steal application data2.Protecting database structures from unauthorized and/or harmful changes3.Enforcing controls over how, when and where application data can be accessed4.Securing existing database environments transparently and without any application changesIn this section, we address managing Oracle Database Vault in different customer scenarios. These scenarios showhow different customers, depending on how large they are and on the number of people available to manage theirdatabases, have integrated Oracle Database Vault into their IT operation. The following diagram shows an outlineof the main duties of a typical IT department and where Oracle Database Vault management and governance fit inthe overall IT structure.Figure 26 This diagram shows the main IT duties integrated with managing Oracle Database VaultAs we can see from the diagram above, the main IT duties are: Information Security Management, UserProvisioning, Database Administration and Database Security, and Development and QA. Given the fact that mostcustomers have a small IT department, most IT personnel have overlapping responsibilities. Let’s first look at eachof these IT duties:» Information Security Management:» Develop and communicate company-wide internal security policies» Conduct internal audits periodically in conjunction with security to ensure compliance with internal securitypolicies and industry regulations» Work with external auditors» Work with security to remedy any audit finding18 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

» User Provisioning:» Provision new users» Assign roles and responsibilities to new and existing users» De-provision users who are no longer with the company» Manage database accounts» Manage passwords for default accounts including default Oracle Database Vault Accounts Administrator andSecurity Administrator» Database Security:» Manage Oracle Database Vault: create realms, command rules, and factors and manage their authorizations» Review database security reports» Work with business owners to authorize exceptions and enable monitoring» Work closely with Information Security Management to conduct internal audits and to remedy any auditfinding» Database Administration:» Database backup» Database tuning» Database patching and upgrade» Database replication and high availability» Work closely with security and data owners to address exceptions and get emergency access» IT development and Quality Assurance (QA):» Develop and test applications» Maintain existing applications» Provide patches to DBAs to apply on production environments» Test applications and patches with Oracle Database VaultNow, let’s cover three customer scenarios for small, medium, and large IT departments and see how they manageOracle Database Vault.In small sized IT departments where security procedures are evolving, the same person might be required to handledifferent responsibilities. For example, the same IT person might be administering the database and doingdevelopment and QA, or this person might be managing security and administering the database at the same time.In this case, we recommend that customers create separate dedicated accounts for each responsibility. Forexample, if John Smith is required to manage security and administer the database at the same time, then JohnSmith should have two separate database accounts with two different passwords: DBA JSMITH for administeringthe database and SEC ADMIN JSMITH for managing database security. This is in addition to his dedicatedoperating system account. This helps the customer keep track of each account’s actions for compliance and auditingpurposes. This also prevents outside hackers from having access to application data if they manage to hijack aprivileged database account.In medium sized IT departments, a small number of people can be dedicated to security and they would typicallyhandle more than one responsibility. For example, people who handle security might also be responsible for userprovisioning. DBAs may be doing some development and system management. Developers might be doing19 ORACLE DATABASE VAULT DBA ADMINISTRATIVE BEST PRACTICES

database administration in addition to their development and testing activities. With Oracle Database Vault,customers

paper are: General Database Administration Tasks, Administering Database Users, Database Backup and Recovery, Database Replication, Database Tuning, Database Patching and Upgrade, and Oracle Enterprise Manager. For each of these topics, DBA best practices with Oracle Database Vault and security considerations are described.