Effective granting of rights to Views

[DTRNTR]

We have a shared schema 'Develop_Views' where database Views are created and then referenced by our application developers.

What is the best way to handle the granting of access to these Views to our individual application developers so as to minimize the administration necessary when new Views are added to this schema. In other words, can this be done by granting access to all Views in the schema (and hopefully inheriting rights to new View objects when created) without explicitly naming each granted View individually?

Thank You!

 

[Dagon]

The best option is probably to script it using the SQL from SQL technique e.g. adding a new user:

set echo off
set feedback off
set pages 0
set heading off

spool grntview.sql
select 'grant select, insert, update, delete on '||view_name||' to &user;'
from all_views
where owner = 'XXX';
spool off
@grntview

 

[Beilstwh]

What Dagon is trying to say is that you can't set schema level permissions in oracle. I have brought this up with oracle in the past, but they don't support this yet.

Bill
Oracle DBA/Developer
New York State, USA

 

[DTRNTR]

Thank You both....I greatly appreciate the code sample and the clarification provided!