If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Select Any Dictionary??

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,498
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!)
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Location: St.Louis,MO
Posts: 120
Quote:
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...

Last edited by The_Duck; 12-10-03 at 17:41.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 22
Re: Select Any Dictionary??

Quote:
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>
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On