6 Oracle Airlines Data Model Security

Provides information on Oracle Airlines Data Model security.

Oracle Airlines Data Model Security Overview

Provides an overview of Oracle Airlines Data Model security. When dealing with security, Oracle Airlines Data Model does not provide specific security features but assumes that you leverage the security features of its dependent Oracle products, including Oracle Database and WebLogic Server.

Oracle Airlines Data Model Security

Oracle Airlines Data Model has no specific security features enabled by default.

Oracle Airlines Data Model is a “normal” data warehouse implemented on top of an Oracle Database (although the data warehouse may only include industry information). Oracle Airlines Data Model is an addition to the Oracle Database and includes all the Oracle Database standard security features, or in some cases Oracle Advanced Security features that should be leveraged (as for any data warehouse containing sensitive data). Oracle Database provides privileged user controls, multi-factor authorization, transparent data encryption, auditing, configuration scanning, and SQL monitoring, and other industry leading security controls.

Read the following documents and apply the security solutions where relevant:

  • Oracle Database Security Guide
  • Oracle Database Advanced Security Guide

  • Oracle Fusion Middleware Securing a Production Environment for Oracle WebLogic Server (especially relevant when either WebLogic only or full OBIEE suite is used in combination with Oracle Airlines Data Model).

  • Oracle Application Server Security Guide (only if used)

  • Oracle Application Server Administrator's Guide (only if used)

All Oracle documentation is available from Oracle Help Center:

http://docs.oracle.com

There are several areas in Oracle Airlines Data Model where you must take specific care and note the security considerations, including:

  • When the sensitivity of the data Oracle Airlines Data Model deals with is an important consideration.

  • When the sensitivity of the access granted for some (power) users is an important consideration. For example, for anyone accessing critical application areas (for example campaign management).

Oracle Airlines Data Model Basic Security Considerations

The following principles are fundamental to using any application securely:

  • Keep software up to date. This includes using the latest product release and any patches that apply to it.

  • Limit privileges as much as possible. Users should be given only the access necessary to perform their work. User privileges should be reviewed periodically to determine relevance to current work requirements. This also means that no user should have direct access to OADM_SYS (at least for production).

  • Monitor system activity. Establish who should access which system components, and how often, and monitor those components. This means that each user should have their own user ID (no generic login). It also means that roles and data access should be restricted and monitored, especially when accessing sensitive or very sensitive data.

  • Install software securely. For example, use firewalls, secure protocols using TLS (SSL), and secure passwords.

  • Learn about and use the Oracle Airlines Data Model security features.

    Note:

    Using Oracle Airlines Data Model the Oracle Database DBA will leverage the standard or advanced security database options to protect Oracle Airlines Data Model content.

  • Use secure development practices. For example, take advantage of existing database security functionality instead of creating your own application security.

  • Keep up to date on security information. Oracle regularly issues security-related patch updates and security alerts. You must install all security patches as soon as possible. See the “Critical Patch Updates and Security Alerts” Web site:

    http://www.oracle.com/technetwork/topics/security/alerts-086861.html

Understanding the Oracle Airlines Data Model Environment

When planning your Oracle Airlines Data Model implementation, consider the following:

  • Which resources must be protected?

    • You must protect employee and customer personal data, such as credit-card numbers, social security numbers and login, PIN, and so on.

    • You must protect internal data, such as proprietary source code, custom development, and so on. This is not specific to Oracle Airlines Data Model, but you must make sure that any party able to access Oracle Airlines Data Model and its customer paying documentation should have an NDA in place, to avoid being liable for dispatch or reuse of information by 3rd party.

    • You must protect system components from being disabled by external attacks or intentional system overloads. This is not specific to Oracle Airlines Data Model.

  • Who Are You Protecting Data From?

    The list of sources of possible threats includes a wide range of people and organizations. Threats depend on the data stored. Individuals include employees or an external hacker.

    For example, you must protect your subscribers’ data from other subscribers, but someone in your organization might need to access that data to manage it. You should analyze your workflows to determine who needs access to the data and what for; it is possible that a system administrator can manage your system components without needing to access the system data. Also, hackers continue to target privileged accounts inside and outside of databases.

    The following list provides some examples of threats and the possible financial impact:

    • Insider: typically an employee or a contractor, who tries to abuse the normal rights for “revenge” or for their own benefit. In this case the possible damage is highly dependent on the breadth of access the person has and how malevolent the actions are. The damage can range from self adjustment (of one’s own bills) up to destruction or re-selling of data.

    • Individual: individual persons outside the organization, not including employees or contractors, that try to get into the organization’s network to test themselves or to test the security for “fun”. These threats can cause significant damage.

    • Criminal organizations: such organizations target the organization for subscriber data, in particular bank account and credit card data. They may also target the network to take control and blackmail the organization.

    • State-like organizations: their aim is usually to gather information on their own target(s). organization data is a treasure if they want this data and they do not or cannot go through a standard judicial process to obtain the data.

    • Research organizations: these organizations try to test security and look for breaches. Their goal is often to publish their finding but such organizations sometimes notify the organization and provide the opportunity (time) to change the system before they publish security vulnerabilities. Such threats, when identified may have a small financial impact if the vulnerability stays under the organization’s control.

    The organization has to protect Oracle Airlines Data Model with the highest security standard possible with respect to the possible wide range of threats.

  • What happens if protections on a strategic resource fail?

    In some cases, a fault in your security scheme is nothing more than an inconvenience. In other cases, a fault might cause great damage to the organization and to your customers. Understanding the security ramifications of each resource allows you to properly protect it.

Oracle Airlines Data Model is an option of the Oracle Database and because it is a physicalized Enterprise Data Warehouse, the security principles of Oracle Airlines Data Model follow the standard security principles valid for any Oracle Database. Additionally, you must apply the security policies associated with the applicable reporting tools. By default, Oracle Airlines Data Model uses the repository and reports with Oracle Business Intelligence Suite Extended Edition(OBIEE), which leverages WebLogic server. Hence, the security standards applied to the Oracle Airlines Data Model reporting layer should be set up as security for WebLogic server (and OBIEE).

Also, security features must protect the servers and their operating systems on which the database for Oracle Airlines Data Model runs, as well as any interfaces and configurations for supplying and accessing the systems or for reports. In particular:

  • ETL/ELT servers and ETL GUI (staging area and more)

  • Direct feed to Oracle Airlines Data Model (whether with GUI or not)

  • Access to the database management (DBA GUI)

  • Access to/from Oracle Airlines Data Model

    • For Development Tools: SQL developer, SQL Data Modeler

    • For research and discovery: Oracle Data Mining GUI, Oracle Data Miner (including SNA), Oracle R

    • For cubes management: Oracle Analytical Workspace Manager

    • External feeds to other applications (from Oracle Airlines Data Model directly, for example through a trigger)

    • Configuration Files

  • Oracle Airlines Data Model Content

    • Users Traceability and Audit-ability (especially for sensitive data)

    • Encryption and masking (especially for sensitive data and the backups for sensitive data)

    • User access rights (through roles)

  • Backup and Disaster Recovery Systems (and high availability architecture).

  • BI systems and applications on top of Oracle Airlines Data Model, leveraging its data

    • OBIEE

    • Any other system (for example a campaign management tool)

Depending on which part of Oracle Airlines Data Model has been implemented and filled with data, some or all of the security steps should be applied. Additionally, any customization must be protected as part of the global security processes.

Recommended Deployment Configurations

Typically, as for any data warehouse, there are several environments on which you install Oracle Airlines Data Model:

  • The Test and Development Environment (R&D): this area is for testing, development, research, and unit testing with a subset of data. The test and development environment is typically where most customization takes place before being moved to other environments. The test and development area usually contains a full end-to-end implementation (from staging area up to BI reporting). Despite the test and development apparently temporary presence, this environment should be kept available for debugging and for work on extensions to Oracle Airlines Data Model (this area could be reduced when there are no planned project extensions).

    Note:

    The staging, data warehouse, and BI server do not need to be installed on the same platform and they can have their own test or pre-production and production environment.

  • The Pre-production Environment: this area is typically provided for scalability testing (load and performance) and global integration testing. The pre-production environment should contain a representative subset of the data set (for example, containing a few months of history), for a relevant test and for final user acceptance.

  • The Production Environment: this area is the final end user environment, providing the required performance and capacity to store the complete data set and any required history.

All these environments must be protected equally since they likely will contain copies of similar sensitive data, whatever subset of data you use for the different testing and development or pre-production environments. Additionally, the backup of each of these environments, if any, should be protected as if it was production.

Architecture Analysis for Security

Describes areas in Oracle Airlines Data Model that are potential security risks, to first be aware of them and second, to secure them.

Security Boundaries of Trust

Within each boundary shown in the following figure, assume that whoever accesses the data can be trusted (with “recognized” users or roles). Outside of those boundaries, anyone is a potential threat (this is critical from a security analysis perspective).

The following figure identifies points of interactions at risk, trust boundaries, through which an attack could occur.

Figure 6-1 Security Boundaries of Trust

Description of Figure 6-1 follows
Description of "Figure 6-1 Security Boundaries of Trust"

  1. ETL GUI: Whatever tools and GUI you use for the Extraction, Transform, and Load tools that manage the data movement and transformation from the source to staging area, there is clearly a risk of attack:

    • Because the tool has access to the raw data source as well as all its transformation rules (Database login data, data source visibility, metadata and transformation rules, mapping).

    • Because someone could change the rules (and place unexpected code into Oracle Airlines Data Model).

    Note:

    If you use Big Data this is also considered part of the “Staging area” even if it also performs operational analysis and reporting. Adding a Big Data environment into the architecture thus includes additional interfaces, and additional boundaries of trust that need to be considered for security and analyzed.

  2. SQL Developer and Oracle Data Modeler GUI: (or any GUI that can directly access the database of either the staging area or the Oracle Airlines Data Model area – hence two accesses at risk). The access at this boundary must to be restricted (leveraging roles) and monitored (traceability). Additionally, the access string must be protected (do not allow the tnsnames.ora to be saved with an unencryption schema password) and the schema password should never be saved with the applications.

  3. Data Source Feed: When some data from the source system could cause a security breach (through unexpected code or corrupted data, intentional or not, that could break the process). The best way to protect from such an attack is to set up a data profiling check (for example with ODI Data Profiling option or manually by verifying that the input data have the content you expect). Additionally, the schema name and password for the connection should be protected as for 1 and 2 (if not done in the respective GUI).

  4. Direct Data Source Feed: This case is similar to case 3, but a direct data source feed assumes a real-time or near real-time direct feed to Oracle Airlines Data Model (to the foundation or analytic layer). The protection should however be similar to 3.

  5. DBA GUI Access: Due to its rights, the DBA is clearly a role with risk, whatever GUI this person uses (Oracle Enterprise Manager, or any other tools of choice),

    See Also:

    Leverage the suggestions in Oracle Database Security Guide to split the various DBA roles and data access

  6. DBA Backup Process and Access GUI: This is similar to 5, but for the backup process. Refer to the Oracle Database Security Guide for this area, in particular for backup encryption and splitting the backup role from the standard DBA role.

  7. Backup Process: The backup process must be as secure as the Oracle database it is supposed to backup. Configuration files must be encrypted to avoid malevolent use (for example running an unwanted “Restore” or changing the target for a backup). Refer to the security guide of the backup software for such risk analysis.

  8. Oracle Advanced Analytics Mining GUI: (or any other mining tools GUI): Power users that have access to mining normally also have access to most of the data in clear text (to be able to run the mining and to build the models). They should be treated correspondingly from a data access perspective with much care, as their power, unless explicitly limited within their role, is similar to the DBA. Such power users may also use significant resources (for example CPUs) to run their models, possibly unwillingly deploying a self-developed model to the full customer base on a production system at an unplanned time, making the data warehouse slow down such that it becomes unusable. On top of a secure access to the database (with hopefully encrypted configuration files), the GUI for mining users should be protected (and access traced), as for the DBA GUI (5).

  9. Oracle Analytics Workspace Manager GUI: (or any GUI accessing the database OLAP cubes or creating M/R/HOLAP cubes in Oracle Airlines Data Model or accessing data from Oracle Airlines Data Model): Security risk and mitigations should be as for the mining GUI (7).

  10. WebLogic or BI Server Access: (to Oracle Airlines Data Model): The BI Server that serves as “BI windows” to Oracle Airlines Data Model end users is a boundary of trust, independent of the BI tool used (including WebLogic or other tools) The configuration file and the connection to access Oracle Airlines Data Model should be protected according to the security guidelines for the corresponding tool.

    See Also:

    For more information for WebLogic guidelines see Oracle Fusion Middleware Securing a Production Environment for Oracle WebLogic Server

  11. External Data Feed: (outgoing): When information is directly picked up from Oracle Airlines Data Model database by an external application (campaign management or monitoring tools for example, to obtain the churn probability and to generate the best offer for a customer) or when Oracle Airlines Data Model itself is setup to export on a regular basis directly to a file, using a database trigger or Spool scripts for example, the data exported should be correspondingly:

    • Anonymized (as part of the export process) or encrypted (before being transmitted) according to the sensitivity of the content.

    • Stored in a place that is only accessible for the purpose required, and not changeable, possibly encrypted or unreadable except by the targeted application.

    • In a format and with the data expected (quality check).

    • Removed after use by the target application.

    Check the appropriate database security guide to protect the external feed and the security guide of the application when direct load or query to the database.

  12. OBIEE Administrator GUI: The administrator is usually accessing the BI tools and manages the access rights and data accessed as well as the way they are organized and presented to the end-users. With OBIEE, the repository (rpd – default in Oracle Airlines Data Model is oadm.rpd) and the webcat files are the most important to protect.

    The OBIEE admin should remove or restrict access to OADM_SAMPLE after it is no longer required, change the default password of OADM RPD, and define all the users (individually), associated with database users. Additionally, the OBIEE administrator should define various groups with limited rights (at OBIEE level) and associate each user with one of those groups. It is important to implement a 2 level security:.

    • Database level (I can only access the data I am entitled to – independently of what rights I am given at BI level)

    • OBIEE level (I can only access the reports and create new reports or leverage OBIEE features on data I am entitled to see and/or use).

    See Also:

    For more information for WebLogic guidelines see Oracle Fusion Middleware Securing a Production Environment for Oracle WebLogic Server

  13. OBIEE End-Users: The end-users usually access their data through their browser on their mobile device or from their desktop.

    See Also:

    For more information for WebLogic guidelines see Oracle Fusion Middleware Securing a Production Environment for Oracle WebLogic Server

  14. WebLogic Outside or Other Incoming Feed: This corresponds to the loading of external data to the WebLogic server to combine them with Oracle Airlines Data Model output. It is proper to the WebLogic server and should be handled through Oracle Fusion Middleware Securing a Production Environment for Oracle WebLogic Server instructions. An example of load is an end-users loading an Excel file with 3rd party data (weather data, market share data, and so on) to enrich its own reports. If this file is corrupted or prepared with a malevolent intention, it can represent a risk for the complete architecture.

  15. WebLogic Outside or Other (outgoing) Feeds: This is exported from WebLogic server to the external world (as MS object or flat file, SMS or email, web agent, and so on). This can become particularly sensitive depending on the information sent or its form of management. Such external feeds must be handled as referred to in Oracle Fusion Middleware Securing a Production Environment for Oracle WebLogic Server.

  16. BI Server Backup: This is similar to the Oracle Airlines Data Model backup, the BI server backup is to be secured in a similar way as for the Database.

    See Also:

    For more information for WebLogic guidelines see Oracle Fusion Middleware Securing a Production Environment for Oracle WebLogic Server to securely handle backups and see the Oracle Database Security Guide

  17. Application GUI: (that manages the access and data loaded directly from the Oracle Airlines Data Model database): Such applications should be similarly protected (encrypted where possible), monitored and limited (from a data access) as for a normal end-user. Avoid general application (generic) user accounts unless the information is not accessible by anyone else but the application itself (no external communications – internal processes only). Always use the “user ID” that logs into the application and do not use a generic user account.

