Forum Stats

  • 3,768,187 Users
  • 2,252,756 Discussions
  • 7,874,485 Comments

Discussions

Categories

grant needed for dbms_metadata.get_ddl on objects in another schema

dario-2280378
dario-2280378 Member Posts: 110
edited Sep 3, 2013 11:02AM in General Database Discussions
In Oracle 11g R2, I use dbms_metadata.get_ddl to get DDL from another schema, but I got an error:

ORA-31603: object "IS_SNMP_DATA" of type TABLE not found in schema "TXV"

which grant is needed for this package to get ddl from another schema?
I tried with authid current_user (as I found on google), but no result...
Tagged:
Naji Al-AliManuel Ferreira
· Share on TwitterShare on Facebook

Best Answer

  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    Accepted Answer
    Check the following example: 
    SQL> create user us_one identified by us1;
User created.

SQL> grant connect, resource to us_one;
Grant succeeded.

SQL> create user us_two identified by us2;
User created.

SQL> grant connect, resource to us_two;
Grant succeeded.

SQL> conn us_one/us1
Connected.
SQL> create table t (id number);

Table created.

SQL> grant select on t to us_two;
Grant succeeded.

SQL> conn / as sysdba
Connected.

SQL> grant execute on dbms_metadata to us_two;

Grant succeeded.

SQL> conn us_two/us2
Connected.

SQL> select dbms_metadata.get_ddl('TABLE','T','US_ONE') from dual;
ERROR:
ORA-31603: object "T" of type TABLE not found in schema "US_ONE"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2806
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



no rows selected

SQL> conn / as sysdba
Connected.
SQL> grant select_catalog_role to us_two;

Grant succeeded.

SQL> conn us_two/us2
Connected.
SQL> select dbms_metadata.get_ddl('TABLE','T','US_ONE') from dual;

DBMS_METADATA.GET_DDL('TABLE','T','US_ONE')
--------------------------------------------------------------------------------

  CREATE TABLE "US_ONE"."T"
   (    "ID" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRA


SQL>
    · Share on TwitterShare on Facebook
«1

Answers

  • 618702
    618702 Member Posts: 2,659
    Dear 846417,

    Please refer to http://tahiti.oracle.com for the correct argument structure of DBMS_METADATA.GET_DDL objects.

    You have probably misplaced the arguments in it.

    Regards.

    Ogan
    618702
    · Share on TwitterShare on Facebook
  • dario-2280378
    dario-2280378 Member Posts: 110
    It's all ok when I'm running it for objects within the same schema, but when they are in another one, I got this error
    · Share on TwitterShare on Facebook
  • Sunny kichloo
    Sunny kichloo Member Posts: 2,443 Gold Trophy
    edited May 2, 2011 6:22AM
    Try to give select_catalog_role to the user and also this oracle document is helpful


    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1016867


    Also from what I've read and searched it seems like it is not a good idea to give this role as it gives them access to SYS schema objects

    Edited by: Sunny kichloo on May 2, 2011 3:19 AM

    Edited by: Sunny kichloo on May 2, 2011 3:22 AM
    · Share on TwitterShare on Facebook
  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    846417 wrote:
    In Oracle 11g R2, I use dbms_metadata.get_ddl to get DDL from another schema, but I got an error:

    ORA-31603: object "IS_SNMP_DATA" of type TABLE not found in schema "TXV"

    which grant is needed for this package to get ddl from another schema?
    I tried with authid current_user (as I found on google), but no result...
    What grants to you have on the object IS_SNMP_DATA that resides under the schema TXV?
    · Share on TwitterShare on Facebook
  • dario-2280378
    dario-2280378 Member Posts: 110
    select & alter.

    Also, invoker account has following rights:
    SELECT_CATALOG_ROLE
    ALTER ANY TABLE
    DEBUG ANY PROCEDURE
    SELECT ANY TABLE
    Naji Al-Ali
    · Share on TwitterShare on Facebook
  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    Accepted Answer
    Check the following example: 
    SQL> create user us_one identified by us1;
User created.

SQL> grant connect, resource to us_one;
Grant succeeded.

SQL> create user us_two identified by us2;
User created.

SQL> grant connect, resource to us_two;
Grant succeeded.

SQL> conn us_one/us1
Connected.
SQL> create table t (id number);

Table created.

SQL> grant select on t to us_two;
Grant succeeded.

SQL> conn / as sysdba
Connected.

SQL> grant execute on dbms_metadata to us_two;

Grant succeeded.

SQL> conn us_two/us2
Connected.

SQL> select dbms_metadata.get_ddl('TABLE','T','US_ONE') from dual;
ERROR:
ORA-31603: object "T" of type TABLE not found in schema "US_ONE"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2806
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



no rows selected

SQL> conn / as sysdba
Connected.
SQL> grant select_catalog_role to us_two;

Grant succeeded.

SQL> conn us_two/us2
Connected.
SQL> select dbms_metadata.get_ddl('TABLE','T','US_ONE') from dual;

DBMS_METADATA.GET_DDL('TABLE','T','US_ONE')
--------------------------------------------------------------------------------

  CREATE TABLE "US_ONE"."T"
   (    "ID" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRA


SQL>
    · Share on TwitterShare on Facebook
  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    Just to make sure, after granting SELECT_CATALOG_ROLE to your user, You need to exit and reconnect sqlplus to that specified user ;)
    Manuel Ferreira
    · Share on TwitterShare on Facebook
  • Vivek
    Vivek Member Posts: 82
    edited May 2, 2011 8:49AM
    hmmm need time 2 answer

    Edited by: 855956 on May 2, 2011 5:49 AM
    · Share on TwitterShare on Facebook
  • dario-2280378
    dario-2280378 Member Posts: 110
    It's working when I'm invoking an sql (select from dual), but not when I'm doing it through pl/sql (package)
    · Share on TwitterShare on Facebook
  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    846417 wrote:
    It's working when I'm invoking an sql (select from dual), but not when I'm doing it through pl/sql (package)
    Because SELECT_CATALOG_ROLE is a "role"

    Check the following blog post:
    http://kamranagayev.wordpress.com/2010/06/11/getting-ora-01031-being-granted-dba-role/
    Kamran Agayev A.
    · Share on TwitterShare on Facebook
This discussion has been closed.