Try
1. creating a role for the updates.
2. running a query against the dba_tables table that will generate all the
grants neeeded to the role.
3. Spool that query to a file.
4. Run the resultant file as the schema that owns the tables you want access
granted on.
For example:
-- Log in as your friendly DBA and create the update role called upd_role.
connect dbaid/password@database
Create role upd_role;
-- Grant the role to the user who needs the privledges
grant upd_role to USER1;
-- This will generate a script called C:\grant.sql that contains all
statements neccessary to grant update privledges on the tables belonging to
'SCHEMA1' to the Update Role, upd_role.
Spool C:\grant.sql;
select 'grant update on '||owner||'.'||table_name||' to upd_role;'
from dba_tables
where owner = 'SCHEMA1';
Spool off;
-- connect to SCHEMA1 and run the script
connect schema1/password@database
@c:\grant.sql
You could also bypass the use of a role and grant directly to the user.
But, if you use a role you can give the authorities to any user without
having to regenerate the script.
REMEMBER... If you add new objects to SCHEMA1 you need to grant them as
well. This will not automatically get all objects for now until the
database dies.
-----Original Message-----
Sent: Thursday, August 02, 2001 10:01 AM
To: Multiple recipients of list ORACLE-L
Hope there is not an obvious answer to this..... I want to grant UPDATE priv
to all objects in a schema to a user. Do I need to grant to each object or
can I somehow wildcard all the objects?. What are my option(s)?. I'd
appreciate example grant statements!
Sean :)
Rookie Data Base Administrator
Oracle 7.3.3, 8.0.5, 8.1.7 - NT, W2K
[0%] OCP Oracle8i DBA
[0%] OCP Oracle9i DBA
-------------------------------- ------------
Organon (Ireland) Ltd.
E-mail: [EMAIL PROTECTED] [subscribed: Digest Mode]
Visit: http://groups.yahoo.com/group/Oracle-OCP-DBA
"Nobody loves me but my mother... and she could be jivin' too." - BB King
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: O'Neill, Sean
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
