RE: grants to schema query
Privileges have to be granted one object at a time. My preferred method is to grant the privileges to a role and then grant the role to the user. Then if you need to do the same to another user, you just grant them the role also. > -Original Message- > From: O'Neill, Sean [mailto:[EMAIL PROTECTED]] > Sent: Thursday, August 02, 2001 11:01 AM > To: Multiple recipients of list ORACLE-L > Subject: grants to schema query > > > 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: Page, Bruce 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).
RE: grants to schema query
You have to do them individually, but you can use SQL to write the script, as follows (use SQL*Plus to log in to Oracle as the schema owner): set pages 0 feedb off trimspool on lines 200 wrap off select 'grant update on '||table_name||' to ;' from user_tables spool grantem.sql / spool off @grantem Paul Baumgartel MortgageSight Holdings, LLC [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 02, 2001 11: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: Paul Baumgartel 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).
Re: grants to schema query
as owner: spool /tmp/update_all.spl select 'grant update on '||table_name||' to user;' from user_tables; spool off @/tmp/update_all.spl joe >>> [EMAIL PROTECTED] 08/02/01 11:01AM >>>Hope there is not an obvious answer to this. I want to grant UPDATE privto all objects in a schema to a user. Do I need to grant to each object orcan I somehow wildcard all the objects?. What are my option(s)?. I'dappreciate example grant statements!Sean :)Rookie Data Base AdministratorOracle 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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: grants to schema query
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).
Re: grants to schema query
grant to each object ... but you can sorta kinda wildcard it as follows:
select 'grant update on '||owner|'.'||object_name||' to ;'
from dba_objects where owner='' and
object_type in ('TABLE','VIEW','SEQUENCE',);
spool to a file, then run the file as either the object owner, or as someone
who has been granted update with admin
>From: "O'Neill, Sean" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: grants to schema query
>Date: Thu, 02 Aug 2001 07:01:15 -0800
>
>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).
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
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).
RE: grants to schema query
Write a script that dynamically builds your script to create the grants. Dynamically building scripts is very important in making your life much easier. The data dictionary is your friend. -Original Message- Sent: Thursday, August 02, 2001 8: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: Kimberly Smith 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).
RE: grants to schema query
I think you need to something like CREATE ROLE upd_privs; GRANT UPDATE ON table1 TO upd_privs; GRANT UPDATE ON tableN TO upd_privs; GRANT upd_privs TO username; Rick -Original Message- Sent: Thursday, August 02, 2001 11: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: Cale, Rick T (Richard) 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).
