datapump gives wrong privileges

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

Silent upgrade troubles

Last week I was asked to write a little script to automate an upgrade of oracle client 9.2.0.1 to 9.2.0.8.
Reason for this was that we needed to update arround 1.300 clients to enable them to connect to a 10g database (we couldn’t install 10g clients because of other applications restricted the client version to 9i).

Ok, easy enough. Oracle allows you to automate installations and upgrades via response files and the response file for a client upgrade from 9.2.0.1 to 9.2.0.8 is very simple.
When I started testing the upgrade, I immediately spotted a first problem. The setup.exe (it was on windows xp) started a new console and then returned directly to the prompt in the original console. This would make it impossible to check the return codes to know if a upgrade was successful or not.

The upgrade itself finished without a problem, but at the end the following message appeared in the newly started console: “Press enter to exit”.
Huh!? This was supposed to be a “silent” install, meaning no interraction needed. But here it was, asking to press enter to exit.
And the documentation was not telling anything about it.
After some searching, I found that you can specify the “-noconsole” flag when starting the setup, which would surpress the new console and avoid the question to press enter.
You still would see the question in the logfiles, but the installation presumed you responded to it and finishes the upgrade.

This left me with the first problem: the prompt would still directly return while the upgrade was running in the background.
After some searching in the documentation I found a note stating that you need to modify the oraparam.ini file and change the BOOTSTRAP parameter from TRUE to FALSE.
Unfortunately this did not help. Yelling at it did either.

Then I found that in 10g, you had a “-waitforcompletion” flag you could set, that would do exactly what I needed. So I tried if it would work for the oui shipped in the 9.2.0.8 patchset.
At first, it didn’t, but then I found metalink note 293044.1 that said that the setup.exe in Disk1 and Disk1/install where not the same and that the one in Disk1/install should be used for the “-waitforcompletion” flag.
At last it worked.

For those interested, here is the full command I used to start the silent upgrade:

start /wait C:\oracle\patches\9.2.0.8\Disk1\install\setup.exe -silent -noconsole -waitforcompletion -responsefile c:\oracle\patches\9.2.0.8\patchset.rsp -paramfile c:\oracle\patches\9.2.0.8\oraparam.ini

—————————-

Thanks to Geert for the yelling link :)

How to use the plan_table table to sabotage your oracle upgrade

Lets say you need to upgrade your 9i database to 10g (10.2.0.4 to be exact), but you actually want to sabotage the upgrade (don’t know why, just assume you do).
Granted, there are many ways to do this, but you want to do it subtle. What are your options then?
Well, one option is to create the plan_table table in your sys schema (or a synonym plan_table to a plan table in another schema if you want to make it really subtle) before the upgrade.
If you do this, you will see the following message in your upgrade log:

Warning: Package Body created with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY DBMS_SQLPA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
113/5    PL/SQL: SQL Statement ignored
118/44   PL/SQL: ORA-00904: "OTHER_XML": invalid identifier

And the “oracle database server” component in the dba_registry will be marked as invalid.
Mission accomplished I would say.

What is that?
You regret your actions and you want to fix the problem?

*sigh*

Ok then, to fix it you can use the following steps:

• drop the sys.plan_table table
• drop the sys.plan_table$ table
• drop all sys synonyms and public synonyms to the plan_table or the plan_table$
• @?/rdbms/admin/catplan.sql — recreate the plan table
• @?/rdbms/admin/dbmsxpln.sql — reload dbms_xplan spec
• @?/rdbms/admin/prvtxpln.plb — reload dbms_xplan implementation
• @?/rdbms/admin/prvtspao.plb — reload dbms_sqlpa

For those seeking more information:

Metalink note 565600.1 – ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
Metalink note 605317.1 – DBMS_SQLPA ORA-00904 OTHER_XML invalid identifier

According to the notes, the problem only exists with upgrades to 10.2.0.4 or to 11.1.0.7.

ps. Don’t ask me why I had a synonym called plan_table in my sys schema. I didn’t do it.
pps. This is why you should test your migration (I’m glad I did)