This discussion is archived
2 Replies Latest reply: Oct 17, 2012 6:35 AM by user8632123 RSS

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

user8632123 Newbie
Currently Being Moderated
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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points