Re: Insufficiente privileges querying my own views...

From: Sunil Bhola <oraclebhola_at_yahoo.com>
Date: Thu, 25 Aug 2005 01:34:16 -0700 (PDT)
Message-ID: <20050825083417.23325.qmail@web31610.mail.mud.yahoo.com>

Hi Luis,  

Please check this exercies :-  

Moral -  

SQL> create role emp_select;
Role created.  

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: 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.  

SQL> connect text/text
Connected.

SQL> create view emp_select as select *from scott.emp; View created.
SQL>   Hope I cleared your doubt :-)  

QUERIES in Oracle, Feel free to Join:  

http://groups.yahoo.com/group/oracle_expert/  

Regards,

Sunil Bhola

Oracle_Expert, Moderator    

Jose Luis Delgado <joseluis_delgado_at_yahoo.com> wrote: DBAs...

Sorry if this is a silly question, my brain is not thinking clearly today...

1.- UserA gives SELECT privileges on his tables to UserB trough a role RoleA.

2.- UserB creates ViewA, querying the UserA tables.

3.- UserB creates viewB, which query ViewA, in his own schema.

He gets:
ORA-01031 Insufficient privileges

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
--

http://www.freelists.org/webpage/oracle-l Received on Thu Aug 25 2005 - 03:36:19 CDT