Home » RDBMS Server » Security » what is difference if user got privilege direct or by role?
what is difference if user got privilege direct or by role? [message #27759] Tue, 28 October 2003 01:12 Go to next message
barwas
Messages: 19
Registered: October 2003
Junior Member
there are:-
-user1 has table employees and role named approle
-user2 has create view privilege

###situation one:

-user1:
sql> grnat select on employees to approle;
sql> grant approle to user2;

-user2:
sql> set role approle;
role set.

sql> create view emp_view as select * from user1.employees;
create view emp_view as select * from user1.employees
*
ERROR at line 1:
ORA-01031: insufficient privileges

###situation two:

-user1:
sql> grnat select on employees to user2;

-user2:
sql> create view emp_view as select * from user1.employees;
view created

### so what is difference if user2 got select on employees privilege directly or by approle?
thank you
Re: what is difference if user got privilege direct or by role? [message #27761 is a reply to message #27759] Tue, 28 October 2003 01:46 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Well, in order to create a view, a user must own all objects referenced in the view or he must have had the access rights directly. This is a deliberate concept to ensure security. This is also said in the Oracle SQL Reference:

The owner of the schema containing the view must have the privileges necessary
to either select, insert, update, or delete rows from all the tables or views on
which the view is based.
The owner must be granted these privileges directly, rather than through a role.


Hth,
MHE
Previous Topic: checking user name and password
Next Topic: system privileges and roles
Goto Forum:
  


Current Time: Sat Aug 08 19:24:51 CDT 2020