Irrelevant thoughts of an oracle DBA

2 November 2011

datapump gives wrong privileges

Filed under: bugs,upgrades / migrations — Freek D'Hooge @ 20:58
Tags: , , , ,

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

Advertisements

Create a free website or blog at WordPress.com.