Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    MN
    Posts
    10

    Select Any Dictionary??

    Alright - I must be doing something wrong.

    I want user "test1" to be able to look at the entire data dictionary. SYS.v$sess_io, v$instance... - the entire thing.

    I connect as SYS as sysdba and grant SELECT ANY DICTIONARY to test1

    grant successful!

    When I connect as test1, I still can't see the SYS tbls.
    The test1 user currently has the CONNECT role.


    Is it something to do with the SELECT_CATALOG_ROLE role? I've tried this too.

    THX

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,647
    Rhetorical questions....

    Why do folks who are looking for assistance frequently neglect to specify the actual Oracle s/w and VERSION being used?
    Sometimes it matters.
    Why do folks paraphrase what is happening; instead of ACTUALLY showing us EXACTLY what they did & EXACTLY how the s/w responded?

    Since you report that user TEST1 can't see some objects owned by SYS,
    some possibilities are that user TEST1 does not have his eyes open, is working a room with no lighting, or has their monitor turned off.
    Your On Your Own (YOYO!)

  3. #3
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    Originally posted by anacedent
    Rhetorical questions....

    Why do folks who are looking for assistance frequently neglect to specify the actual Oracle s/w and VERSION being used?
    Sometimes it matters.
    Why do folks paraphrase what is happening; instead of ACTUALLY showing us EXACTLY what they did & EXACTLY how the s/w responded?

    Since you report that user TEST1 can't see some objects owned by SYS,
    some possibilities are that user TEST1 does not have his eyes open, is working a room with no lighting, or has their monitor turned off.
    Your On Your Own (YOYO!)
    grant dba

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    I think Anacedent means something like this:
    PHP Code:
    17:31:40 kop1:stagedemoselect from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.4.0 64bit Production
    PL
    /SQL Release 9.2.0.4.0 Production
    CORE    9.2.0.3.0       Production
    TNS 
    for SolarisVersion 9.2.0.4.0 Production
    NLSRTL Version 9.2.0.4.0 
    Production

    Elapsed
    00:00:00.00

    17
    :31:52 kop1:stagedemoshow user
    USER is 
    "STAGEDEMO"

    17:32:27 kop1:stagedemoselect count(*) from dba_tables;
    select count(*) from dba_tables
                         
    *
    ERROR at line 1:
    ORA-00942table or view does not exist


    Elapsed
    00:00:00.00
    17
    :32:37 kop1:stagedemoconnect system/password@kop1
    Connected
    .

    17:32:40 kop1:stagedemoshow user
    USER is 
    "SYSTEM"

    17:32:48 kop1:stagedemogrant select any dictionary to stagedemo;

    Grant succeeded.

    Elapsed00:00:00.02
    17
    :34:10 kop1:stagedemoconnect stagedemo/password@kop1
    Connected
    .
    17:34:15 kop1:stagedemo>  select count(*) from dba_tables;

      
    COUNT(*)
    ----------
          
    1309

    Elapsed
    00:00:01.03
    17
    :34:26 kop1:stagedemo
    Last edited by The_Duck; 12-10-03 at 18:41.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2003
    Posts
    22

    Re: Select Any Dictionary??

    Originally posted by ericksob
    Alright - I must be doing something wrong.

    I want user "test1" to be able to look at the entire data dictionary. SYS.v$sess_io, v$instance... - the entire thing.

    I connect as SYS as sysdba and grant SELECT ANY DICTIONARY to test1

    grant successful!

    When I connect as test1, I still can't see the SYS tbls.
    The test1 user currently has the CONNECT role.


    Is it something to do with the SELECT_CATALOG_ROLE role? I've tried this too.

    THX

    Below is my test, please refer:

    SQL> connect system@3021
    Enter password:
    Connected.
    SQL> create user test identified by test;

    User created.

    SQL> grant select any dictionary to test;

    Grant succeeded.

    SQL> grant connect to test;

    Grant succeeded.

    SQL> connect test/test@3021
    Connected.
    SQL> select count(*)
    2 from v$session;

    COUNT(*)
    ----------
    344

    SQL> select *
    2 from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
    PL/SQL Release 9.2.0.3.0 - Production
    CORE 9.2.0.3.0 Production
    TNS for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production
    NLSRTL Version 9.2.0.3.0 - Production

    SQL> connect system@3021
    Enter password:
    Connected.
    SQL> drop user test;

    User dropped.

    SQL>

  6. #6
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    You might want to check your O7_DICTIONARY_ACCESSIBILITY parameter in the init.ora... here is what the Oracle docs have to say:

    Code:
    Description    : Used primarily for migration from Oracle7 to Oracle8i. If TRUE, SYSTEM privileges, such as SELECT ANY TABLE, do not restrict access to objects in the SYS schema (Oracle7 behavior). If FALSE, users can only access objects in the SYS schema when granted the SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, or SELETE_CATALOG_ROLE. 
    
    Range of Values: TRUE | FALSE 
    Default Value  : TRUE
    My guess would be that you need to have SELECT_CATALOG_ROLE granted to your test user. Also, note the underscores... that is the name of the role to grant so it should be:

    GRANT SELECT_CATALOG_ROLE TO test_user;

    JoeB

Posting Permissions

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