Security by Component

Outlines security by component. To obtain the highest level of security you must leverage all the possible security options you can within your budget and resources. The default security provided is better than no security, but you should provide enough resources to obtain the highest possible security standard.

Security Component by Component

  • Operating System Security

    This area is not Oracle Airlines Data Model specific. For example, if the Linux OS is used, see the following documents:

    • Guide to the Secure Configuration of Red Hat Enterprise Linux 5

    • Hardening Tips for the Red Hat Enterprise Linux 5

  • Oracle Database Security and WebLogic Server Security

    Not Oracle Airlines Data Model specific.

Performing a Secure Oracle Airlines Data Model Installation

Presents information to plan for a secure Oracle Airlines Data Model installation. Outlines the steps required to perform a secure installation.

Pre-Installation Configuration

On the Oracle Database, after installation of partitioning, Database OLAP, and mining, the simplest recommendation is first to change all the default passwords (in particular of SYS and SYSTEM) and lock all accounts and schema that are not used.

Installing Oracle Airlines Data Model Securely

You can perform a custom installation or a typical installation. Perform a custom installation to avoid installing options and products you do not need. If you perform a typical installation, remove or disable features that you do not need after the installation.

On the Oracle Database, during the installation process, first unlock and set passwords for OADM_SYS (and OADM_SAMPLE if installed). For example, use ALTER USER IDENTIFIED BY SQL command.

The following are areas with default passwords:

  • OADM_SYS

  • OADM_SAMPLE (to be removed at least for pre-production and production).

  • OADM_USER

  • RPD Admin default password

  • OADM _REPORT user OBIEE default password

Define the list of people and applications that must access Oracle Airlines Data Model, then determine the data they require the constraints to their access (the complete data set or only a subset), and for each type of profile define a role at the database level with corresponding privileges. (CREATE ROLE command, and GRANT ROLE, PRIVILEGEs TO ROLE / USER command).

Create as many schemas as you need for each end-user that accesses Oracle Airlines Data Model and specify the correct roles to limit access as required.

When you use OBIEE for reporting, change the default passwords of the RPD (see OBIEE Admin Guide for this) and change also the user password. Create a user for each end-user, with groups, rights, and roles as you did for the Database but at the OBIEE level.

Post-Installation Configuration

There is nothing specific for this area except that you must encrypt any custom scripts that you create and use with Oracle Airlines Data Model.

Implementing Data Model Security

Explains the Oracle Airlines Data Model security features for tables and entities. This topic lists security sensitivity at various levels for Oracle Airlines Data Model entities.

Implementing Data Model Security

