Home » RDBMS Server » Server Administration » dbms_mview (9.0.1.0.1, windows xp prof)
dbms_mview [message #283438] Tue, 27 November 2007 02:40 Go to next message
raji.s
Messages: 52
Registered: February 2005
Member
Hi,

I have created a procedure in SYS user and then granted execute privilege to public. Then i have created a public synonym of this procedure.

SQL> sho user
USER is "SYS"

1 create or replace procedure prc_mv_refresh(list varchar2, method varchar2) as
2 begin
3 dbms_mview.refresh(list,method);
4 exception
5 when others then
6 raise_application_error(-20202,sqlerrm);
7* end;
SQL> /

Procedure created.

I have a materialized view and materialized view log in scott schema. when i try to refresh using dbms_mview, it is working fine but when i try to refresh it through user defined proc prc_mv_refresh, it is giving me insufficeint privileges issue.

SQL> exec dbms_mview.refresh('MV_EMP','F')

PL/SQL procedure successfully completed.


SQL> exec prc_mv_refresh('MV_EMP','F')
BEGIN prc_mv_refresh('MV_EMP','F'); END;

*
ERROR at line 1:
ORA-20202: ORA-01031: insufficient privileges
ORA-06512: at "SYS.PRC_MV_REFRESH", line 6
ORA-06512: at line 1


I am unable to undestand what is the issue here, can someone please help.
Re: dbms_mview [message #283442 is a reply to message #283438] Tue, 27 November 2007 02:52 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

I have created a procedure in SYS user
YOu know sys is magic user...
please don't use sys user for anything...

Try with some other user.
Re: dbms_mview [message #283443 is a reply to message #283438] Tue, 27 November 2007 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I have created a procedure in SYS user

Very bad idea.
SYS is only for Oracle and not your stuff.

Quote:

and then granted execute privilege to public

A bug security hole.

5 when others then
6 raise_application_error(-20202,sqlerrm);

A stupid thing.
What is the purpose of this "when others"?
What do you think it happens it you remove this?

Check privileges requested to refresh a mview.
Check the parameters meaning in refresh procedure (tip: does SYS.MV_EMP exist?).

Regards
Michel
Re: dbms_mview [message #283446 is a reply to message #283438] Tue, 27 November 2007 03:10 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
I know i am using SYS which is not the correct way but needed to try this option....

I have modified the procedure a bit.

SQL> sho user
USER is "SYS"

SQL> create or replace procedure prc_mv_refresh(v_user varchar2,list varchar2, method varchar2) as
2 begin
3 dbms_mview.refresh(v_user||'.'||list,method);
4 end;
5 /

Procedure created.

SQL> sho user
USER is "SYS"
SQL> exec prc_mv_refresh('scott','MV_EMP','F')

PL/SQL procedure successfully completed.


SQL> sho user
USER is "SCOTT"
SQL> exec prc_mv_refresh('scott','MV_EMP','F')
BEGIN prc_mv_refresh('scott','MV_EMP','F'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 814
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 872
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 852
ORA-06512: at "SYS.PRC_MV_REFRESH", line 3
ORA-06512: at line 1

What would be the issue here ????
Re: dbms_mview [message #283448 is a reply to message #283446] Tue, 27 November 2007 03:14 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

correct way but needed to try this option....
Can you explain us how is important for you to use SYS user.

and did scott user have sufficient privs.?
Re: dbms_mview [message #283451 is a reply to message #283446] Tue, 27 November 2007 03:37 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I know i am using SYS which is not the correct way but needed to try this option....

No you don't need it.
It is the wrong way.
Full stop.

Quote:

- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.



Regards
Michel
Previous Topic: ORA-00600 error
Next Topic: Oracle table size
Goto Forum:
  


Current Time: Thu Dec 08 20:30:55 CST 2016

Total time taken to generate the page: 0.05434 seconds