Thanks Bill - not good news, then!
Trouble is, I'm setting up a bunch of "read-only" userids which can see (but not touch) tables in several other schemas. So instead of doing all the grants with a single connect (as SYS), evidently I'll have to connect as each schema-owner in turn, and issue the grants for that schema's objects.
Or you could use the procedure "trick" I mentioned - it's simple to put together what you want to do in a script; the following will run as SYS:
SQL> create procedure scott.grant_privs
2 as
3 begin
4 execute immediate 'grant select on emp to bill';
5 end;
6 /
Procedure created.
SQL> begin
2 scott.grant_privs;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> drop procedure scott.grant_privs;
Procedure dropped.
Do you think Oracle keep rationing out these long-standing wish-list items with each successive release, in order to encourage us to take up those releases?
Still waiting for DEFAULT_INDEX_TABLESPACE :-)
- Bill.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
Oh well, thanks again for the info!
Regards,
Paul
- -----Original Message-----
- From: Bill Buchan [mailto:[EMAIL PROTECTED]]
- Sent: 12 September 2003 12:15
- To: Multiple recipients of list ORACLE-L
- Subject: Re: Granting object privileges as SYS
- At 02:49 12/09/2003 -0800, you wrote:
- Hi,
- I'm trying to grant SELECT privileges on some tables in schema X to userid Y. I'm currently connected as SYS. But when I try typing
- GRANT SELECT ON X.MYTABLE TO Y;
- ...I get an ORA-01031 error - Insufficient Privileges.
- Now I thought SYS had all the necessary privileges to do this - i.e. granting access on user X's tables to user Y. But it appears not! Am I missing something here, or is this another one of those loveable Oracle quirks which enrich our lives so much?
- Oh, by the way, it's Oracle 8.1.7.
- That's the problem: you can only do this in 9i.
- In 8i SYS cannot grant privileges on other user's tables. The trivial work-around for 8i is, of course, just to log in as X to grant privileges. The slightly less trivial work-around (if you don't want to log in as X) is for SYS to create a procedure in schema X which grants the required table privileges (execute immediate) to Y and then run it (and drop it). Not my idea - can't remember who first suggested it.
- HTH
- - Bill.
- Regards,
- Paul Vincent
- DBA
- University of Central England
