Leaving the History Behind with Oracle's Fine Grained Access Control

Friday Jan 24th 2003 by DatabaseJournal.com Staff
Share:

Marin Komadina explains how to export tables from a user schema--without having to export the history tables as well.

Marin Komadina

Oracle has not solved the problem of exporting all but a few larger tables from a user schema thus far. This leaves DBAs to find their own workarounds when necessary. This article covers the three most used solutions.

  • Full export
  • Data dictionary view modification
  • Fine Grained Access Control ( FGAC)

Scenario A:
The DBA needs to export schema tables, without the static history data. The user schema has approximately 1000 tables; four tables are static and include historic partitioned data. These four tables need to be skipped in the export file.

Scenario B:
Recovery strategy, with daily export, has to be abandoned because it takes too long. Exporting the large history tables consumes most of the export time.

In a case where the production table data has been deleted, an export file can be used to quickly populate small tables, leaving the big historical tables to be fixed later with a transportable tablespaces solution or by importing them from separated export file.

Some solutions have suggested creating large tables with different schemas, importing, exporting, duplicating, moving etc. I have found this solution to be too haphazard for some bussiness oriented companies.

For a clever DBA, there are several possible solutions:

  • full schema export and send everything to developers, saying that is only way possible
  • play with fire, modifying the database catalog view
  • do a professional job and show others that you know what Fine Grained Access Control is

Test Case

As a test case, let's assume we have created a small Oracle database 8.1.7.4 on Solaris.

  • User schema for this test will be user ARTIST
  • ARTIST has a few test tables
  • User ARTIST has DBA rights, just to make the test simpler.
  • The same logic applies to any other user you choose to export.
Database Version:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
 
Schema ARTIST : 

TABLE_NAME                          COUNT(*)
---------------------------------------------
BACKUP_STATUS                           411
CRITICAL_SEGMENTS                         0
INPUT                                10,000
PLAN_TABLE                               13
TRANSFER                             10,000
TS_STORAGE                           25,334

Solutions

a. ) Full export

. about to export ARTIST's tables via Conventional Path ...
. . exporting table                  BACKUP_STATUS        411 rows exported
. . exporting table              CRITICAL_SEGMENTS          0 rows exported
. . exporting table                          INPUT      10000 rows exported
. . exporting table                     PLAN_TABLE         13 rows exported
. . exporting table                       TRANSFER      10000 rows exported
. . exporting table                     TS_STORAGE      25334 rows exported

The full export that we have just produced is one solution to our problem. You have just to send it, together with log file to developers.

Here is small tip regarding export. It is possible to make a selective table export with parameter tables.

$exp parfile=artist.par userid=system/manager

Example of parameter file: 

$ cat artist.par 
buffer=8388608 
file=artist_full.dmp
log=artist_exp.log
consistent=y
tables=(artist.backup_status,artist.critical_segments,...)

This allows you to mark up to a maximum of 99 table names for export in the form owner.table_name inside parameter file. In our case, we have 996 tables that we want to export. (We don't want the 4 large ones). It would be hard work to write all of the names in a list, and to make several export files with several parameter files. For that reason, we are going to solution b.)

b.) Data dictionary view modification

For this next solution, I'll remind you of the oft repeated warning from Oracle.

"You should not change datadictionary objects without supervision from Oracle."

That is absolutely correct; but we're still going to do it. Our operation is not that critical, because we are only going to change the definition of one datadictionary view: the one responsible for table selection during export. Be sure to make a backup before you start.

CREATE OR REPLACE VIEW SYS.EXU81TABU 
...
from exu81tab WHERE ownerid = uid 
and NAME NOT IN ('INPUT','TRANSFER');

Version 8: view name - SYS.EXU81TABU
Version 7: view name - SYS.EXU7TAB

After modification, let's run an export of test user ARTIST:

. about to export ARTIST's tables via Conventional Path ...
. . exporting table                  BACKUP_STATUS        411 rows exported
. . exporting table              CRITICAL_SEGMENTS          0 rows exported
. . exporting table                     PLAN_TABLE         13 rows exported
. . exporting table                     TS_STORAGE      25334 rows exported

We have succeeded in getting rid of the large tables INPUT and TRANSFER. The export file is consistent. As a check, you can run import to test recovery scenarios.

All of this, but yet, there is still a better solution.

c.) Fine Grained Access Control ( FGAC)

Two new features of Oracle8i release 8.1 are Fine Grained Access Controls and Secure Application Contexts. There has been considerable confusion regarding the naming of these new features.

