Create materialized view(snapshot)

Hi kishleo,

try to grant "CREATE ANY VIEW" to mvadmin


Hope this helps

grant create materialized view to mvadmin;

create any view is already granted to mvadmin, i can create snapshot in mvadmin schema, i am getting error when i try to create snapshot in scott schema from mvadmin.

PURPOSE
  How to evaluate and resolve ORA-1031 (insufficient privileges) and/or  
  ORA-6512 errors using DDL or dynamic SQL (DBMS_SQL) to create a snapshot  
  in Oracle8. The approach in this article can also be used to tackle other  
  ORA-1031 errors.  
 
RELATED DOCUMENTS
  Oracle SQL Reference - CREATE SNAPSHOT, GRANT
  Oracle Supplied Packages Reference - DBMS_SQL
  Oracle PL/SQL - Dynamic SQL
  Oracle 8i Concepts - Definer/Invoker-Rights
 
  [NOTE:1008453.6] <ml2_documents.showDocument?p_id=1008453.6&p_database_id=NOT>  USING THE DBMS_SQL PACKAGE TO EXECUTE DDL STATEMENTS  
 
 
Overview:  
=========
 
An ORA-01031 error will result if the user executing DDL or a package has  
insufficient privilege(s).  Since named PL/SQL blocks are executed with  
definer-rights for all versions of Oracle before 8i, roles are disabled and
therefore all privileges required for procedure execution must be granted  
directly to the user (refer to Note:162489.1> Invokers Rights Procedure Executed  
by Definers Rights Procedures.
The ORA-06512 error results anytime there exists an unhandled exception within  
PL/SQL.  Oracle8i introduces invoker-rights (roles enabled) for named PL/SQL.  
Oracle8i supplied packages DBMS_SQL and DBMS_SYS_SQL are created as  
invoker-rights procedures (AUTHID CURRENT_USER).  Refer to the Oracle8i  
Concepts manual for additional information regarding invoker-rights.
 
The three scenarios that will be covered are:
I)   Creating a snapshot using DDL
II)  Creating a snapshot using DBMS_SQL
III) Creating a snapshot in another schema
 
 
The first step in resolving an ORA-01031 errorfrom the execution of PL/SQL is  
to turn on SQL tracing and review the resulting trace file.  The trace file
will contain the SQL statement that produced the ORA-01031 error.  In the  
following example, the CREATE TABLE statement is followed by "err=1031", thus  
we know that the user executing the procedure does not have the correct  
privileges to create a table.
 
1.  alter session set sql_trace = true;
2.  exec <procedure>
3.  locate trace file in user_dump_dest.
 
 
Extract from trace file:
========================
 
PARSING IN CURSOR #4 len=116 dep=2 uid=152 oct=1 lid=152 tim=19811659 hv=0 ad='1
6ed74c'
CREATE TABLE "TEST"."TEST_SNAP" ("C1","C2","C3")  AS (SELECT "TEST"."C1","TEST".
"C2","TEST"."C3" FROM "TEST" "TEST")
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=3,cu=0,mis=1,r=0,dep=2,og=4,tim=19811659
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=19811659
ERROR #4:err=1031 tim=19811659
EXEC #2:c=7,e=9,p=0,cr=43,cu=4,mis=0,r=0,dep=1,og=4,tim=19811659
ERROR #2:err=1031 tim=19811659
STAT #4 id=1 cnt=0 pid=0 pos=0 obj=156 op='TABLE ACCESS BY INDEX ROWID SNAP$ '
STAT #4 id=2 cnt=1 pid=1 pos=1 obj=157 op='INDEX UNIQUE SCAN '
EXEC #1:c=7,e=9,p=0,cr=43,cu=4,mis=0,r=0,dep=0,og=4,tim=19811659
ERROR #1:err=1031 tim=19811659
 
 
The dictionary view DBA_SYS_PRIVS can be queried to determine what privileges
have been granted directly to users and roles (including PUBLIC).  
DBA_ROLE_PRIVS can be queried to view all roles granted to users and roles.  
Since the PUBLIC role applies to all users, it is not included in  
DBA_ROLE_PRIVS.  
 
 
SQL> desc sys.dba_sys_privs
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE                                   NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)
 
