Home » RDBMS Server » Security » schema access
schema access [message #62899] Mon, 23 August 2004 04:28 Go to next message
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 Go to previous messageGo to next message
Frank Naude
Messages: 4570
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 Go to previous messageGo to next message
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 #62905 is a reply to message #62903] Mon, 23 August 2004 10:47 Go to previous messageGo to next message
Yong Huang
Messages: 5
Registered: August 2004
Junior Member
Can't he grant select_catalog_role to you?
Re: schema access [message #62906 is a reply to message #62902] Mon, 23 August 2004 12:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: password file
Next Topic: Recovering the sys password
Goto Forum:
  


Current Time: Fri Dec 03 07:23:35 CST 2021