Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: View creation error on a table with grant through a role?
Hi,
>From the online generic doc's version 8.1.7
Oracle avoids the dependencies on privileges received by way of roles by restricting the use of specific privileges in certain DDL statements. The following rules outline these privilege restrictions concerning DDL statements:
All system privileges and schema object privileges that permit a user to perform a DDL operation are usable when received through a role.
Examples:
System Privileges: the CREATE TABLE, CREATE VIEW and CREATE PROCEDURE privileges.
Schema Object Privileges: the ALTER and INDEX privileges for a table.
Exception: The REFERENCES object privilege for a table cannot be used to define a table's foreign key if the privilege is received through a role.
All system privileges and object privileges that allow a user to perform a DML operation that is required to issue a DDL statement are not usable when received through a role.
Example:
A user who receives the SELECT ANY TABLE system privilege or the SELECT object privilege for a table through a role can use neither privilege to create a view on another user's table.
The following example further clarifies the permitted and restricted uses of privileges received through roles:
Example: Assume that a user is:
Granted a role that has the CREATE VIEW system privilege
Granted a role that has the SELECT object privilege for the EMP table, but the user is indirectly granted the SELECT object privilege for the EMP table
Directly granted the SELECT object privilege for the DEPT table
Given these directly and indirectly granted privileges:
The user can issue SELECT statements on both the EMP and DEPT tables.
Although the user has both the CREATE VIEW and SELECT privilege for the EMP table through a role, the user cannot create a usable view on the EMP table, because the SELECT object privilege for the EMP table was granted through a role. Any views created will produce errors when accessed.
The user can create a view on the DEPT table, because the user has the CREATE VIEW privilege through a role and the SELECT privilege for the DEPT table directly.
HTH Jack
Szecsy Tamas <tszecsy_at_GEOMETRI To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> A.hu> cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: RE: View creation error on a table with grant through a role? root_at_fatcity.com 04-04-2002 15:08 Please respond to ORACLE-L
:-O Thanks. Its funny. Could some one please point me to some online docs (OTN)? Just to see the logic behind this - for me unwanted - feature?
Tamas
-----Original Message-----
Sent: Thursday, April 04, 2002 2:14 PM
To: Multiple recipients of list ORACLE-L
Hi
To create a view or procedure the rights have to be granten to the person
directly and not through a role
So yes, quite normal behaviour (maybe not wanted)
Jack
Szecsy Tamas <tszecsy_at_GEOMETRI To: Multiplerecipients
A.hu> cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: View creationerror
root_at_fatcity.com 04-04-2002 13:28 Please respond to ORACLE-L
Hi,
Is it normal that
connect x/x
select id, name
from y_schema.table1;
execuites fine, but
connect x/x
create or rpelace view v_myview( id, name) as
select id, name
from y_schema.table1;
fails? User X has grants on y_schema's table1 through a role only.
TIA,
Tamas Szecsy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Szecsy Tamas
INET: tszecsy_at_GEOMETRIA.hu
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen.
Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden.
If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies.
In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack van Zanen
INET: nlzanen1_at_EY.NL
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen.
Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden.
If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies.
In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack van Zanen
INET: nlzanen1_at_EY.NL
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Apr 04 2002 - 08:28:33 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message