Oracle Airlines Data Model is an option of the database and it does not contain product specific security features. However to secure Oracle Airlines Data Model you must apply the rules of a secured data warehouse as explained in the Oracle Database Security Guide. The security rules must be applied and enforced.

The Oracle Airlines Data Model includes entities and some entities must be protected at various levels of security based on the sensitivity of the data they store.

The range for secure entities in the listing ranges from 1 (very sensitive) to 5 (public data).

Table 6-1 Entity Sensitivity Levels

Level Type of data Actions recommended

1 Highly sensitive

payment data, and so on.

Encrypt by default (at least the sensitive fields). Audit, do not allow backup (without encryption), limit roles accessing them

2 Sensitive

address, age, resource, and so on.

Mask by default (at least sensitive fields), audit from time to time, limit roles accessing them

3 Somewhat sensitive

No value

Limit access

4 Not sensitive

No value

No value

5 Public

Publicly available data (weather, and so on.)

No value

Note:

By default, any information in Oracle Airlines Data Model is somewhat sensitive. Even if no data is stored in an entity, the entity should be protected from access.

Security Maintenance, Monitoring and Control

Provides information on controls for maintaining security for Oracle Business Intelligence Suite Extended Edition (OBIEE) and the Oracle Database. You must ensure that you remove or at least lock all unused users at the OBIEE and database level, and perform regular monitoring of user permissions and usage.

Users and Schema Management at Database and OBIEE Level

Remove (or at least lock) all unused users from both the OBIEE and the database level.

Monitor and automatically lock users without activity for the last N or 60 days (on OBIEE and on Oracle Airlines Data Model). Providing a regular schedule for locking or resetting for the password for accounts every 120 days, to force a password change, is also a good practice.

Security Considerations for Developers

Provides information for developers about how to create secure applications for Oracle Airlines Data Model, and how to extend Oracle Airlines Data Model without compromising security.

Security Considerations for Developers

Oracle Airlines Data Model developers work on the following levels:

  • Database level: In Oracle Airlines Data Model itself, typically for extensions and Customization, from tables to Intra-ETLs up to OLAP cubes or Mining models.

  • ETL level: In the staging area typically, to move data from source to Oracle Airlines Data Model, while taking care of quality (correctness of content and quantity, timely arrival, meaningfulness for the scope), coherence, latency (or time coherence) and correctness (of mapping to Oracle Airlines Data Model).

  • OBIEE level: Development (extensions, customizations) of dashboards, reports, alerts, web agents, guided analytics, users and user interfaces.

Each developer should work only on the test environment (except those who test performance in a pre-production environment). Developers should have access to a limited extract of the data that only concerns their scope of work. If possible, provide a dummy set of sample data, as similar to a real data set as possible, for unit testing before testing the system with real data.

If it is not possible to use sample data, in most cases, you must encrypt sensitive data on your site to meet a regulatory compliance for customer and payment information. Also, consider encrypting the source files or input files if sensitive information is stored there.

Dealing with Data Privacy, Data Retention, and other Data Related Rules and Laws

Describes regulations and covers dealing with data privacy, other data related rules and laws.

Dealing with Data Privacy, Data Retention, and other Data Related Rules and Laws

Each country has its own regulations with respect to data privacy, data allowed and forbidden to be stored and used, and the time for which it must be stored and available for criminal or state inquiry.

For example, in Europe, you must consider the laws at least for data retention, that includes:

  • DIRECTIVE 2006/24/EC OF THE EUROPEAN PARLIAMENT AND OF THE COUNCIL - 15 March 2006, which amended the Directive 2002/58/EC for Data privacy of Electronic communications.

These items specify rules for data for quality, security, and confidentiality.

Another concrete example: some fields need to be removed from Oracle Airlines Data Model(or not used at least) in Europe: ETHNICITY field in PARTY is not allowed. The Opt-in and Opt out option of customers must be leveraged by campaign management systems. The Op-in and Opt out options are available in the Social Network Analytics option of Oracle Airlines Data Model.

Because it is locally specific, you should review the Oracle Airlines Data Model content for the project and data you are considering using with your data protection staff (or review the law currently applicable).

Database Vault on Oracle Airlines Data Model

Describes how to use Database Vault with Oracle Airlines Data Model.

DBA Privileges

It is known that the user who has DBA privilege can see data in OADM_SYS schema, Oracle Database Vault provides powerful security controls to help protect application data from unauthorized access, and comply with privacy and regulatory requirements.

Registering Oracle Database Vault with an Oracle Database

Describes how to register Oracle Database Vault from SQL*Plus in a non-multitenant environment.

Registering Oracle Database Vault with an Oracle Database

You can register Oracle Database Vault from SQL*Plus in a non-multitenant environment.

  1. Log into the database instance as user SYS with the SYSDBA administrative privilege. 
    sqlplus sys as sysdba
