2 Replies Latest reply: Oct 17, 2012 8:35 AM by user8632123 RSS

    attempt to create object view with oid clause fails because miss privileges

    user8632123
      Hi,

      my attempt to create an object view with object identifier primary key clause fails because of ORA-01031 "insufficient privileges An attempt was made to change the current username or password ...".

      I have no idea which privilege is missing.

      The code is:

      create table temptable
      (id number(8) primary key, class varchar2(4000), playorder varchar2(4000));

      create or replace type temptest as object (id number(8), class varchar2(4000), playorder varchar2(4000));

      create or replace view tempview of temptest
      with object identifier(ID)
      as select id, class, playorder from temptable;

      The user has resource privileges. Granting DBA privileges doesn't help either.

      I'd appreciate any ideas!

      Yours.
        • 1. Re: attempt to create object view with oid clause fails because miss privileges
          Billy~Verreynne
          Do not assign privileges arbitrarily. Even the resource role is no longer a recommended role for robust security.

          As for your problem, we need the 4 digit Oracle version and a copy-and-paste of a session that demonstrates the problem.

          The following works just fine on 11.2.0.2:
          // create a user with minimum privs needed
          
          SQL> create user user1 identified by user1 default tablespace users quota 1M on users;
          
          User created.
          
          SQL> grant create session, create table, create type, create view to user1;
          
          Grant succeeded.
          
          // connect as user
          SQL> connect user1/user1@dev1
          Connected.
          
          // run the code you supplied
          SQL> create table temptable
            2  (id number(8) primary key, class varchar2(4000), playorder varchar2(4000));
          
          Table created.
          
          SQL> create or replace type temptest as object (id number(8), class varchar2(4000), playorder varchar2(4000));
            2  /
          
          Type created.
          
          SQL> create or replace view tempview of temptest
            2  with object identifier(ID)
            3  as select id, class, playorder from temptable;
          
          View created.
          
          SQL> insert into temptable select rownum, object_name, object_type from all_objects where owner = 'SYS' and rownum < 11;
          
          10 rows created.
          
          SQL> col playorder format a25
          SQL> col class format a25
          SQL> select * from tempview;
          
                  ID CLASS                     PLAYORDER
          ---------- ------------------------- -------------------------
                   1 ORA$BASE                  EDITION
                   2 DUAL                      TABLE
                   3 SYSTEM_PRIVILEGE_MAP      TABLE
                   4 TABLE_PRIVILEGE_MAP       TABLE
                   5 STMT_AUDIT_OPTION_MAP     TABLE
                   6 DM$EXPIMP_ID_SEQ          SEQUENCE
                   7 STANDARD                  PACKAGE
                   8 DBMS_STANDARD             PACKAGE
                   9 ALL_XML_SCHEMAS           VIEW
                  10 ALL_XML_SCHEMAS2          VIEW
          
          10 rows selected.
          
          SQL> 
          SQL> select value(t) from tempview t;
          
          VALUE(T)(ID, CLASS, PLAYORDER)
          ----------------------------------------
          TEMPTEST(1, 'ORA$BASE', 'EDITION')
          TEMPTEST(2, 'DUAL', 'TABLE')
          TEMPTEST(3, 'SYSTEM_PRIVILEGE_MAP', 'TABLE')
          TEMPTEST(4, 'TABLE_PRIVILEGE_MAP', 'TABLE')
          TEMPTEST(5, 'STMT_AUDIT_OPTION_MAP', 'TABLE')
          TEMPTEST(6, 'DM$EXPIMP_ID_SEQ', 'SEQUENCE')
          TEMPTEST(7, 'STANDARD', 'PACKAGE')
          TEMPTEST(8, 'DBMS_STANDARD', 'PACKAGE')
          TEMPTEST(9, 'ALL_XML_SCHEMAS', 'VIEW')
          TEMPTEST(10, 'ALL_XML_SCHEMAS2', 'VIEW')
          
          10 rows selected.
          
          SQL> 
          • 2. Re: attempt to create object view with oid clause fails because miss privileges
            user8632123
            Hi Billy,

            indeed, you are right. I granted the privileges from your code directly and everything worked fine. (My database version is 11.2.0.3).

            Thanks!

            Bernd.