SQL> desc sys.dba_role_privs
 Name                            Null?    Type
 ------------------------------- -------- ----
 GRANTEE                                  VARCHAR2(30)
 GRANTED_ROLE                    NOT NULL VARCHAR2(30)
 ADMIN_OPTION                             VARCHAR2(3)
 DEFAULT_ROLE                             VARCHAR2(3)
 
 
 
I. Complete Example - Using DDL:
================================
 
The following example creates a new schema, TEST, with only CONNECT and  
RESOURCE privileges.  The execution of the CREATE SNAPSHOT DDL command results
in an ORA-01031 error because the user does not have sufficient privileges to  
create a snapshot
 
SQL> connect internal
Connected.
SQL> create user test identified by test
  2  default tablespace users
  3  temporary tablespace temp;
 
User created.
 
SQL> grant connect, resource to test;
 
Grant succeeded.
 
SQL> select privilege from dba_sys_privs where grantee = 'TEST';
 
PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
 
SQL> connect test/test
Connected.
SQL> create table test (c1 integer primary key, c2 integer, c3 integer);
 
Table created.
 
SQL> create snapshot snap_test as select * from test;
create snapshot snap_test as select * from test
                                           *
ERROR at line 1:
ORA-01031: insufficient privileges
 
SQL> connect internal
Connected.
SQL> grant create snapshot to test;
Grant succeeded.
 
SQL> connect test/test
Connected.
SQL> create snapshot snap_test as select * from test;
 
Snapshot created.
 
 
II.  Complete Example - Using DBMS_SQL:
=======================================
 
The following example creates a new schema, TEST, with only CONNECT and  
RESOURCE privileges.  The execution of procedure CREATE_TEST_SNAP results in an
ORA-01031 and ORA-06512 error because the user does not have sufficient  
privileges to create a snapshot using DBMS_SQL package.  This example will work
in both Oracle release 8.0 and Oracle release 8i since the required roles are  
granted directly to the user.  Since Oracle supplied packages (e.g. DBMS_SQL  
and DBMS_SYS_SQL) in Oracle8i have invoker rights execution privileges (the  
roles are enabled), the user could receive the privilege either directly (as in
the following example) or via a role (e.g. GRANT CREATE TABLE TO PUBLIC).  Use  
DBA_SYS_PRIVS to view all system privileges granted to users and roles  
(including PUBLIC).  As a reminder, privileges must be granted directly to  
users for all versions before Oracle8i.
 
 
SQL> connect internal
Connected.
 
SQL> create user test identified by test
  2  default tablespace userdata
  3  temporary tablespace temp;
 
User created.
 
SQL> grant connect, resource to test;
 
Grant succeeded.
 
SQL> select privilege from dba_sys_privs where grantee = 'TEST';
 
PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
 
SQL> connect test/test
Connected.
 
SQL> -- display results of dbms_output.put_line
SQL> set serverout on    
 
SQL> create table test (c1 integer primary key, c2 integer, c3 integer);
 
Table created.
 
SQL> create or replace procedure create_test_snap as
  2  sql_stmt varchar2(4000);
  3  cursor_handle integer;
  4  begin
  5  sql_stmt := 'create snapshot test_snap '||
  6              'as select * from test';
  7  cursor_handle := dbms_sql.open_cursor;
  8  dbms_output.put_line (sql_stmt);
  9  dbms_sql.parse (cursor_handle, sql_stmt, dbms_sql.native);
 10  end;
 11  /
 
Procedure created.
 
SQL> exec create_test_snap
create snapshot test_snap as select * from test
BEGIN create_test_snap; END;
 
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 487
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "TEST.CREATE_TEST_SNAP", line 9
ORA-06512: at line 1
 
