Prohibit the User from viewing Users_ & All_ dictionary Views [message #457934] |
Wed, 26 May 2010 08:16 |
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 |
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 |
|
Michel Cadot
Messages: 68729 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 |
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
|
|
|
|
|
|