Synonyms used so far:

  • Fine Grained Access Control (technical name)
  • Virtual Private Database (marketing name)
  • Row Level Security (technical name based on the PL/SQL packages that implement this feature)

From Oracle documentation:

"Fine Grained Access Control in Oracle8i is the ability for you to dynamically attach, at runtime, a predicate (where clause) to any and all queries issued against a database table or view. You now have the ability to procedurally modify the query at runtime. You may evaluate who is running the query, where they are running the query from, when they are running the query and develop a predicate given those circumstances. With the use of Application Contexts, your may securely add additional information to the environment (such as an application role the user may have) and access this in your procedure or predicate as well."

For our export test we are going to mask some tables from user.
Please take care that you use this approach only if your database supports the FGAC option.
The Standard Edition does not include the FGAC option, and it will not work. You have full support in the Enterprise Edition and the Personal Edition.

First we have to create a function and then a policy for the ARTIST user.

SQL> connect artist/artist@dba.world
Connected.

In schema ARTIST, create a function "skip_artist_table" which will return predicate, with condition checking (1=2).

The result is always false, so rows are never returned to ARTIST.

SQL> CREATE or REPLACE FUNCTION skip_artist_table (tbl_schema VARCHAR2, tbl_name VARCHAR2)
  2     RETURN VARCHAR2 IS usr_context VARCHAR2(2000);
  3     BEGIN
  4      if sys_context ('USERENV', 'SESSION_USER') = 'ARTIST' THEN usr_context := '1=2';
  5      else 
  6       usr_context := '';
  7      end if;
  8      RETURN usr_context;
  9  END skip_artist_table;
 10  /

Function created.

SQL> select skip_artist_table('dummy','dummy') from dual;

SKIP_ARTIST_TABLE('DUMMY','DUMMY')
---------------------------------------------------------------
1=2

Next, we need to check the tables.

SQL> connect artist/artist@dba.world
Connected.

SQL> select count(*) from TRANSFER;

  COUNT(*)
----------
     10000
SQL> select count(*) from INPUT;

  COUNT(*)
----------
     10000

The next step is to add Policy for each table that we want to skip. We are using add_policy procedure from Oracle dbms_rls package:

SQL> execute dbms_rls.add_policy 
('ARTIST','TRANSFER','SKIP_TRANSFER','ARTIST','SKIP_ARTIST_TABLE');

PL/SQL procedure successfully completed.

SQL> execute dbms_rls.add_policy 
('ARTIST','INPUT','SKIP_INPUT','ARTIST','SKIP_ARTIST_TABLE');

PL/SQL procedure successfully completed.

SQL> select count(*) from TRANSFER;

  COUNT(*)
----------
         0

SQL> select count(*) from INPUT;

  COUNT(*)
----------
         0

We're ready to make an export and compare results.

. about to export ARTIST's tables via Conventional Path ...
. . exporting table                  BACKUP_STATUS        411 rows exported
. . exporting table              CRITICAL_SEGMENTS          0 rows exported
EXP-00079: Data in table "INPUT" is protected. 
Conventional path may only be exporting partial table.
. . exporting table                          INPUT          0 rows exported
. . exporting table                     PLAN_TABLE         13 rows exported
EXP-00079: Data in table "TRANSFER" is protected. 
Conventional path may only be exporting partial table.
. . exporting table                       TRANSFER          0 rows exported
. . exporting table                     TS_STORAGE      25334 rows exported

To get rid of masking:

SQL> connect artist/artist@dba.world
Connected.
SQL>  execute dbms_rls.drop_policy('ARTIST','TRANSFER','SKIP_TRANSFER');

PL/SQL procedure successfully completed.

SQL> execute dbms_rls.drop_policy('ARTIST','INPUT','SKIP_INPUT');

PL/SQL procedure successfully completed.

SQL> select count(*) from TRANSFER;

  COUNT(*)
----------
     10000
SQL> select count(*) from INPUT;

  COUNT(*)
----------
     10000

This small UNIX script can handle the task of dynamically adding policy, exporting users' tables, and dropping policy after export.

Summary

This article shows some techniques to avoid the Oracle database utility limitation. Export is one of the great tools and DBAs should know their limitations. Oracle is developing in such a way that we can expect a solution for tricks we used in version 10i. Anyway, we've touched on some lovely features, like FGAC database, that are not well known but used throughout Europe in the banking sector. So DBAs, be prepared for more surprises.

» See All Articles by Columnist Marin Komadina

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved