Data Masking

We test the application on our project using a development database created by scrubbing production data. That original scrub happened over 10 years ago. Our development data is starting to get a bit stale. It gets updated every year to try to keep pace with the new dates. But formats and structures change. This sounds like a job for Oracle Data Masking,

Data Masking is an add on pack for Enterprise Manager. It allows you to meet data privacy and data protection mandates. It replaces sensitive values with realistic replacements. Thus you can use production like data for non-production uses.

Data Masking can actually scan for sensitive data. It can make sure sensitive data does not leave the production environment. Its operation is also called scrambling or anonymization. The scrubbing is based on rules that you set up or choose from some preset formats.

Here are the data type supported for scrubbing:

• Numeric
• String
• Date
• LOB (requires Grid 11gR1)

These are the overall steps you take to perform the data masking:

1. Clone production to a staging environment
2. Select mask definition in staging
3. Clone the masked staging data to a test environment

The predefined mask formats allow you to generate random numbers for replacements. You can also do some post processing to ensure the data is of believable format. Masking is controlled by the DM_FMTLIB package. Here are some presets for well known data types that are supported:

• Credit card numbers
• ISBN numbers
• UPC codes
• Social Security Numbers
• Phone numbers

 Here are the roles you need to perform a masking operation:

• Select catalog role
• Select any dictionary
• Execute privileges on DBMS_CRYPTO

Be aware that masking partitioned tables might cause some partition movement if you mask the partition key. There are some types that are not supported by masking:

• Clustered tables
• Object tables
• XML tables
• Virtual columns

Masking can happen quickly because of the following optimiations:

• Bulk operations
• Disabled logging
• Running in parallel

Oracle Spatial

Oracle has support to location enable application. This is called Spatial. It was named Oracle Spatial since Oracle 8. And it is now called Oracle Spatial and Graph since Oracle 10g. Spatial consists of functions and procedures for quick spatial data access and analysis. The code is available in the MDSYS schema.

Spatial defines a number of geometry types:

• Point
• Line
• Polygon
• Arc
• Circle
• Rectangle

Spatial allows you to deal with height and depth data. Or it can represent longitude and lattitude data. This is application to a Geographic Information System (GIS) application. It allows you to project this data onto a two dimensional surface.

The basic building block in Spatial is an element. Types, like mentioned before, are point, line, or polygon. Many elements make up a geometry. And a collection of geometries is a layer.

Spatial allows you to perform an area of interest query. There are two tiers to its query model: (1) primary filter, and (2) secondary filter. To help speed querying, a concept of a spatial index has been created. This is a logical index. There are two types. The first, which is the default, is the R-tree. It uses a minimum bounding box. The second is the quadtree. It uses a technique called tessellation. Each rectangle is chopped into smaller rectangles.

Oracle Label Security

There are many ways to lock down data access in Oracle. A low granularity technique is one we use on my project. That would be the Discretionary Access Control (DAV). With this you pretty much determine whether users have SELECT/INSERT/UPDATE/DELETE privilege to any given table. The users can either do those operations, or they cannot.

The Virtual Private Database (VPD) technology takes security to another level. You get more control. Specifically you decide which rows any type of user is allowed to see. I have written about VPD before. Now I want to talk about Oracle Label Security (OLS). OLS is built upon VPD. But unlike VPD, which requires you to write an access function in PL/SQL, OLS runs "out of the box."

Out of the box means you don't need to do any programming to use OLS. You can set up what you need using Oracle Enterprise Manager. You can create labels. Assign labels to rows. And also assign labels to users. Also you can use Oracle's policy manager tool.

As you can imagine, OLS was originally created to server government applications. Specifically it was meant to address Department of Defense security needs. However OLS must have been a hit. Because now it is also being used in the commercial sector.

The high level idea is that users can have labels associated with them. Specific rows of specific tables can also have labels applied to them. When a table is accessed by a user, the labels of the user and rows are compared. If the labels match, the user gets access. This label information is stored in a new column added to the table for access purposes.

Although I have heard it different ways, more people report that OLS does not come installed by default in the Oracle database. If so, you must use the Universal Installer to add it in. You also need to configure the LBACSYS account that gets installed with OLS. You must unlock it, set its password, and grant it SELECT ANY DICTIONARY.

Recovery Manager

Oracle has had the Recovery Manager (RMAN) since Oracle 8. It is installed along with the database. RMAN is a database client. The client can be run from the command line or Enterprise Manager. One main feature is cross platform backup and restore. RMAN automates a lot of the administration of backups. It can do point in time table recovery. It also can detect corruption in backups.

