| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Skipping a table on import (Using FGAC)
Suggestion: Investigate using FGAC.
I just spent part of my day working on a solution to a very similar problem. I wanted to do a user level export but restrict the records from one table based on the value of a timestamp field. I did not want to have multiple exports. I originally considered a combination of tablespace and table level exports, but it made the nightly import process rather difficult to perform the way I would like.
The solution I settled on was to use Oracle's fine-grained access control (FGAC). This feature has been available since 8i. I found FGAC a bit difficult to get my head around, but I had no problems after I worked through the example. I got the idea from Metalink DOC_ID: 162914.1 which explains how to use FGAC to skip a table during an export. I also referred to the Application Developer's Guide documentation.
The effect of the FAGC I implemented is restrict which records the EXPORT user can see in a table. This effects only the EXPORT user. All other users see all of the records.
RECORD COUNT ISSUED RUN AS ME (I SEE ALL RECORDS) SQL> select count(*) from bigjet.message;
COUNT(*)
342559
Export run as user EXPORT (HE SEES FILTERED RECORDS)
exp export/freewilly_at_WHALEJET file=test.dmp tables=bigjet.message
About to export specified tables via Conventional Path ...
Current user changed to BIGJET
EXP-00079: Data in table "MESSAGE" is protected. Conventional path may only
be exporting partial table.
. . exporting table MESSAGE 52445 rows exportedEXP-00091: Exporting questionable statistics. Export terminated successfully with warnings.
The basic steps I followed are listed below.
/****************************************
CREATE Function to return predicate
used to filter records for the EXPORT user
****************************************/CREATE OR REPLACE FUNCTION export.export_message(obj_schema varchar2, obj_name varchar2)
BEGIN
IF sys_context('USERENV','SESSION_USER')='EXPORT' THEN
d_predicate := 'SYS_LAST_CHANGED_TS > TRUNC(SYSDATE-2) ';
ELSE
d_predicate := '';
END IF;
RETURN d_predicate;
END export_message;
/
/****************************************
CREATE FGAC Policy Group
****************************************/EXECUTE DBMS_RLS.CREATE_POLICY_GROUP('BIGJET','MESSAGE','MESSAGE_GROUP');
****************************************/
EXECUTE
DBMS_RLS.ADD_GROUPED_POLICY('BIGJET','MESSAGE','MESSAGE_GROUP','EXPORT_POL',
'EXPORT','EXPORT_MESSAGE');
/****************************************
ENABLE FGAC Policy
****************************************/
EXECUTE DBMS_RLS.ENABLE_GROUPED_POLICY
('BIGJET','MESSAGE','MESSAGE_GROUP','EXPORT_POL',TRUE);
/****************************************
DISABLE FGAC Policy
****************************************/
EXECUTE DBMS_RLS.ENABLE_GROUPED_POLICY
('BIGJET','MESSAGE','MESSAGE_GROUP','EXPORT_POL',FALSE);
> -----Original Message----- > Is there a > way to skip > certain tables during an import?
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: Stephen.Lee_at_DTAG.Com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ed Bittel INET: grep_oracle_at_hotmail.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Feb 20 2003 - 15:56:33 CST
 |
 |