OraFAQ Forum: Security » GRANT ALL on a schema

Home » RDBMS Server » Security » GRANT ALL on a schema

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

GRANT ALL on a schema [message #182091]

Wed, 12 July 2006 23:51

gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen

Senior Member

Hi,

I have a schema PRODOBJ holding many objects(all types). now i want to grant all permission on all objects to another user called 'appuser'.

Any idea,how to?

Report message to a moderator

Re: GRANT ALL on a schema [message #182096 is a reply to message #182091]

Thu, 13 July 2006 00:38

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As far as I know, there's no simple way to do that; "GRANT ALL ON schema TO another_user" doesn't exist. Therefore, you should grant all objects separately.

In order not to type it manually, you could create a script - spool result of a query which selects data from the data dictionary and create GRANT statements automatically. After it is done, run the script you created.

Report message to a moderator

Re: GRANT ALL on a schema [message #183613 is a reply to message #182091]

Fri, 21 July 2006 09:55

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

littlefoot is correct. Spooling/editing/running it the way to go.

You can try a system grant as PROBOBJ or maybe sys (grant select any table to appuser;)

I usually create a public synonym and grant object privs for the synonym to a user or role.

in sqlplus as PROBOBJ;
spool synonym.txt
set pagesize 0
set linesize 100
select 'create public synonym ' || table_name || ' for PROBOBJ.' || table_name || ' to appuser;' where owner = 'PROBOBJ' order by table_name;
exit sqlplus
edit synonym.txt
run synonym.txt

Then
spool grant.txt
set pagesize 0
set linesize 100

select 'grant <object priv> on PROBOBJ.' || table_name || ' to appuser;" from dba_tables where owner = 'PROBOBJ' order by table_name;
exit sqlplus
edit grant.txt
run grant.txt in sqlplus

Report message to a moderator

Previous Topic:	ORACLE NOT AVAILABL PLEASE HELP
Next Topic:	Date format in Oracle alert file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 18 04:01:23 CST 2025