SQL & PL/SQL

> GRANT SELECT ON ALL VIEWS OF USER_1 to USER_2.

No such thing.  Grants must be made one at a time.

However, this is fairly easy to do with a script and dynamic sql.

There is no such thing. You can give "SELECT ANY TABLE" privilege to that user but its not good to give this privilige to anyone unless you know what you are doing. But this privilege should be able to gi ve access to views or future views. You can always test this.

Hi,

As the others have said, each GRANT only applies to a single object.  Unfortunately, Oracle dosn't have any way to group objects for prinvileges.  User_1 shopuld just include a GRANT statement (or a call to a grant script) in every scriopt that creates a view.

You could write a database trigger to do this automatically, but it's probably not worth the effort.

If a user is continually creating new views (or tables), that could be a symptom of a poor design.  What exactly is uesr_1 doing?  What arre the views for? 

You can run the below a query where you will  get the lost of view names so that you can run them as a script/

DECLARE

   v_sql    VARCHAR2 (4000);

   v_sql1   VARCHAR2 (4000);

   v_SQL2   VARCHAR2 (4000);

BEGIN

   FOR i IN (SELECT table_name

               FROM ALL_VIEWS

              WHERE owner = 'USER1')

   LOOP

      v_sql :=

            ' GRANT DELETE, INSERT, SELECT, UPDATE, DEBUG  ON USER1. '

         || i.table_name

         || 'USER2';

      EXECUTE IMMEDIATE v_sql;    

      v_sql := '';

   END LOOP;

END;

The alternative solution is create a ROLE, grant all access to the role and then grant this role to the schema. As and when a new view is created, grant the access to the role and it will give access to all those schemas who have access to role.

Kapil wrote:

The alternative solution is create a ROLE, grant all access to the role and then grant this role to the schema. As and when a new view is created, grant the access to the role and it will give access to all those schemas who have access to role.

Except that won't allow ANY access within named PL/SQL blocks since roles are disabled within such blocks.