Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Create Views with object-privs from Roles

Create Views with object-privs from Roles

From: Loy&Hutz <loyhutz_at_dinx.de>
Date: 1 Oct 1998 18:01:19 -0200
Message-ID: <01bded55$6c9d67a0$6d6464c3@kom>


Create Views with object-privs from Roles

I like to create views in Schema B on Table in Schema A

I give User B (having create any view-privilege) object privileges to Table in Schema A
(GRANT SELECT, INSERT, UPDATE, DELETE ON SchemaA.TableA TO Userb WITH GRANT
OPTION)
plus privilege "references" (although I donīt know, what this means)

I create the view

all ok.

But I have a lot of users like B. So I like to use a role

I create a role with exact the same object privileges
(GRANT SELECT, INSERT, UPDATE, DELETE ON SchemaA.TableA TO RoleX WITH ADMIN
OPTION) I granted the role to user B

I can access the tables (select * from A.T_Taetig2 WHERE M1 = 34) BUT:

Create view B.Test3 As select * from A.T_Taetig2 WHERE M1 = 34                                                                  

Causes (german):
ORA-00942: Tabelle oder View nicht vorhanden

english: Table or view does not exists.

Are object privileges via roles not valid for "create view" ?

But this sentences in the ORA-help indicates the opposite: "If you revoke an object privilege on an object from a user whose schema contains a view on that object, Oracle invalidates the view"

any idea ?

Greetings Sven Kunze

ENVIRONMENT:
PROBLEMS / TESTED WITH Visual Basic 4.0 16 Bit / Access 2.0 / Access 97 - Win NT 4.0 SP3 / Win 95
ODBC-drviver: Oracle 8.00.0400 (=Version 8.0.4.4.0) (SQORA32.DLL 04.05.98) Oracle Server 8.00.4.0-Server Received on Thu Oct 01 1998 - 15:01:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US