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
Irrelevant thoughts of an oracle DBA 