From:	Huang, Yong
Sent:	Wednesday, May 02, 2001 1:36 PM
Subject:What's wrong with port export? "Export port did not complete successfully!"
Hi, All,

Recently exporting the port database always throws the following error which is emailed to us:

=============================================
sire
exp.sh port /f03/backup/cold/port/exp/port.dmp starts at: Tue May 1 05:00:02 CDT 2001
oracle sid: port
logfile: /tmp/expsh_port.log
old export file /f03/backup/cold/port/exp/port.dmp is removed
oracle home: /app/oracle/product/8.1.7
exp.sh: got PASSWORD from dbinfo
database export end at: Tue May 1 05:28:51 CDT 2001
export file /f03/backup/cold/port/exp/port.dmp is compressed
Error/warning during export! See log file /tmp/exp_port.log
--------------------------------------------------------------------
Export port did not complete successfully!
exp.sh: Taking error exit
--------------------------------------------------------------------

The error recorded in the export log file is EXP-79, which is:

oracle AT sire$ oerr exp 79
00079, 0000, "Data in table \"%s\" is protected. Conventional path may only be e
xporting partial table."
// *Cause:  User without the execute privilege on DBMS_RLS, the access control
//        package, tries to export a table that has access control. Since table
//        owner is also subjected to access control, the owner may not be able
//        to export all rows in the table, but only the ones he can see.  Also,
//        to preserve integrity of the table, user exporting the table should
//        have enough privilege to recreate the table with the security
//        policies at import time. Therefore, it is strongly recommended
//        the database administrator should be handling exporting of this
//        table. Granting the table owner execute privilege would also
//        satisfy this security check, though it might have other security
//        implications. If the table does not have objects, can use direct mode.
// *Action: Ask the database administrator to export/import this table/view.

The DBMS_RLS is a package new in Oracle 8i for Row Low Security, a.k.a fine grained access control or virtual database. With it, users select * from sometable will only be able to view those rows allowed by RLS policy. Information about these policies is available in DBA/ALL/USER_POLICIES. For example, the table portal30_sso.WWC_VERSION$ has a security policy on it named WEBDB_VPD_POLICY, which uses a function WEBDB_VPD_SEC. Query or DML action on these tables (or views) is subject to policy control.

But that's not the end of the story. Another effect or side effect of RLS is that no user other than SYS or INTERNAL can export those tables. An error EXP-79 is thrown if you do. Another way to avoid security policies is export with direct path (we have not tested). One other way is modify the policy function so that select * from sometable actually selects all rows (i.e. use a null predicate in the security function for the user doing the export); but this is not possible in our case since PORTAL30 and PORTAL30_SSO, the only two users having security policies in the port database, are not supposed to be modified.

Conclusion: The easiest way to bypass the policies for the purpose of database export is to change user from SYSTEM to SYS. We don't foresee any security or other problems in doing so. For other databases, we may also start to use SYS instead of SYSTEM for export, since someday row level security may be implemented in software upgrade or explicitly by DBAs or developers.

Yong H