Enter password: password
  2. As user SYS with the SYSDBA administrative privilege, configure the Database Vault user accounts. 
    SQL>CREATE USER dbv_owner IDENTIFIED BY dbv_owner;
SQL>CREATE USER dbv_acctmgr IDENTIFIED BY dbv_acctmgr;
SQL>GRANT CREATE SESSION TO dbv_owner, dbv_acctmgr;
SQL>BEGIN
DVSYS.CONFIGURE_DV (
dvowner_uname => 'dbv_owner',
dvacctmgr_uname => 'dbv_acctmgr');
END;
/
  3. Run the utlrp.sql script to recompile invalidated objects. If the script gives you any instructions, then follow them, and then run the script again. If the script terminates abnormally without giving any instructions, then run the script again.
    SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
  4. Connect as the Database Vault Owner user that you just configured. 
    SQ>CONNECT dbv_owner

    Enter password: password

  5. Enable Oracle Database Vault.
    SQL>EXEC DBMS_MACADM.ENABLE_DV;
  6. Connect with the SYSDBA administrative privilege.
    SQ>CONNECT / AS SYSDBA
  7. Restart the database. 
    SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP

Verifying That Oracle Database Vault Is Configured and Enabled

You can query the V$OPTION dynamic view to verify if Oracle Database Vault is configured and enabled.

To find if Oracle Database Vault is configured and enabled, run the following query, which should show the VALUE setting as TRUE.
  1. Query the V$OPTION dynamic view as follows: 
    SQL>SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';
  2. To check if Oracle Label Security is enabled, query V$OPTION as follows: 
    SQL>SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';

Logging into Oracle Database Vault

From Oracle Enterprise Manager Cloud Control (Cloud Control), you can use the Oracle Database Vault pages to administer and monitor Database Vault-protected databases from a centralized console, automate alerts, view Database Vault reports, and propagate Database Vault policies to other Database Vault-protected databases.
  1. Ensure that you have configured the Cloud Control target databases that you plan to use with Database Vault.
  2. If necessary, register Oracle Database Vault.
  3. Start Cloud Control.

    For example:

    https://myserver.example.com:7799/em

  4. Log into Cloud Control as user SYSMAN.
  5. In the Cloud Control home page, from the Targets menu, select Databases.
  6. In the Databases page, select the link for the Oracle Database Vault-protected database to which you want to connect. The Database home page appears.
  7. From the Security menu, select Database Vault.The Database Login page appears.
  8. Enter the following information:

    Username: Enter the name of a of a user who has been granted the appropriate Oracle Database Vault role(dbv_owner).

    The Database Vault home page appears.

Securing OADM_SYS Schema from DBA Access

Describes how to secure the OADM_SYS Schema.

  1. Log On as SYSTEM to access the OADM_SYS Schema.
  2. Create a Realm.
    1. Log into Oracle Database Vault Administrator from Cloud Control as a user who has been granted the DV_OWNERor DV_ADMIN role and the SELECT ANY DICTIONARY privilege.
      "Logging into Oracle Database Vault" on page 3-7 explains how to log in.
    2. In the Administration page, under Database Vault Components, click Realms. (It should be selected by default.)
    3. In the Realms page of Oracle Database Vault Administrator, click Create.
    4. In the Create Realm page, under General, enter OADM Apps after Name.
    5. In the Description field, enter Realm to protect the HR schema.
    6. After Status, ensure that Enabled is selected so that the realm can be used.
    7. Under Audit Options, ensure that Audit On Failure is selected so that you can create an audit trial later on.
    8. Click Next to display the Realm secured objects page.
    9. Click the Add button and in the Add Secured Object dialog box, enter the following information:
      Owner: Enter OADM_SYS to select the OADM_SYS schema.

      Object Type: Enter TABLE.

      Object Name: Enter DWB_ACCT_PYMT.

    10. Click OK.
      The OADM_SYS.DWB_ACCT_PYMT table is added to the Create Realm : Realm Secured Objects page
    11. Click Done, and then click Finish.
  3. Create the OADM Manager User Account.
    At this stage, there are no database accounts or roles authorized to access or otherwise manipulate the database objects the realm protects. So, the next step is to authorize database accounts or database roles so that they can have access to the schemas within the realm. Create the OADM_MGR user account.
    1. .In SQL*Plus, connect as the Database Vault Account Manager, who has the DV_ACCTMGR role, and create the local user OADM_MGR. 
      SQL>conn dbv_acctmgr/dbv_acctmgr;
SQL>GRANT CREATE SESSION TO OADM_MGR identified by OADM_MGR;
    2. Connect as SYS with the SYSDBA privilege, and then grant SEBASTIAN the following additional privilege: 
      SQL>conn / as sysdba;
