Oracle 10g R2 CREATE VIEW: ORA-01031

SQL*Plus: Release 10.2.0.1.0 - Beta on Wed Nov 9 03:16:53 2005
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect OWNER/secret@xe;
Connected.
SQL> create view vtest as select * from dual;
create view vtest as select * from dual
            *
ERROR at line 1:
ORA-01031: insufficient privileges

Alle anderen Rechte die dort nichts zu suchen haben wurden entfernt. Dagegen ist im Prinzip nichts einzuwenden, und ein Blick in die Datei RDBMS/ADMIN/sql.eql zeigt auch genau mit welchen Privilegien die Rollen angelegt werden:

...
rem   dmwong     07/21/04 - fix connect role to only contain create session
...
create role connect
/
grant create session to connect
/
create role resource
/
grant create table,create cluster,create sequence,create trigger,
 create procedure, create type, create indextype, create operator
 to resource
/
...

Damit ist es klar, dass das CREATE VIEW Privileg leider unter den Tisch gefallen ist, und die RESOURCE Rolle nicht wirklich sinnvoll benutzt werden kann. Für die Express Edition ist dieses Verhalten sehr störend, da dort in der HTML GUI einem User nur CONNECT, RESOURCE oder DBA zugewiesen werden kann.

Bei eigenen Anwendungen bietet es sich an mit anwendungsspezifischen Rollen zu arbeiten, und die Rolle für die Einrichtung des Schemas muss auch nicht immer aktiviert sein:

SQL> CONNECT system/secret@XE;
SQL> CREATE ROLE MYOWNER;
SQL> GRANT CREATE VIEW TO MYOWNER;
SQL> GRANT  CREATE TABLE TO MYOWNER;
SQL> CREATE USER "OWNER"  PROFILE "DEFAULT"
     1   IDENTIFIED BY "secret" DEFAULT TABLESPACE "USERS"
     2   ACCOUNT UNLOCK;
SQL> GRANT "CONNECT" TO "OWNER";
SQL> GRANT "MYOWNER" TO "OWNER";
SQL> ALTER USER "OWNER" DEFAULT ROLE  ALL EXCEPT MYOWNER

Denn jetzt kann sich der Owner User bequem connecten, ist nicht mehr von der Definition der System Rollen abhängig und hat die erweiterten Privilegien zum Anlegen des Schema nur auf Zuruf:

SQL*Plus: Release 10.2.0.1.0 - Beta on Wed Nov 9 03:16:53 2005
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect OWNER/secret@xe;
Connected.
SQL> create view vtest as select * from dual;
create view vtest as select * from dual
            *
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> set role myowner;
Role set.
SQL> create view vtest as select * from dual;
View created.

Zum weiterlesen:

• Oracle Scurity Guide mit Lösungsvorschlägen
• Connected Thinking (Blog Artikel von Niall)