Security and Compliance with Oracle Database 12c

Oracle Key Vault

Master Key in Vault – Transparent Data Encryption.

– Validate encryption keys

– Create encrypted tablespace

– Move data into encrypted tablespace

– Verify encryption

– Drop unencrypted tablespace without dropping database files

– Securely delete unencrypted database files

Audit Vault

– Separate instance

– Audit data sent to this instance

Database Firewall

– Only allow certain sql statements

– Can use unique so predicate values do not result in duplicate entries

– Can first capture and login data

– Can select whitelist policy (Pass,Logging Level Unique)

– Does not pickup local connections as these are not over the network

– New SQL Statements go to default rule and can be blocked.

– Default rule can also substitute the statement.

– Assigned Firewall Policy to secured target.

– Create dba user set

– Add exception to policy for dba user set

Database Firewall works at OSI Application (SQL*Net) layer.

Multiple Database Firewalls can connect to 1 Audit Server.

Privileged Account Management – proxy server bob->oracle

Database Vault

– Realms, transparently Block DBA privileges from accessing data

– Command Rules – grant with admin option – can filter

– Installed by default with 12c, just need to enable.

– DBA, Account Administrator, Security Administrator (Database Vault,Realms,Command Rules)

– Oracle 12c adds Mandatory Realm, even table owner cannot access table unless part of the realm.

– Oracle 12c adds privilege analysis, run db in training mode.

– Realm Audit Report shows attempted violations

– Need to enable OLS (Oracle Label Security) to use Database Vault

– Still need traditional object privileges access as well.

– Can specify that a user can only access a realm during business hours

– Can use PL/SQL procedure to apply restrictions

– User can be in multiple realms and all realms must allow access.

– PL/SQL function DVSYS.SQL_TEXT is parsed sql text without comments.

Dask Masking and Subsetting

– Change but keep checksum the same

– Keep primary/foreign keys in sync.

Oracle Internet Directory, Oracle Virtual Directory, Now Oracle Unified Directory consume user/group information from Active Directory, do not need to change AD schema. Roles for users done via groups in AD.

Oracle Real Application Testing and Database Replay.

Database Replay

Capture->Workload->Replay.

Capture with real load,timing,concurrency.

Replay honours commit order.

If mask when transfer to replay server then must also mask the capture files.

All external client requests captured in binary files.

Capture supported for 9i and 10g, replay was not back ported.

Capture files are platform independent.

Capture can be filtered by user,program,module,action,service,session id.

Filters can also be applied at replay time.

10.2.0.4 have patch for replay.

Both capture and replay side require Real Application Testing license.

Estimate capture file size 2*bytes from SQL*Net from AWR report or estimate from small capture window.

Run Workload Analyzer on captured workload to see how many database requests were not complete at the end of the capture window

Replay Workload, Oracle will tell you how many replay clients are needed.

During replay the ‘think’ time can be reduced.

Connections can be remapped e.g. for database links.

Query part of workload can be scaled up during replay for load testing.

11.2.0.2+ allow consolidated replay, workloads from different databases can be replayed together e.g. when testing a move to 12c multitenancy with pluggable databases Start times for each replay can be moved to allow capacity planning for colliding peak workloads.

Workload folding, run multiple parts of a single capture at the same time.

Analysis and Reporting allows checking for divergence – error,data,performance (ADDM,AWR,ASH).

SQL Performance Analyser (SPA) integrated with Resource Governor so can potentially run on production,integrated with tuning and diagnotics pack.

Oracle ODI and Goldengate

Goldegate vs Advanced Replication/Streams

Streams is Oracle only.

Active Data Guard is readonly, Golden Gate can write.

Golden gate links into Log Miner API (11.2.0.3+ specific patch) now and multi threaded (11.2.0.4).

Classic Log Capture still needed for remote databases and integration with Active Data Guard.

Oracle DR still Data Guard.

Goldengate license includes Active Data Guard.

Goldengate linked to Java Message Bus.

Golden gate allows subsetting at schema,table,column,row.

Golden gate low impact, knowledge module for integration.

Goldengate apply to staging area, or JMS queue to ETL tool (chargeable option) or send to flat files for consumption by Etl Tools (chargeable option).

Conflict resolution out of the box. Can also consume from Java Message Bus.

Script check for setup of Goldengate – database check and health check for integrated components.

Oracle Goldengate Coherence – real time.

Goldengate supports off box capture – do not need to install on vendor host.

Trail files used to store changes, universal Trail Format, no endian issues.

Can batch operations, can call to the database procedures as well. Simple transformation, calculations, no aggregations.

Non-oracle databases, reads transaction log.

Capture->Trail file ->Pump (transaction not complete files)->Trail file->Delivery.

Replication Hub possible.

Pump process checkpoint in source/target trail files.

Manager process will restart pump process if network going down.

Rman knows about goldengate now, qill not discard archived logs.

Bounded recovery – can checkpoint partial long running transactions.

RAC – use DBFS for trail files, Goldengate integrated into cluster software – grid control.

Trail files, checkpoint and bounded recovery files.

Can replay trail files from archive logs or source trail files.

If cannot install on source machine use oracle stub database on downstream machine.

Integrated Extract only 1 target can send to downstream machine directly, rest have to be via archived logs.

GoldenGate 12c adds integrated delivery and co-ordinated delivery for non-oracle databases (co-ordinator thread).

Streams can be migrated to Goldengate.

12c added capture for Informix.

Integrated components must be used for Oracle 12c multi-tenant databases.

SQL Server configure DSN,username,password.

Parameter file per Goldengate process.

Capture can be started from SCN or date/time or BEGIN NOW.

Audit tables – ignore deletes, convert all transactions to inserts.

Install Goldengate, start capture,backup.restore, start apply from specific SCN.

Can added metadata via GETENV function and convert all changes to inserts only for audit.

Goldengate Veridata compare source/target.

Veridata 12.1.3 – can fix as well, use literal primary key+hash of non-primary key columns.