SQL> alter session set sql_trace=true;
 
Session altered.
 
=> Review trace file in user_dump_dest and determine statement resulting
   in ORA-01031.  There will either be a CREATE TABLE statement, CREATE
   SNAPSHOT statement or CREATE VIEW statement.  Based on the procedure -  
   this may be an iterative process since execution will terminate  
   upon first detected error.
 
SQL> connect internal
Connected.
 
SQL> -- GRANT <operation> ANY allows operation in any schema other than SYS.
SQL> -- Refer to the SQL Reference manual - GRANT - for additional informaton.
SQL> -- grant create any snapshot to test;
SQL> -- grant create any table to test;
SQL> -- grant create any view to test;
 
SQL> grant create snapshot to test;
 
Grant succeeded.
 
SQL> grant create table to test;
 
Grant succeeded.
 
SQL> grant create view to test;
 
Grant succeeded.
 
SQL> connect test/test
Connected.
 
SQL> set serverout on
 
SQL> exec create_test_snap
create snapshot test_snap as select * from test
 
PL/SQL procedure successfully completed.
 
SQL> select name from sys.dba_snapshots where owner = 'TEST';
 
NAME
------------------------------
TEST_SNAP
 
SQL> select privilege from sys.dba_sys_privs where grantee = 'TEST';
 
PRIVILEGE
----------------------------------------
CREATE SNAPSHOT
CREATE TABLE
CREATE VIEW
UNLIMITED TABLESPACE
 
 
III. Complete Example - Creating a snapshot in another schema
==============================================================
 
If user RICHARD create a database link ORCL to schema SCOTT on another database,
and then attempts to create a snapshot of SCOTT.TEST, in user HILARY's schema,
he gets an ORA-1031, if the privileges of RICHARD and HILARY are incorrect.
 
SQL> connect system/manager
Connected.
SQL> create user richard identified by smith;
 
User created.
 
SQL> grant connect, resource to richard;
 
Grant succeeded.
 
SQL> create user hilary identified by thomas;
 
User created.
 
SQL> grant connect, resource to hilary;
 
Grant succeeded.
 
SQL> connect hilary/thomas
Connected.
SQL> create database link orcl.world connect to scott identified by tiger
  2  using 'orcl.world';
 
Database link created.
 
SQL> connect richard/smith
Connected.
 
SQL> create database link orcl.world connect to scott identified by tiger
  2  using 'orcl.world';
 
Database link created.
 
 
SQL> create snapshot hilary.snaptest as select * from scott.test@orcl.world;
create snapshot hilary.snaptest as select * from scott.test@r1815.world
                                          *
ERROR at line 1:
ORA-01031: insufficient privileges  
 
You have to grant CREATE ANY SNAPSHOT privilege to
richard to allow him to create a snapshot in another schema.
 
SQL> connect system/manager
Connected.
SQL> grant create any snapshot to richard;
 
Grant succeeded.
 
SQL> connect richard/smith;
Connected.
 
SQL> create snapshot hilary.snaptest as select *  
  2  from scott.test@orcl.world;
 
Materialized view created.
 
 
 
Conclusion:
===========
 
In conclusion, CREATE SNAPSHOT privilege must be granted to the user creating
the snapshot if you are using the CREATE SNAPSHOT DDL.  The CREATE TABLE,  
CREATE SNAPSHOT and CREATE VIEW privileges must be granted directly to the user  
attempting to create a snapshot using dynamic SQL (DMBS_SQL) from a named  
PL/SQL block for all versions of Oracle before Oracle8i.  CREATE ANY SNAPSHOT
privilege must be granted to the user creating the snapshot in another
schema.
 
In Oracle8i, the privilege may be either granted directly or via a role,  
including PUBLIC.

kishleo,

try to grant CREATE SNAPSHOT to SCOTT


Hope this helps

!!!!GRANT CREATE any materialized view to mvadmin
as mvadmin is creating the MV in Scott's schema!!!!