Re: CREATE OR REPLACE VIEW ORA-01031

From: Terry Dykstra <tddykstra_at_forestoil.ca>
Date: Wed, 02 Jul 2008 16:47:09 GMT
Message-ID: <hiOak.1327$1o6.190@edtnps83>


"Marcin Wróblewski" <m_wroblewski_at_gazeta.pl> wrote in message news:g4gasn$fih$1_at_inews.gazeta.pl...
> max.fontain_at_yahoo.com pisze:
>> Hello,
>> I am trying to run a long sql script that creates tables, triggers and
>> procedures etc.
>> I ran the same script on 9i some time ago and it worked OK - but 10G
>> barfs on CREATE OR REPLACE VIEW as follows:
>>
>> DROP USER getreports CASCADE;
>>
>> CREATE USER getreports IDENTIFIED BY getreports
>> DEFAULT TABLESPACE getreports
>> TEMPORARY TABLESPACE getreports_TEMP
>> PROFILE DEFAULT ACCOUNT UNLOCK;
>> GRANT CONNECT TO getreports;
>> GRANT RESOURCE TO getreports;
>> GRANT SELECT_CATALOG_ROLE TO getreports;
>> GRANT SELECT ANY TABLE TO getreports;
>> GRANT UNLIMITED TABLESPACE TO getreports;
>>
>> CONNECT getreports/getreports
>>
>> SQL> CREATE OR REPLACE VIEW REFERENCE_DATE AS
>> 2 SELECT sysdate Ref_Date FROM dual;
>> CREATE OR REPLACE VIEW REFERENCE_DATE AS
>> *
>> ERROR at line 1:
>> ORA-01031: insufficient privileges
>>
>> I suppose that some additional privileges for the getreports user are
>> required.
>> Could someone help me out here?
>>
>> TIA
>>
>> Max
>
> GRANT CREATE VIEW TO GETREPORTS
> /
>
> ?

Take a look at the CONNECT privileges in 10g. Oracle cleaned it up and now it has nothing more than create session. Grant select any table and unlimited tablespace should be handled with care. Allways go by least required privileges.

-- 
Terry Dykstra 
Received on Wed Jul 02 2008 - 11:47:09 CDT

Original text of this message