-
ORACLE 8.1.7
=========
I created 2 users : T1 and T2.
I created a role R1. R1 is assigned to T2.
I created a table X under T1.
I granted SELECT on X to R1.
Connected as T2.
I can do - "SELECT * FROM T1.X" - okay.
I can do - "CREAT TABLE TB_X AS SELECT * FROM T1.X" - okay.
Why cann't I do "CREATE VIEW VW_X AS SELECT * FROM T1.X" ?????? I get ORA-942.
If T1 grants SELECT on X directly to T2, the view gets created successfully !!
What is the difference between :
granting a privilege to a role and then granting that role to a user
AND
Directly granting the privilege to user ?
-
Granting privileges to roles are better, than to users, as this is easier to maintain.
If 20 users are to be granted a privilege, create a role and grant the privilege to the role, now grant the role to the 20 users. At a later date, when u want to grant another privilege for an object to the 20 users, you can do this in one shot by granting the privilege to the role already assigned to the 20 users. The same for revoking of privs.
But if u were to grant privs to users, u can imagine the overhead in maintaining the same!
Concerning the example cited, to create a view from an object in another schema where u have select privs throu' a role, it is not possible! You need to have privs explicitly (not throu a role) for the object in the other schema to create a view from it. Refer to view doc of oracle which states this.
So, in this case, u need to grant the priv to the user explicitly and not to the role, if u want to be able to create views.
HTH http://www.dbasupport.com/forums/images/icons/icon6.gif
