Menu de navegação

[thiagobhitaParticipante]

Boa tarde senhores!

Estou criando uma view simples:

create role yyyyy;
Grant select on usu.xpto2 to yyyyy;
grant yyyyy to scott;

create or replace view scott.xpto as select * from usu.xpto2;

se eu tentar criar a view aparece o sguite erro:

ORA-00942: a tabela ou view não existe, estou logado com o system.

Concedi o privilégio :

grant select on usu.xpto2 to scott; se conceder o grant para o usuário direto (sem ser pela role) roda normal;

Alguem sabe me explicar o que esta acontecendo de errado com minha role?

Abraço

[burgaParticipante]

Priviégios de objetos dados por roles não vão funcionar nos casos em que estiverem envolvidos códigos PL/SQL ou views…

Pra isso devem ser dados privilégios diretos mesmo!

Leia o segundo item:
To create a view, you must meet the following requirements:

* To create a view in your schema, you must have the CREATE VIEW privilege. To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege. You can acquire these privileges explicitly or through a role.
* The owner of the view (whether it is you or another user) must have been explicitly granted privileges to access all objects referenced in the view definition. The owner cannot have obtained these privileges through roles. Also, the functionality of the view is dependent on the privileges of the view's owner. For example, if the owner of the view has only the INSERT privilege for Scott's emp table, the view can only be used to insert new rows into the emp table, not to SELECT, UPDATE, or DELETE rows.
* If the owner of the view intends to grant access to the view to other users, the owner must have received the object privileges to the base objects with the GRANT OPTION or the system privileges with the ADMIN OPTION.</code>

Fonte: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/views.htm#303

[Autor]

Posts