CREATE OR REPLACE VIEW ORA-01031
in [Oracle]
|
From: max.fontain on 2 Jul 2008 12:24 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
From: Marcin Wróblewski on 2 Jul 2008 12:38 max.fontain(a)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 / ?
From: Terry Dykstra on 2 Jul 2008 12:47 "Marcin Wr�blewski" <m_wroblewski(a)gazeta.pl> wrote in message news:g4gasn$fih$1(a)inews.gazeta.pl... > max.fontain(a)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
From: max.fontain on 3 Jul 2008 07:35 Terry Dykstra wrote: > "Marcin Wr�blewski" <m_wroblewski(a)gazeta.pl> wrote in message > news:g4gasn$fih$1(a)inews.gazeta.pl... > > max.fontain(a)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 Thanks for your responses - sorted now!
From: gazzag on 3 Jul 2008 10:18 On 3 Jul, 12:35, max.font...(a)yahoo.com wrote: > Thanks for your responses - sorted now!- Hide quoted text - > > - Show quoted text - As Terry Dykstra mentioned in his earlier post, do _not_ over-grant privileges! You should work on the principle of _least_ possible privileges to get the job done. Security isn't a dirty word, you know... HTH -g
|
Pages: 1 Prev: problem to opening isqlplus Next: can't get the em db control page |