Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Granting select on other users schema

Re: Granting select on other users schema

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/11
Message-ID: <349047db.15527457@inet16>#1/1

On 10 Dec 1997 09:15:34 GMT, smulcahy_at_soggey.ilo.dec.com wrote:

>Hi,
>
>As a novice DBA, I'm currently stuck on a permissions problem. I'm not
>sure if this is the product of my unfamiliarity with the "Oracle way" or
>because I'm missing something simple.
>
>
>Basically, I have a user foo. I want to give select access on certain
>tables within foo's schema to user john. I have tried the following
>
>grant select on foo.sometable to john
>
>But have gotten the error "insufficient privileges". Should I be getting
>this error while running the above statement as sys or system? Maybe I'm
>too used to the unix super-user mode of thinking.
>
>I know I can connect as foo and then grant the privileges but I'd prefer
>to do it as system or sys or some other dba user.
>

When FOO granted priveleges to John, FOO should grant with the "with grant option" so John not only has the privelege but John has the privelege to give that privelege as well. For example using SCOTT as your FOO and tkyte as your john:

SQL> connect scott/tiger
Connected.
SQL> grant select on emp to tkyte;
Grant succeeded.  

SQL> connect tkyte/tkyte
Connected.
SQL> grant select on scott.emp to public; grant select on scott.emp to public

                      *

ERROR at line 1:
ORA-01031: insufficient privileges    

SQL> connect scott/tiger
Connected.
SQL> grant select on emp to tkyte with grant option; Grant succeeded.  

SQL> connect tkyte/tkyte
Connected.
SQL> grant select on scott.emp to public; Grant succeeded.

So, by adding the 'with grant option' the second time around, we gave the grantee the ability to be the grantor....

>Thanks for your help,
>
>-stephen
>
>
>
>----
>Stephen Mulcahy, Digital Equipment International BV, Galway, Ireland.
> mailto:smulcahy_at_soggey.ilo.dec.com
> Disclaimer: All opinions expressed are purely my own.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Dec 11 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US