CODEWITHSUNDEEP
oracle11g
Deepak Ram
Deepak Ram

Reputation: 377

grant create view using system view with dba privilage in oracle

When I try to create a view like

create view viewname as select * from table1;

it is working. But when I try to create view like 

Create view view1 as Select * from dba_users;

I am unable to create it with a exception showing insufficient privileges. However I am able to do a select operation on the given view and getting the results.

select * from dba_users;

Is there any other role I need to add in order to able to create a view which contains dba_users(or any other tables with dba) as part of its definition

Upvotes: 4

Views: 1053

Answers (1)

Justin Cave
Justin Cave

Reputation: 231881

Most likely, the issue is that your access to dba_users comes via a role. If you want to create a view that references dba_users (or if you want to creates a definer's rights stored procedure that references dba_users), you would need to have privileges granted to your user directly not via a role. Assuming you want to be able to reference all the data dictionary tables in views and stored procedures, you probably want to ask your DBA to grant you the SELECT ANY DICTIONARY privilege directly not via a role.

Upvotes: 1

Related Questions