ORA-39181: Only partial table data may be exported due to fine grain access control on "OWNER"."TABLE_NAME"

Received an error while performing the export of an oracle database using System user.

ORA-39181: Only partial table data may be exported due to fine grain access control on "OWNER"."TABLE_NAME"

As the errors points that this is the issue with the fine grain access on the table. User surprisingly user system need exemption and can be done by

 GRANT EXEMPT ACCESS POLICY to System;

Next time you perform the export using system it should be all good.

Role is disabled for users when operation is done through plsql

Getting error like ORA-00942 or ORA-01933 or ORA-01031 and ORA-06512 or  PLS-00201 and ORA-06550 or ORA-28111 or ORA-01031: insufficient privileges or PLS-00201: identifier 'x' must be declared


If there is a user which has got some permission to do the following through the role


select a table, select a view, create a table, create a view, create a trigger


and the above operations are done using calling a plsql procedure then the operation will only work if the direct grants are done for the user running plsql block.


The reason is that when a plsql block is executed then only direct grants on that user are in use and grant done through the role will not work as the role will not kick in.


Example is as show below:

SQL> create user harv identified by abc123 default tablespace USERS temporary tablespace temp;

User created.

SQL> grant create session to harv;

Grant succeeded.

SQL> grant resource to harv;

Grant succeeded.

SQL> conn harv/abc123
Connected.
SQL> create or replace procedure harv.temp is
  2     var1 varchar2(4000);
  3  BEGIN
  4     var1:='create table harv.tab(
  5            id number)';
  6     EXECUTE IMMEDIATE var1;
  7  end temp;
  8  /

Procedure created.

SQL> exec harv.temp
BEGIN harv.temp; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "harv.TEMP", line 6
ORA-06512: at line 1


SQL> conn / as sysdba
Connected.
SQL>

SQL> grant create table to harv;

Grant succeeded.

SQL>

SQL> conn harv/abc123
Connected.
SQL> exec harv.temp

PL/SQL procedure successfully completed.


SQL>