Home » RDBMS Server » Server Administration » Prohibit the User from viewing Users_ & All_ dictionary Views (Oracle 10g)
Prohibit the User from viewing Users_ & All_ dictionary Views [message #457934] Wed, 26 May 2010 08:16 Go to next message
imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad
Member
Dear Expert

I have granted connect,resource privilege to a user 'TestU' and have create several synonyms in the schema using the public database link.
What worries me is that the user 'TestU' can view the all_ and users_ dictionary views, which makes him accessible to all public database links and all user specific objects definition.

Is there a way to restrict the user to see only the synonyms and avoid looking at their definitions

Looking for your cooperation


Regards

[Updated on: Wed, 26 May 2010 08:18]

Report message to a moderator

Re: Prohibit the User from viewing Users_ & All_ dictionary Views [message #457942 is a reply to message #457934] Wed, 26 May 2010 08:55 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

SQL> select owner,object_name,objecT_type from dba_objects where objecT_name like 'USER_TABLES%';

OWNER                          OBJECT_NAME                  OBJECT_TYPE
------------------------------ ---------------------------- -------------------
SYS                            USER_TABLES                  VIEW
PUBLIC                         USER_TABLES                  SYNONYM
SYS                            USER_TABLESPACES             VIEW
PUBLIC                         USER_TABLESPACES             SYNONYM

SQL> select owner,object_name,objecT_type from dba_objects where objecT_name like 'ALL_OBJECT%';

OWNER                          OBJECT_NAME                  OBJECT_TYPE
------------------------------ ---------------------------- -------------------
SYS                            ALL_OBJECT_TABLES            VIEW
SYS                            ALL_OBJECTS                  VIEW
PUBLIC                         ALL_OBJECT_TABLES            SYNONYM
PUBLIC                         ALL_OBJECTS                  SYNONYM


ALL_ & USER_ it's public synonym's if you don't want to access test user then you have revoke this objects from public but you may face some side effects

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:582971000346388526

Babu

[Updated on: Wed, 26 May 2010 08:58]

Report message to a moderator

Re: Prohibit the User from viewing Users_ & All_ dictionary Views [message #457946 is a reply to message #457934] Wed, 26 May 2010 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have granted connect,resource privilege to a user 'TestU'

You should not, forget these roles there are for Oracle only, not for you.

Quote:
What worries me is that the user 'TestU' can view the all_ and users_ dictionary views,

Yes, this is by default, don't need to give any privilege to a schema, the schema has ALWAYS access to these views.

Quote:
which makes him accessible to all public database links ... definition.

True.

Quote:
which makes him accessible to ... all user specific objects definition.

Wrong, only those it has some privileges.

Beging able to see a public synonym definition does not grant any access to the object, so why do you care?

Quote:
Is there a way to restrict the user to see only the synonyms and avoid looking at their definitions

No. You can drop ALL_SYNONYMS view but then you are no more supported by Oracle.
What is your actual concern about this?

Regards
Michel

Re: Prohibit the User from viewing Users_ & All_ dictionary Views [message #457947 is a reply to message #457942] Wed, 26 May 2010 09:02 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Instead of revoke public synonysm try to create private views.

SQL> show user
USER is "TEST"
SQL>
SQL> select count(*) from all_tables;

  COUNT(*)
----------
        81

SQL> conn sys/sys as sysdba
Connected.
SQL>
SQL> create view test.all_tables as select * from sys.all_tables where 1=2;

View created.

SQL> conn test/test
Connected.
SQL> select count(*) from all_tables;

  COUNT(*)
----------
         0


Babu

[Updated on: Wed, 26 May 2010 09:03]

Report message to a moderator

Re: Prohibit the User from viewing Users_ & All_ dictionary Views [message #457950 is a reply to message #457947] Wed, 26 May 2010 09:06 Go to previous messageGo to next message
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why on earth would you want to do that?
Re: Prohibit the User from viewing Users_ & All_ dictionary Views [message #457951 is a reply to message #457950] Wed, 26 May 2010 09:13 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

My bad; Apologies

-Babu
Re: Prohibit the User from viewing Users_ & All_ dictionary Views [message #457952 is a reply to message #457934] Wed, 26 May 2010 09:20 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>What worries me is that the user 'TestU' can view the all_ and users_ dictionary views, which makes him accessible to all public database links and all user specific objects definition.

Why is user TESTU allowed access to client software that allows ad hoc SQL queries?
Previous Topic: TNS-V1-V3 in v$session
Next Topic: Question about viewing "tree tablespace"
Goto Forum:
  


Current Time: Sun May 19 12:24:51 CDT 2024