Forum Stats

  • 3,757,088 Users
  • 2,251,194 Discussions
  • 7,869,721 Comments

Discussions

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

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> 
«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
  • 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
  • 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
  • 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?
  • 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
  • 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> 
  • 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
  • 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
  • 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)
  • 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.
This discussion has been closed.