SQL>GRANT READ ANY TABLE TO OADM_MGR;
  4. Create an Authorization for the Realm.
    At this stage, even though OADM_MGR has the SELECT ANY TABLE privilege, he cannot select from the OADM_SYS.DWB_ACCT_PYMT table because it is protected by a realm.
    Next, authorize user OADM_MGR to have access to the OADM Apps realm as follows:
    1. In the Realms page of Database Vault Administrator, select OADM Apps in the list of realms, and then click Edit.
    2. Click Next until you reach the Realm authorizations page.
    3. Click Addand then enter the following information in the Add Authorizations dialog box:

      • Realm Authorization Grantee: Enter OADM_MGR.

      • Realm Authorization Type: Select Participant from the list.

      • Realm Authorization Ruleset: Leave this field blank.

    4. Click OK.
    5. Click Done, and then Finish.
  5. Test the Realm.
    To test the realm, access the DWB_ACCT_PYMT table as a user other than OADM_SYS. The SYSTEM account normally has access to all objects in the OADM schema, but now that you have safeguarded the DWB_ACCT_PYMT table with Oracle Database Vault, this is no longer the case. Connect as SYSTEM, and then access the account balance information in the DWB_ACCT_PYMT table again:
    SQL>CONNECT SYSTEM 
Enter password: password

    The following output should appear:

    Error at line 1:

    ORA-01031: insufficient privileges

    SQL>CONNECT OADM_MGR

    Enter password: password

    Query data in DWB_ACCT_PYMT,data appears

Data Masking on Oracle Airlines Data Model

Describes how to use Data Masking with Oracle Airlines Data Model.

Data Masking Overview

When performing real-world testing, there is the risk of exposing sensitive data to non-production users in a test environment. The Data Masking feature of the Enterprise Manager for Oracle Database Plug-in enables you to securely manage test data.

  • Using Data Masking
    Describes steps for using data masking with Oracle Airlines Data Model.

Using Data Masking

Describes steps for using data masking with Oracle Airlines Data Model.

Assume there is sensitive data showing bank card number information where masking is required before sending the data to a test environment.
  1. Go to EM cloud control, Security->Application Data Model.
  2. Create Application Data Model.
  3. Enter Application Data Model name and select source database.
  4. Enter the database credentials and select OADM_SYS schema.
  5. Enter the job name.
  6. Job submitted successfully.
  7. Job done.
  8. Edit the Application Data Model.
  9. Add sensitive column.
  10. Select column BNK_CARD_NBR in table DWR_BNK_CARD.
  11. Sensitive column added.
  12. Set sensitive column type.
  13. Set sensitive column type to ‘CREDIT_CARD_NUMBER’.
  14. Sensitive column type updated.
  15. Go to Security->Data Masking and Subsetting->Data Masking Definitions.
  16. Data masking definition.
  17. Create data masking definition.
  18. Add column BNK_CARD_NBR.
  19. Set format entry to random number.
  20. Set the random number from 100000000 to 999999999.
  21. Data masking definition created.
  22. Generate data masking script.
  23. Schedule data masking script job.
  24. Generating masking script.
  25. Masking script generating job succeeded.
  26. Masking script generated.
  27. Schedule data masking job.
  28. Data masking job submitted successfully.
  29. Data masking job succeeded.
  30. Now, check BNK_CARD_NBR, it is masked.

Transparent Data Encryption in Oracle Airlines Data Model

Describes how to configure Transparent Data Encryption (TDE), and demonstrates using TDE (making one encrypted column and one encrypted tablespace).

Transparent Data Encryption (TDE) stops would-be attackers from bypassing the database and reading sensitive information from storage by enforcing data-at-rest encryption in the database layer. Applications and users authenticated to the database continue to have access to application data transparently (no application code or configuration changes are required), while attacks from OS users attempting to read sensitive data from tablespace files and attacks from thieves attempting to read information from acquired disks or backups are denied access to the clear text data.

Configuring a Software Keystore

Describes the steps required to configure a Software Keystore.

Steps show how to configure TDE, and demonstrate after using TDE, making one encrypted column and one encrypted tablespace, how Oracle Airlines Data Model can work transparently.

  1. Set the Software Keystore Location in the sqlnet.ora file. 
    NAMES.DIRECTORY_PATH (TNSNAME, EXCONNECT)
  ERYPTION_WALLET_LOCATION =
    (SOURCE=(METHOD=FILE)(MTHOD_DATA=
     (DIRECTORY=/biaora/home/app/biaora/admin/orcl12102/wallet)))
  2. Create the Software Keystore: 
    SQL>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/biaora/home/app/biaora/admin/orcl12102/wallet’ IDENTIFIED BY "password";

