Re: Insufficiente privileges querying my own views...
- From: Sunil Bhola <oraclebhola@xxxxxxxxx>
- To: joseluis_delgado@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 25 Aug 2005 01:34:16 -0700 (PDT)
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@xxxxxxxxx> 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
--
//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
Other related posts:
