schema access [message #62899] |
Mon, 23 August 2004 04:28 |
shyam
Messages: 25 Registered: January 2001
|
Junior Member |
|
|
i want to give a privilege to a user to select only to a
particular schemas all object.how can i accomplish this.Not one schemas particular objects.
pls hlp
soumya
|
|
|
Re: schema access [message #62902 is a reply to message #62899] |
Mon, 23 August 2004 04:58 |
Frank Naude
Messages: 4589 Registered: April 1998
|
Senior Member |
|
|
Hi,
One cannot do what you want to with a single grant statement. Best would be to generate a script to grant access per object. Look at this example:
SQL> SELECT 'grant select on '||owner||'.'||object_name||' to scott;' "COMMAND"
2 FROM dba_objects
3 WHERE object_type IN ('TABLE', 'VIEW')
4 AND owner IN ('HR', 'SH', 'OE') -- List schema names here
5 /
COMMAND
--------------------------------------------------------------------------------
grant select on HR.REGIONS to scott;
grant select on HR.COUNTRIES to scott;
grant select on HR.LOCATIONS to scott;
grant select on HR.DEPARTMENTS to scott;
grant select on HR.JOBS to scott;
...
Best regards.
Frank
|
|
|
Re: schema access [message #62903 is a reply to message #62902] |
Mon, 23 August 2004 06:59 |
Carl
Messages: 3 Registered: May 2002
|
Junior Member |
|
|
I am looking to do the same thing. I am a data architect taking a proactive approach to finding a solution to suggest to a DBA.
Currently, when I reverse-engineer a model, the DBA has to come by my desk and log-in with the DBA password. I am hoping to avoid this in the future since sometimes they are too busy or off-site working on a project. To reverse-engineer the databases, I need access to everything in the database including: tables, views, triggers, procedures/functions, packages, storage objects, object types, and synonyms.
Can this be done with a permission or role?
If not, can the script you provided be adjusted to include everything in the database?
Thanks for you help,
the Cat
|
|
|
|
Re: schema access [message #62906 is a reply to message #62902] |
Mon, 23 August 2004 12:44 |
shyam
Messages: 25 Registered: January 2001
|
Junior Member |
|
|
hi frank i tried the dynamic sql its not working
so i created a procedure to get the count and put in aloop and used with dynamic sql then its working
thnks
|
|
|
Re: schema access [message #62925 is a reply to message #62905] |
Tue, 24 August 2004 12:14 |
Carl
Messages: 3 Registered: May 2002
|
Junior Member |
|
|
No, the data modeling tool (ER/Studio) does not "see" all the structures with the select/execute_catalog_role. HAve any other ideas?
|
|
|
Re: schema access [message #62927 is a reply to message #62925] |
Tue, 24 August 2004 13:07 |
Yong Huang
Messages: 5 Registered: August 2004
|
Junior Member |
|
|
That's strange. Can you use SQL*Plus to login the database as that user (ER/Studio) and paste the result of these queries:
select * from session_privs;
select * from session_roles;
If any role shown is not standard, please show
select * from dba_sys_privs where grantee = '&thatrole'
If the role contains other roles, you know what to show.
|
|
|