SQL> grant select on emp to emp_select; Grant succeeded.
SQL> connect text/text Connected.
SQL> ed Wrote file afiedt.buf 1 create view text_emp_select 2* as select *from scott.emp SQL> / as select *from scott.emp * ERROR at line 2: ORA-01031 (See ORA-01031.ora-code.com): insufficient privileges ********************************** But when we directly grant the object role ie without role, the view will be created without any error :-
SQL> connect scott/tiger Connected.
SQL> drop role emp_select; Role dropped.
SQL> grant select on emp to text; Grant succeeded.
SQL> connect text/text Connected.
SQL> create view emp_select as select *from scott.emp; View created.
But when we grant the ALTER ANY ROLE to TEXT user he/she can create a view:- SQL> ed Wrote file afiedt.buf 1* grant alter any role to text SQL> / Grant succeeded.
Of course, this is because of he cannot query the tables (through the role, right?) that the ViewA is pointing to...
the point here is: What could be the best solution to give UserB enough privileges to query the view, without having a mess with privileges??
Thanks in advance! JL
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
-- http://www.freelists.org/webpage/oracle-l
QUERIES in Oracle, Feel free to Join:
http://groups.yahoo.com/group/oracle_expert/
Regards, Sunil Bhola Oracle_Expert, Moderator
-- ---- ---- ---- ---- ---- ----- Start your day with Yahoo! - make it your home page <DIV> <DIV>Hi Luis,</DIV> <DIV> </DIV> <DIV>Please check this exercies :-</DIV> <DIV> </DIV> <DIV>Moral - </DIV> <DIV> </DIV> <DIV>SQL> create role emp_select;</DIV> <DIV>Role created.</DIV> <DIV> </DIV> <DIV>SQL> grant select on emp to emp_select;</DIV> <DIV>Grant succeeded.</DIV> <DIV> </DIV> <DIV>SQL> connect text/text<BR>Connected.</DIV> <DIV> </DIV> <DIV>SQL> ed<BR>Wrote file afiedt.buf</DIV> <DIV> 1 create view text_emp_select<BR> 2* as select *from scott.emp<BR>SQL> /<BR>as select *from scott.emp<BR> *<BR>ERROR at line 2:<BR>ORA-01031 (See ORA-01031.ora-code.com): insufficient privileges</DIV> <DIV><STRONG>**********************************</STRONG><BR><STRONG>But when we directly grant the object role ie without role, the view will be created without any error :-</STRONG></DIV> <DIV> </DIV> <DIV>SQL> connect scott/tiger<BR>Connected.</DIV> <DIV><BR>SQL> drop role emp_select;</DIV> <DIV>Role dropped.</DIV> <DIV> </DIV> <DIV>SQL> grant select on emp to text;</DIV> <DIV>Grant succeeded.</DIV> <DIV> </DIV> <DIV>SQL> connect text/text<BR>Connected.<BR></DIV> <DIV>SQL> create view emp_select as select *from scott.emp;</DIV> <DIV>View created.</DIV> <DIV><BR>But when we grant the ALTER ANY ROLE to TEXT user he/she can create a view:-</DIV> <DIV>SQL> ed<BR>Wrote file afiedt.buf</DIV> <DIV> 1* grant alter any role to text<BR>SQL> /</DIV> <DIV>Grant succeeded.</DIV> <DIV> </DIV> <DIV>SQL> connect text/text<BR>Connected.<BR></DIV> <DIV>SQL> create view emp_select as select *from scott.emp;</DIV> <DIV>View created.</DIV> <DIV>SQL> </DIV> <DIV> </DIV> <DIV>Hope I cleared your doubt :-)</DIV> <DIV> </DIV> <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><STRONG><B><FONT face=Verdana color=#434343 size=3><SPAN style="FONT-SIZE: 12pt; COLOR: #434343; FONT-FAMILY: Verdana">QUERIES in Oracle, Feel free to Join:</SPAN></FONT></B></STRONG><?xml :namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o :p></P> <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P> <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><STRONG><B><FONT face=Verdana size=3><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: Verdana"><A href="http:/ /groups.yahoo.com/group/oracle_expert/"><FONT color=#434343><SPAN style="COLOR: #434343">http://groups.yahoo.com/group/oracle_expert/</SPAN></FONT></A><FONT color=yellow><SPAN style="COLOR: yellow"> </SPAN></FONT></SPAN></FONT></B>< /STRONG><o:p></o:p></P> <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P> <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><STRONG><B><FONT face=Verdana size=3><SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: Verdana">Regards,</SPAN>< /FONT></B></STRONG><o:p></o:p></P> <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><STRONG><B><FONT face=Verdana color=red><SPAN style="FONT-SIZE: 12pt; COLOR: red; FONT-FAMILY: Verdana">S< /SPAN></FONT></B></STRONG><STRONG><B><FONT face=Verdana size=3><SPAN style="FONT -FAMILY: Verdana">unil <FONT color=red><SPAN style="COLOR: red">B</SPAN></FONT >hola</SPAN></FONT></B></STRONG><o:p></o:p></P> <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><STRONG><B><FONT face=Verdana> <SPAN style="FONT-SIZE: 12pt; FONT-FAMILY: Verdana">Oracle_Expert, Moderator< /SPAN></FONT></B></STRONG><o:p></o:p></P> <P class=MsoNormal style="MARGIN: 0in 0in 0pt"><FONT face=Arial color=navy size =2><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o :p></SPAN></FONT></P> <DIV> </DIV> <DIV><BR><BR><B><I>Jose Luis Delgado <joseluis_delgado@(protected)></I></B> wrote:</DIV> <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER -LEFT: #1010ff 2px solid">DBAs...<BR><BR>Sorry if this is a silly question, my brain is not<BR>thinking clearly today...<BR><BR>1.- UserA gives SELECT privileges on his tables to<BR>UserB trough a role RoleA.<BR><BR>2.- UserB creates ViewA, querying the UserA tables.<BR><BR>3.- UserB creates viewB, which query ViewA, in his own<BR>schema.<BR><BR>He gets:<BR>ORA-01031 (See ORA-01031.ora-code.com) Insufficient privileges<BR><BR>Of course, this is because of he cannot query the<BR>tables (through the role, right?) that the ViewA is<BR>pointing to...<BR><BR>the point here is:<BR>What could be the best solution to give UserB enough<BR>privileges to query the view, without having a mess<BR>with privileges??<BR><BR>Thanks in advance!<BR>JL<BR><BR><BR><BR><BR>__ ____ ____ ____ ____ ____ ____ ____ ____ __ __ ____<BR>Start your day with Yahoo! - make it your home page <BR>http://www .yahoo.com/r/hs <BR><BR>--<BR>http://www.freelists.org/webpage/oracle-l<BR></BLOCKQUOTE></DIV> <BR><BR><DIV> <DIV> <DIV> <DIV> <DIV> <DIV> <DIV> <DIV> <DIV><FONT size=2><FONT size=3><FONT face=verdana color=#434343><STRONG>QUERIES in Oracle, Feel free to Join:</STRONG></FONT></FONT></FONT></DIV> <DIV><STRONG><FONT face=Verdana color=#434343 size=3></FONT></STRONG> < /DIV> <DIV><FONT size=2><FONT size=3><FONT size=+0><FONT face=verdana><STRONG><A href ="http://groups.yahoo.com/group/oracle_expert/"><FONT color=#434343>http:/ /groups.yahoo.com/group/oracle_expert/</FONT></A><FONT color=#ffff00> <BR></FONT ></STRONG></FONT></FONT></FONT></FONT></DIV> <DIV><STRONG><FONT face=verdana></FONT></STRONG> </DIV> <DIV><STRONG><FONT face=verdana>Regards,</FONT></STRONG></DIV> <DIV><FONT face=verdana><FONT size=3><STRONG><FONT color=#ff0000>S</FONT>unil <FONT color=#ff0000>B</FONT>hola</STRONG></FONT></FONT></DIV> <DIV> <DIV><FONT face=verdana size=3><STRONG>Oracle_Expert, Moderator<BR></STRONG>< /FONT></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV><p> <hr size=1> <a href="http://us.rd.yahoo.com/evt=34442/*http://www.yahoo.com/r /hs">Start your day with Yahoo! - make it your home page </a>
