Forum Stats

  • 3,767,865 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

grant needed for dbms_metadata.get_ddl on objects in another schema

2»

Answers

  • ok, now I copied my code to a 'target' schema, and there's the same error.... so, it's not beetween schemas...
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    Privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    846417 wrote:
    ok, now I copied my code to a 'target' schema, and there's the same error.... so, it's not beetween schemas...
    You need to grant all privileges of SELECT_CATALOG_ROLE directly to the user
    Kamran Agayev A.
  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    846417 wrote:
    I did
    Show what you did and show the result please
    Kamran Agayev A.
  • this is user's script:
    CREATE USER TXV
      IDENTIFIED BY <password>
      DEFAULT TABLESPACE TXV
      TEMPORARY TABLESPACE TEMP
      PROFILE DEFAULT
      ACCOUNT UNLOCK;
      -- 3 Roles for TXV 
      GRANT RESOURCE TO TXV;
      GRANT CONNECT TO TXV;
      GRANT SELECT_CATALOG_ROLE TO TXV;
      ALTER USER TXV DEFAULT ROLE ALL;
      -- 17 System Privileges for TXV 
      GRANT DEBUG CONNECT SESSION TO TXV;
      GRANT DEBUG ANY PROCEDURE TO TXV;
      GRANT CREATE ANY DIRECTORY TO TXV;
      GRANT CREATE MATERIALIZED VIEW TO TXV;
      GRANT CREATE SYNONYM TO TXV;
      GRANT CREATE VIEW TO TXV;
      GRANT UNLIMITED TABLESPACE TO TXV;
      GRANT ADMINISTER DATABASE TRIGGER TO TXV;
      GRANT CREATE TRIGGER TO TXV;
      GRANT CREATE ANY TABLE TO TXV;
      GRANT CREATE TABLE TO TXV;
      GRANT CREATE PROCEDURE TO TXV;
      GRANT CREATE SEQUENCE TO TXV;
      GRANT CREATE SESSION TO TXV;
      GRANT CREATE TYPE TO TXV;
      GRANT SELECT ANY TABLE TO TXV;
      GRANT ALTER TABLESPACE TO TXV;
      -- 2 Tablespace Quotas for TXV 
      ALTER USER TXV QUOTA UNLIMITED ON TXV;
      ALTER USER TXV QUOTA UNLIMITED ON TXV_ARCHIVE;
      -- 42 Object Privileges for TXV 
        GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON COMMON.ARCHIVE_TABLES TO TXV;
        GRANT ALTER, SELECT ON COMMON.ARC_SEQ TO TXV;
        GRANT ALTER, SELECT ON COMMON.CHANGE_SEQ TO TXV;
        GRANT ALTER, SELECT ON COMMON.ERROR_SEQ TO TXV;
        GRANT ALTER, SELECT ON COMMON.JOB_SEQ TO TXV;
        GRANT EXECUTE ON COMMON.RMAN_MANAGER TO TXV;
        GRANT SELECT, UPDATE ON COMMON.SETTINGS TO TXV;
        GRANT ALTER, SELECT ON COMMON.SQL_SEQ TO TXV;
        GRANT DELETE, INSERT, SELECT, UPDATE ON COMMON.T$ARCHIVE_LOGS TO TXV;
        GRANT DELETE, INSERT, SELECT, UPDATE ON COMMON.T$ARCHIVE_LOGS_DETAIL TO TXV;
        GRANT SELECT ON COMMON.T$CHANGE_LOG TO TXV;
        GRANT SELECT ON COMMON.T$DBA_INDEXES TO TXV;
        GRANT SELECT ON COMMON.T$DBA_PART_TABLES TO TXV;
        GRANT SELECT ON COMMON.T$DBA_TABLES TO TXV;
        GRANT SELECT ON COMMON.T$DBA_TAB_PARTITIONS TO TXV;
        GRANT SELECT ON COMMON.T$DBA_USERS TO TXV;
        GRANT INSERT, SELECT, UPDATE ON COMMON.T$ERRORS TO TXV;
        GRANT SELECT ON COMMON.T$SESSION_LOG TO TXV;
        GRANT DELETE, INSERT, SELECT, UPDATE ON COMMON.T$SQL_LOG TO TXV;
        GRANT EXECUTE ON COMMON.UTILS TO TXV;
        GRANT SELECT ON SYS.DBA_CONSTRAINTS TO TXV;
        GRANT SELECT ON SYS.DBA_DATA_FILES TO TXV;
        GRANT SELECT ON SYS.DBA_FREE_SPACE TO TXV;
        GRANT SELECT ON SYS.DBA_IND_COLUMNS TO TXV;
        GRANT SELECT ON SYS.DBA_INDEXES TO TXV;
        GRANT SELECT ON SYS.DBA_PART_TABLES TO TXV;
        GRANT SELECT ON SYS.DBA_TAB_COLUMNS TO TXV;
        GRANT SELECT ON SYS.DBA_TABLES TO TXV;
        GRANT SELECT ON SYS.DBA_TABLESPACES TO TXV;
        GRANT SELECT ON SYS.DBA_TAB_PARTITIONS TO TXV;
        GRANT SELECT ON SYS.DBA_USERS TO TXV;
        GRANT SELECT ON SYS.T$DBA_CONSTRAINTS TO TXV;
        GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.TPS_SAVE TO TXV WITH GRANT OPTION;
        GRANT SELECT ON SYS.V_$BACKUP_DATAFILE TO TXV;
        GRANT SELECT ON SYS.V_$DATABASE TO TXV;
        GRANT SELECT ON SYS.V_$DATABASE_BLOCK_CORRUPTION TO TXV;
        GRANT SELECT ON SYS.V_$DATAFILE TO TXV;
        GRANT SELECT ON SYS.V_$DATAFILE_HEADER TO TXV;
        GRANT SELECT ON SYS.V_$INSTANCE TO TXV;
        GRANT SELECT ON SYS.V_$PARAMETER TO TXV;
        GRANT SELECT ON SYS.V_$SESSION TO TXV;
        GRANT SELECT ON SYS.V_$TABLESPACE TO TXV;
    I got:
    ORA-01403: no data found

    I don't understand why
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    ORA-01403: no data found
    I don't understand why
    I don't understand either.
    too bad you did not show whole session.
    sb92075
  • Kamran Agayev A.
    Kamran Agayev A. Member Posts: 5,520 Bronze Crown
    Could you please try my scenario, grant all privileges instead of role and try it (and post all output here)?
  • dario-2280378
    dario-2280378 Member Posts: 110
    ok, there was a bug, but on a different side :mad:

    it's after getting ddl...
    tnx to everyone for your efforts
  • I also receive no_data_found error when calling from a named procedure. I added all grants from the role SELECT_CATALOG_ROLE and its subrole HS_ADMIN_SELECT_ROLE

    select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to <schema name>;' from role_tab_privs

    where role = 'SELECT_CATALOG_ROLE' or role = 'HS_ADMIN_SELECT_ROLE';

    But it didn't help (I won't paste the result of this query, because it returns >2000 lines).

    What else could be necessary?

This discussion has been closed.