RMAN backups are stored as backup sets. It can do incremental as well as full backups. RMAN works with a media manager. That would be the system that controls the devices where backups are stored. For example, one media manager might control your tape drive.

An important facet of RMAN operation is the recovery catalog. This is a database schema where RMAN activity is recorded. It tracks the file names of all database files. It stores the location of redo logs. It can be used if the control file is lost.

RMAN is started by running the rman executble. You CONNECT TARGET to log into a database. The user needs SYSDBA privileges, although you do not explicitly state AS SYSDBA. The BACKUP command will start the backup. Unique file names are generated for backup files. The files are proprietary and specific to RMAN only.

You can do a hot backup while the system is running. The database must be in ARCHIVELOG mode. This results in what they call an inconsistent backup because it will require redo logs. Backups can be full or incremental. Incremental backups start at level 0 which is equivalent to a full backup. The VALIDATE command checks whether there is any backup corruption.

You can put RMAN commands in a file. Run it with the @ sign, just like SQL*Plus. Use the LIST BACKUPS for a report. Or use the REPORT command for a more comprehensive report. DELETE will explicitly get rid of old backups. Any backups older than the retention period are automatically deleted. Recovery involves a RESTORE DATABASE along with a RECOVER DATABASE command.

Virtual Private Database

Oracle has a security feature introduced in Oracle 8i called the Virtual Private Database (VPD). It allows you to specify policies at the row and column level. Under the hood, it adds a WHERE clause to SQL statements to limit what data you can see. Normally there is no way to bypass this security.

The VPD restrictions apply to select/insert/update/index/delete statements. They do no affect DDL. The security will have an impact on security. There are some types of VPD you can use to lessen the performance hit:

• Static policy - applied once per query
• Context sensitive - applied when context changes
• Dynamic policy - reevaluated every run

VPD runs with definers rights. You write a function that generates the WHERE clause to restrict access. This function must take a schema and object names as inputs. It must return a VARCHAR2 which is the WHERE clause addition. This special function cannot access the table it is providing security for.

You manage the VPD security with calls to the DBMS_RLS package. RLS stands for row level security. There is also a column masking type of VPD. That style will return all rows int he object. But sensitive columns will be returned as NULL values. The column masking technique only applies to SELECT statements.

VPD policies can be managed in groups. One should not perform a SELECT FOR UPDATE on an object managed with VPD. There can be multiple polices on an object. There WHERE clauses implementing the VPD will be joined via AND.

Earlier I said that VPD cannot be bypassed. VPD is not applied in these circumstances:

• DIRECT path exports
• Operations on SYS owned objects
• Operations run by SYS
• Operations run AS SYSDBA
• The user has the EXEMPT ACCESS POLICY privilege

VPD goes by other names. It is also known as Fine Grained Access Control. And it is also sometimes referred to as Row Level Security. Thus the DMMS_RLS package.

Bulk Processing

I usually keep performance in mind when I write my PL/SQL. Just being smart about the SQL usually is good enough. But sometimes you got a lot of data to process. In those scenarios, you might need to bulk up. Do some bulk processing that is.

Bulk processing is the term to describe BULK COLLECT and FORALL. The big idea is that there is a PL/SQL engine and a SQL engine in the database. Switching between the two costs you speed due to overhead. So it would be best to make one handoff from the PL/SQL engine to the SQL one.

BULK COLLECT will cause a query to make a single switch into the SQL engine and fill up your colection. FORALL works the other way around, allowing you to do one DML operation for all the items in your collection with just one context switch into the SQL engine.

There is a warning though. Don't do unbounded BULK COLLECT operations. That will eat up a lot of memory in the PGA (Program Global Area). Collections are stored in memory there. You don't want a huge one taking up too much memory. Use the LIMIT statement to cap how many records are grabbed at once in a BULK COLLECT. Experience says you should use a LIMIT of at least 25. It would be preferable to use a LIMIT of around 100. Oracle uses 100 as the default optimization for cursor loops in Oracle 10g and above.

After you do the BULK COLLECT, if there are records, they will start with index 1 in your collection. The rest of the records will be densely populated in the collection. If there are no records, the collection will be empty. The COUNT on your cursor will be zero. There will not be a NO_DATA_FOUND exception raised in this scenario. Similarly you cannot count on %NOTFOUND. Use the COUNT.

Here is a tip for the FORALL. It is not a loop. You don't need a loop command. By default the collection you are doing DML on must be dense. If not, you must use the IN INDICES OF with the FORALL. That only works with Oracle 10g and above. Normally you would not want exceptions in the middle of your FORALL to stop the flow. To prevent that, use the SAVE EXCEPTIONS clause. Then errors will be stored up in the SQL%BULK_EXCEPTIONS pseudo collection.