Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View refreshed with invoker's schema
Materialized View refreshed with invoker's schema [message #207480] Tue, 05 December 2006 13:56 Go to next message
luis_maris
Messages: 2
Registered: December 2006
Junior Member
In schema "prod" I have a materialized view "prod.MV_RETAIL_CAPE" and a process in a package (prod.pkg_refresh_retail.refresh_mv (p_mv_name varchar2)) that refreshes materialized views that I send as a parameter. The procedure looks like this:

 1    procedure refresh_mv (p_mv_name varchar2)
 2    IS
	...
10    BEGIN
11        -- refresh materialized view
12        DBMS_MVIEW.refresh (p_mv_name, 'C');
	...
20    end refresh_mv;     


If I run this process using the schema "prod," the MV gets updated just fine.

From "prod":

begin
    pkg_refresh_retail.refresh_mv('MV_RETAIL_CAPE');
end;


But if I run this process from another schema (lets call it "dev") like this:

From "dev":

begin
    prod.pkg_refresh_retail.refresh_mv('MV_RETAIL_CAPE');
end;


It updates the materialized view in "dev", not in "prod", just like if it was calling the procedure with invoker's rights. Does anybody know why is this happening? I would expect for this procedure to execute with owner's (prod) rights and all unqualified objects be invoked from the owner's schema, not the invoker's.

If I change line 12 in the procedure as follows:

12        DBMS_MVIEW.refresh ('prod.'||p_mv_name, 'C');


It works as it should, it does refresh the materialized view in "prod" and not in "dev", but I do not want to implement it this way. What can I do to make it work correctly without hardcoding the schema name inside my procedure?

Thank you.

[Updated on: Tue, 05 December 2006 15:02]

Report message to a moderator

Re: Materialized View refreshed with invoker's schema [message #207483 is a reply to message #207480] Tue, 05 December 2006 14:05 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
How would Oracle know to refresh prod when you are logged into dev ?

I'm unsure whether synonyms work on MV's, if so that might be one approach.
Re: Materialized View refreshed with invoker's schema [message #207486 is a reply to message #207483] Tue, 05 December 2006 14:26 Go to previous messageGo to next message
luis_maris
Messages: 2
Registered: December 2006
Junior Member
Whenever a procedure (or any other program) is created by an schema in Oracle, the objects to which this procedure refer to are invoked with the owner of that procedure's right. In other words, if schema xxx created the procedure x.proc, and this procedure inserts into table xxx.tbl, whichever schema yyy that is granted permission to execute xxx.proc will be able to insert into table xxx.tbl through this procedure, even if no insert privilege has been granted to yyy to insert into that table. This is what is called owner's right in Oracle and is the default behavior of procedures.

However, in the case of the Materialized View, it seems like it is being invoked with owner's right, whereas all other objects in this procedure (which are not listed, but include tables and sequences) are being called with owner's right. One example can be seen in another portion of the same procedure, as follows:

 SELECT retail_mgi_nmgi_seq.nextval 
        INTO v_sequence 
        FROM dual;


In this case the sequence being invoked is the one in schema prod, even if the command is not explicitly mentioning the schema of the sequence, as in:

 SELECT prod.retail_mgi_nmgi_seq.nextval 
        INTO v_sequence 
        FROM dual;


Why does the Materialized View seem to behave as invoker's right, whereas all other objects seem to behave as owner's right?

Thank you.

[Updated on: Tue, 05 December 2006 15:04]

Report message to a moderator

Re: Materialized View refreshed with invoker's schema [message #207513 is a reply to message #207486] Tue, 05 December 2006 21:00 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
DBMS_MVIEW is yet another package, and it is probably run with invokers rights. I read something about this in the doco once, when you call an INVOKER-rights package 'B' from an OWNER-rights package 'A', whether B runs as the original invoker, or as 'A's owner. I can't remember what it said, but it looks like its using the original invoker.

You may have to prefix the MV name. Rather than hard-coding it, you should be able to get the owner of the package as a variable and use that. I think the USER pseudocolumn returns the original invoker - so that's no good. I believe SYS_CONTEXT('SESSION_USER') will do the trick - try it.

Ross Leishman
Previous Topic: type_name for %rowtype
Next Topic: Diff b/w distinct & group by clause
Goto Forum:
  


Current Time: Thu Dec 08 14:09:16 CST 2016

Total time taken to generate the page: 0.09143 seconds