Home » RDBMS Server » Security » GRANT ALL on a schema
GRANT ALL on a schema [message #182091] Wed, 12 July 2006 23:51 Go to next message
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?
Re: GRANT ALL on a schema [message #182096 is a reply to message #182091] Thu, 13 July 2006 00:38 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: GRANT ALL on a schema [message #183613 is a reply to message #182091] Fri, 21 July 2006 09:55 Go to previous message
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

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


Current Time: Thu Mar 28 17:57:13 CDT 2024