When performing an export / import using datapump or with the legacy exp / imp utilities, a bug can cause wrong privileges to be granted to users.
The circumstances under which the bug occurs seems to be that a privilege is given on one user with grant option and on a second user (or role) without grant option and that the object must be a schema procedural object (job, program, schedule, …).
I could reproduce this issue on 10.2.0.4, 11.2.0.2 and 11.2.0.3, so changes are that all versions since 10.2 (or even 10.1) are affected.
SQL> @reproduce.sql SQL> set feedback on SQL> set linesize 120 SQL> set pages 9999 SQL> set trimspool on SQL> SQL> select 2 banner 3 from 4 v$version 5 ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production 5 rows selected. SQL> SQL> drop user fdh cascade; User dropped. SQL> drop user grant_test_usr; drop user grant_test_usr * ERROR at line 1: ORA-01918: user 'GRANT_TEST_USR' does not exist SQL> drop user grant_test_usr2; drop user grant_test_usr2 * ERROR at line 1: ORA-01918: user 'GRANT_TEST_USR2' does not exist SQL> drop role grant_test_role; drop role grant_test_role * ERROR at line 1: ORA-01919: role 'GRANT_TEST_ROLE' does not exist SQL> SQL> select 2 directory_path 3 from 4 dba_directories 5 where 6 directory_name = 'DATA_PUMP_DIR'; DIRECTORY_PATH ------------------------------------------------------------------------------------------------------------------------ /u01/oracle/oracle1/admin/gunnar/dpdump/ 1 row selected. SQL> SQL> create user fdh 2 identified by blabla 3 default tablespace users 4 quota unlimited on users 5 / User created. SQL> SQL> create user grant_test_usr 2 identified by blabla 3 default tablespace users 4 quota unlimited on users 5 / User created. SQL> SQL> create role grant_test_role 2 / Role created. SQL> SQL> create user grant_test_usr2 2 identified by blabla 3 default tablespace users 4 quota unlimited on users 5 / User created. SQL> SQL> BEGIN 2 dbms_scheduler.create_job 3 ( job_name => 'FDH.TEST', 4 job_type => 'PLSQL_BLOCK', 5 job_action => 'begin null; end;', 6 repeat_interval => 'FREQ=DAILY; BYHOUR=5', 7 end_date => NULL, 8 enabled => FALSE, 9 auto_drop => FALSE 10 ); 11 12 commit; 13 END; 14 / PL/SQL procedure successfully completed. SQL> SQL> create procedure 2 fdh.procedure_test 3 is 4 begin 5 null; 6 end; 7 / Procedure created. SQL> SQL> grant alter on fdh.test to grant_test_usr with grant option; Grant succeeded. SQL> grant alter on fdh.test to grant_test_role; Grant succeeded. SQL> grant alter on fdh.test to grant_test_usr2; Grant succeeded. SQL> SQL> grant debug on fdh.procedure_test to grant_test_usr with grant option; Grant succeeded. SQL> grant debug on fdh.procedure_test to grant_test_role; Grant succeeded. SQL> grant debug on fdh.procedure_test to grant_test_usr2; Grant succeeded. SQL> SQL> select 2 table_name, grantee, privilege, grantable 3 from 4 dba_tab_privs 5 where 6 owner = 'FDH' 7 and table_name in 8 ( 'TEST', 'PROCEDURE_TEST' 9 ) 10 order by 11 table_name, grantee, privilege, grantable; TABLE_NAME GRANTEE PRIVILEGE GRA ------------------------------ ------------------------------ ---------------------------------------- --- PROCEDURE_TEST GRANT_TEST_ROLE DEBUG NO PROCEDURE_TEST GRANT_TEST_USR DEBUG YES PROCEDURE_TEST GRANT_TEST_USR2 DEBUG NO TEST GRANT_TEST_ROLE ALTER NO TEST GRANT_TEST_USR ALTER YES TEST GRANT_TEST_USR2 ALTER NO 6 rows selected. SQL> SQL> /* execute the following part on the os SQL> in the data_pump_dir directory SQL> SQL> expdp system schemas='FDH' dumpfile=grant_test.dmp SQL> impdp system schemas='FDH' dumpfile=grant_test.dmp sqlfile=grant_test_dump.txt SQL> SQL> grep GRANT grant_test_dump.txt SQL> */ SQL> SQL> SQL> ! [oracle1@elin ~]$ cd /u01/oracle/oracle1/admin/gunnar/dpdump/ [oracle1@elin dpdump]$ expdp system schemas='FDH' dumpfile=grant_test.dmp Export: Release 11.2.0.2.0 - Production on Wed Nov 2 19:49:15 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password: UDE-28002: operation generated ORACLE error 28002 ORA-28002: the password will expire within 6 days Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=FDH dumpfile=grant_test.dmp Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ Processing object type SCHEMA_EXPORT/POST_SCHEMA/GRANT/PROCOBJ_GRANT Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/oracle/oracle1/admin/gunnar/dpdump/grant_test.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:49:49 [oracle1@elin dpdump]$ impdp system schemas='FDH' dumpfile=grant_test.dmp sqlfile=grant_test_dump.txt Import: Release 11.2.0.2.0 - Production on Wed Nov 2 19:49:57 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password: UDI-28002: operation generated ORACLE error 28002 ORA-28002: the password will expire within 6 days Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/******** schemas=FDH dumpfile=grant_test.dmp sqlfile=grant_test_dump.txt Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ Processing object type SCHEMA_EXPORT/POST_SCHEMA/GRANT/PROCOBJ_GRANT Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 19:50:02 [oracle1@elin dpdump]$ grep GRANT grant_test_dump.txt -- new object type path: SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT GRANT DEBUG ON "FDH"."PROCEDURE_TEST" TO "GRANT_TEST_USR" WITH GRANT OPTION; GRANT DEBUG ON "FDH"."PROCEDURE_TEST" TO "GRANT_TEST_ROLE"; GRANT DEBUG ON "FDH"."PROCEDURE_TEST" TO "GRANT_TEST_USR2"; -- new object type path: SCHEMA_EXPORT/POST_SCHEMA/GRANT/PROCOBJ_GRANT SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT ALTER ON "FDH"."TEST" TO "GRANT_TEST_USR" WITH GRANT OPTION'); SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT ALTER ON "FDH"."TEST" TO "GRANT_TEST_ROLE" WITH GRANT OPTION'); SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT ALTER ON "FDH"."TEST" TO "GRANT_TEST_USR2" WITH GRANT OPTION');
As you can see, all three grants on fdh.test are now including the “with grant option” clause, while only the grant to grant_test_usr should have been using the “with grant option”.
The privileges on the procedure are correct.
I have logged a case about this with Oracle, but no bug number is assigned at this moment