Results 1 to 3 of 3

Thread: privs confusion

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: privs confusion

    Just when you think you know...

    I have the following VIEW defined:
    Code:
    CREATE OR REPLACE VIEW COMMON.SHR_IMD_SCHEMA_V
    as
    select grantee as imd_schema
    from   dba_role_privs 
    where  granted_role = 'OWNER_ROLE';
    /
    GRANT SELECT ON  COMMON.SHR_IMD_SCHEMA_V TO PUBLIC;
    /
    In our DEV environment, our web user acct can access the data

    Code:
    SQL> connect online_usr@develop123
    Enter password: ***********
    Connected.
    SQL> select count(*) from COMMON.shr_imd_schema_v;
    
      COUNT(*)
    ----------
            23
    but that's not the case in test
    Code:
    SQL> connect online_usr@test123
    Enter password: ***********
    Connected.
    SQL> /
    select count(*) from COMMON.shr_imd_schema_v
                                *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    I thought it might be due to rights to the underlying DBA_ROLE_PRIVS view, but the rights are the same in develop123 & test123
    Code:
    SQL> connect forbesc@develop123
    Enter password: *******
    Connected.
    SQL> select grantee, privilege 
      2  from dba_tab_privs 
      3  where table_name = 'DBA_ROLE_PRIVS';
    
    GRANTEE                        PRIVILEGE
    ------------------------------ --------------
    OPER                           SELECT
    COMMON                         SELECT
    WKSYS                          SELECT
    CTXSYS                         SELECT
    SELECT_CATALOG_ROLE            SELECT
    
    6 rows selected.
    
    SQL> select grantee, privilege 
      2  from dba_tab_privs 
      3  where table_name = 'SHR_IMD_SCHEMA_V';
    
    GRANTEE                        PRIVILEGE
    ------------------------------ --------------
    PUBLIC                         SELECT
    vs test123
    Code:
    SQL> connect forbesc@test123
    Enter password: *******
    Connected.
    SQL> select grantee, privilege 
      2  from dba_tab_privs 
      3  where table_name = 'DBA_ROLE_PRIVS';
    
    GRANTEE                        PRIVILEGE
    ------------------------------ -------------
    SELECT_CATALOG_ROLE            SELECT
    CTXSYS                         SELECT
    WKSYS                          SELECT
    COMMON                         SELECT
    OPER                           SELECT
    
    SQL> select grantee, privilege 
      2  from dba_tab_privs 
      3  where table_name = 'SHR_IMD_SCHEMA_V';
    
    GRANTEE                        PRIVILEGE
    ------------------------------ -------------
    PUBLIC                         SELECT

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Here's some further information that might help someone, but is still keeping me confused:

    Another view in the COMMON schema which has the SHR_IMD_SCHEMA_V embedded within it still works

    Code:
    CREATE OR REPLACE VIEW COMMON.SHR_IMD_TABLE_V
    (IMD_SCHEMA, TABLE_NAME)
    AS 
    select owner as imd_schema, table_name  
    from all_tables
    where tablespace_name is not null and
    	  table_name not like '%REPORT_WORK%' and
    	  owner in  (select imd_schema 
                      from SHR_IMD_SCHEMA_V)
    Code:
    SQL> connect online_usr@test123
    Enter password: ***********
    Connected.
    SQL> select count(*) from COMMON.shr_imd_table_v;
    
      COUNT(*)
    ----------
           397
    
    SQL> select count(*) from COMMON.SHR_IMD_SCHEMA_V;
    select count(*) from COMMON.SHR_IMD_SCHEMA_V
                                *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    What is going on?
    -Chuck

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    ... sigh ...
    The COMMON schema in develop123 had the SELECT ANY DICTIONARY system privilege.

    And, although I had granted SELECT on DBA_ROLE_PRIVS to COMMON in TEST, it wasnt enough. I had to actually grant SELECT on DBA_ROLE_PRIVS to COMMON with GRANT OPTION in order for anyone else to use the VIEW.

    -Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •