Provides information on Oracle Airlines Data Model security.
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 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:
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.
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:
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)
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
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).
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.
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
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).
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.
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.
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).
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.
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),
Leverage the suggestions in Oracle Database Security Guide to split the various DBA roles and data access
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.
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.
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).
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).
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.
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.
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).
OBIEE End-Users: The end-users usually access their data through their browser on their mobile device or from their desktop.
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.
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.
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.
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
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.
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.
Presents information to plan for a secure Oracle Airlines Data Model installation. Outlines the steps required to perform a secure installation.
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_SAMPLE (to be removed at least for pre-production and production).
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.
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.
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
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
4 Not sensitive
Publicly available data (weather, and so on.)
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.
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.
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.
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).
Describes how to use Database Vault with Oracle Airlines Data Model.
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.
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.
sqlplus sys as sysdba Enter password: password
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; /
utlrp.sqlscript 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.
Enter password: password
SQL>SHUTDOWN IMMEDIATE SQL>STARTUP
You can query the V$OPTION dynamic view to verify if Oracle Database Vault is configured and enabled.
SQL>SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';
SQL>SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
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.
Describes how to secure the OADM_SYS Schema.
DV_ADMINrole and the SELECT ANY DICTIONARY privilege.
Object Type: Enter TABLE.
Object Name: Enter DWB_ACCT_PYMT.
SQL>conn dbv_acctmgr/dbv_acctmgr; SQL>GRANT CREATE SESSION TO OADM_MGR identified by OADM_MGR;
SQL>conn / as sysdba; SQL>GRANT READ ANY TABLE TO OADM_MGR;
Realm Authorization Grantee: Enter OADM_MGR.
Realm Authorization Type: Select Participant from the list.
Realm Authorization Ruleset: Leave this field blank.
SQL>CONNECT SYSTEM Enter password: password
The following output should appear:
Error at line 1:
ORA-01031: insufficient privileges
Enter password: password
Query data in DWB_ACCT_PYMT,data appears
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.
Describes steps for using data masking with 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.
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.
NAMES.DIRECTORY_PATH (TNSNAME, EXCONNECT) ERYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(MTHOD_DATA= (DIRECTORY=/biaora/home/app/biaora/admin/orcl12102/wallet)))
SQL>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/biaora/home/app/biaora/admin/orcl12102/wallet’ IDENTIFIED BY "password"; keystore altered
SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "password"; keystore altered SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "password" with backup;
Demonstrates TDE with one IETL package (PKG_DWD_BKG_FCT) as a TDE example.
Figure 6-2 Query Listing Tables for PCK_DWD_BKG_FCT
Figure 6-3 Shows Query for DWB_BKG with FLT_NBR Column
Figure 6-4 Data and Table for DWR_FLT
Figure 6-5 Query to Show Encrypted Columns
Figure 6-6 Showing Encryption Property for Tablespaces
Figure 6-7 Alter DWB_BKG and Encrypt Column FLT_NBR
Figure 6-8 List the FLT_NBR Column in DWB_BKG Encrypted
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';
Select tablespace_name,encrypted from sys.user_tablespaces where tablespace_name like ‘TBS_%’
Figure 6-9 Show the TBS_REFERENCE_TDE Tablespace
SQ> ALTER USER OADM_SYS QUOTA UNLIMITED ON TBS_REFERNCE_TDE; user OADM_SYS altered.
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; /
Figure 6-10 Shows the Table Name and Tablespace for DWR_FLT
Figure 6-11 Shows the Empty DWD_BKG_FCT Count
Figure 6-12 Shows a Run of the IETL Process
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.