keystore altered
  3. Open the Software Keystore: 
    SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "password";

keystore altered

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "password" with backup;

Demonstration of Oracle Airlines Data Model Working with TDE

Demonstrates TDE with one IETL package (PKG_DWD_BKG_FCT) as a TDE example.

  1. Query that shows dependent tables for PKG_DWD_BKG_FCT.

    Figure 6-2 Query Listing Tables for PCK_DWD_BKG_FCT

    Description of Figure 6-2 follows
    Description of "Figure 6-2 Query Listing Tables for PCK_DWD_BKG_FCT"
  2. Query shows part of data in table DWB_BKG. including the column FLT_NBR that is encrypted in a later step.

    Figure 6-3 Shows Query for DWB_BKG with FLT_NBR Column

    Description of Figure 6-3 follows
    Description of "Figure 6-3 Shows Query for DWB_BKG with FLT_NBR Column"
  3. Query shows data and current tablespace of table DWR_FLT, later we will move it to one encrypted tablespace.
  4. Query shows encrypted columns.

    Figure 6-5 Query to Show Encrypted Columns

    Description of Figure 6-5 follows
    Description of "Figure 6-5 Query to Show Encrypted Columns"
  5. Showing current encryption property for each tablespace.

    Figure 6-6 Showing Encryption Property for Tablespaces

    Description of Figure 6-6 follows
    Description of "Figure 6-6 Showing Encryption Property for Tablespaces"
  6. Encrypt column FLT_NBR.

    Figure 6-7 Alter DWB_BKG and Encrypt Column FLT_NBR

    Description of Figure 6-7 follows
    Description of "Figure 6-7 Alter DWB_BKG and Encrypt Column FLT_NBR"
  7. Now, column FLT_NBR is encrypted.

    Figure 6-8 List the FLT_NBR Column in DWB_BKG Encrypted

    Description of Figure 6-8 follows
    Description of "Figure 6-8 List the FLT_NBR Column in DWB_BKG Encrypted"
  8. Shows the command to create the encrypted tablespace: TBS_REFERENCE_TDE: 
    CREATE SMALLFILE TABLESPACE "TBS_REFERENCE_TDE" DATAFILE '/biaora/home/app/biaora/oradata/orcl12102/TBS_REFERENCE_TDE' SIZE 64M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT STORAGE(ENCRYPT) ENCRYPTION USING 'AES256';
  9. List the encrypted value for tablespaces, including: TBS_REFERENCE_TDE: 
    Select tablespace_name,encrypted from sys.user_tablespaces where tablespace_name like ‘TBS_%’

    Figure 6-9 Show the TBS_REFERENCE_TDE Tablespace

    Description of Figure 6-9 follows
    Description of "Figure 6-9 Show the TBS_REFERENCE_TDE Tablespace"
  10. Shows the commands to grant privileges on the new TBS_REFERENCE_TDE tablespace: 
    SQ> ALTER USER OADM_SYS  QUOTA UNLIMITED ON TBS_REFERNCE_TDE;

user OADM_SYS altered.
  11. Shows the commands to move the table DWR_FLT to the encrypted tablespace: 
    SQL>ALTER TABLE OADM_SYS.DWR_FLT MOVE TABLESPACE "TBS_REFERENCE_TDE";

SQL>BEGIN
DBMS_STATS.GATHER_TABLE_STATS('OADM_SYS','DWR_FLT',estimate_percent=>NULL,cascade=>TRUE);

END;
/
  12. Shows the query that list the table DWR_FLT in the encrypted TBS_REFERENCE_TDE tablespace.

    Figure 6-10 Shows the Table Name and Tablespace for DWR_FLT

    Description of Figure 6-10 follows
    Description of "Figure 6-10 Shows the Table Name and Tablespace for DWR_FLT"
  13. Shows the content of the table DWD_BKG_FCT. Before running the IETL, the target table DWD_BKG_FCT is empty:

    Figure 6-11 Shows the Empty DWD_BKG_FCT Count

    Description of Figure 6-11 follows
    Description of "Figure 6-11 Shows the Empty DWD_BKG_FCT Count"
  14. Run the IETL process to populate the DWD_BKG_FCT table:

    Figure 6-12 Shows a Run of the IETL Process

    Description of Figure 6-12 follows
    Description of "Figure 6-12 Shows a Run of the IETL Process"
  15. Now the target table has data.

    Figure 6-13 Shows a Nonzero Count for DWD_BKG_FCT

    Description of Figure 6-13 follows
    Description of "Figure 6-13 Shows a Nonzero Count for DWD_BKG_FCT"

Although the column DWB_BKG.FLT_NBR is encrypted and the table DWR_FLT is moved to the encrypted tablespace, this is transparent to Oracle Airlines Data Model.