RE: grants to schema query

From: Kevin Lange <kgel_at_ppoone.com>
Date: Thu, 02 Aug 2001 22:49:34 -0700
Message-ID: <F001.0035DC08.20010802091633@fatcity.com>

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:

connect dbaid/password_at_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_at_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: sean.oneill_at_organon.ie [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: Sean.ONeill_at_organon.ie

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: ListGuru_at_fatcity.com (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: kgel_at_ppoone.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------