Hello all,
When using data pump API i get ORA-31631: privileges are required
create user dp_user identified by Morninj01 default tablespace users profile IMDD_SCHEMA_PROFILE;
alter user dp_user quota unlimited on users;
GRANT CREATE TRIGGER TO DP_USER;
GRANT CREATE SESSION TO DP_USER;
GRANT CREATE TABLE TO DP_USER;
GRANT CREATE PROCEDURE TO DP_USER;
GRANT CREATE SEQUENCE TO DP_USER;
GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO DP_USER;
GRANT EXP_FULL_DATABASE TO DP_USER;
-- as SYS/SYSDBA:
GRANT SELECT ON DBA_OBJECTS TO DP_USER;
GRANT SELECT ON DBA_DATAPUMP_JOBS TO DP_USER;
This is happens always when API creates a new session.
1. Create user to run dbms_datapump
2. Create supporting objects
3. Run code: shows "ORA-31631: privileges are required"
4. Run anonymous block:
declare
h number;
begin
h := dbms_datapump.open( operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => null,
job_name => 'GU_EXPORT',
version => 'COMPATIBLE',
compression => 2 );
dbms_datapump.stop_job( handle => h, immediate => 0, keep_master => 0 );
end;
... which works
5 Re-run code and it works
My opinion is Privileges acquired via roles are not effective in a stored procedure. You need to grant the required privileges to the account that creates the package
The anonymous block uses rights granted to roles, whereas when you create a stored procedure, only rights that are granted directly to your user are used.
Thank You