It looked so promising
Ed Hoelker, September 16, 2004 - 4:29 pm UTC
Hi Tom,
Still not working in my environment. I did some more research concerning roles & dbms_job and came up empty-handed. I took your suggestion and did this:
create table t ( job int, txt varchar2(30) );
create or replace procedure ewh_1
authid current_user
is
v_object_name all_objects.object_name%TYPE;
begin
execute immediate 'set role ALL';
ewh_1_write;
insert into t values(99,'DONE Outer');
execute immediate 'select object_name from dba_objects where rownum < 2'
into v_object_name;
dbms_output.put_line(v_object_name);
end;
create or replace procedure ewh_1_write
authid current_user
is
pragma autonomous_transaction;
p_job NUMBER;
begin
p_job := 1;
insert into t
select p_job, role from session_roles
union all
select p_job, USER from dual;
insert into t values(98,'DONE Inner');
commit;
end ewh_1_write;
Straight sql*plus:
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.91
SQL>
SQL> begin
2 ewh_1;
3 end;
4 /
/1001a851_ConstantDefImpl
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
SQL>
SQL> select * from t;
JOB TXT
---------- ------------------------------
1 CONNECT
1 SELECT_CATALOG_ROLE
<snip>
1 KATE_ML
98 DONE Inner
99 DONE Outer
14 rows selected.
Elapsed: 00:00:00.00
SQL>
But as a job...
SQL> @u:\data\devml
Connected.
SQL> variable n number
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.60
SQL> exec dbms_job.submit(:n,'ewh_1;');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> exec dbms_job.run(:n);
BEGIN dbms_job.run(:n); END;
*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 405
ORA-06512: at "SYS.DBMS_JOB", line 267
ORA-06512: at line 1
Elapsed: 00:00:00.40
SQL> select * from t;
JOB TXT
---------- ------------------------------
1 KATE_ML
98 DONE Inner
Elapsed: 00:00:00.90
SQL>
The alert log is still pointing to the execute immediate 'select object_name from dba_objects.. statement
September 16, 2004 - 7:30 pm UTC
version and OS?
Environment Info
Ed Hoelker, September 17, 2004 - 9:21 am UTC
Compaq PROLIANT DL380 G2, running Red Hat Linux AS2.1
>uname -a
Linux hlwsdlx001 2.4.9-e.27enterprise #1 SMP Tue Aug 5 15:39:21 EDT 2003 i686 unknown
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_TIM STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- ----------- ------- --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE
---------- --- ----------------- ------------------
1 UKDEV188
hlwsdlx001
8.1.7.4.0 12-SEP-2004 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE
September 17, 2004 - 10:15 am UTC
hmm, i ran this on 8174 as well
if you run *just my example* what happens? roles or no?
Only Userid
Ed Hoelker, September 17, 2004 - 12:09 pm UTC
Hi Tom,
I ran your exact sql from above and only got the userid back. (I can post the results if you'd like).
Ed
September 17, 2004 - 1:13 pm UTC
arg, don't ask me why I did that test in 9i :(
it works in 9i
the roles just won't come on in 8i, even if we nuke the stored procedure:
ops$tkyte@ORA817DEV> variable n number
ops$tkyte@ORA817DEV> begin
2 dbms_job.submit(:n,'
3 dbms_session.set_role(''DBA'');
4 insert into t
5 select JOB, role from session_roles
6 union all
7 select JOB, USER||'',''||userenv(''schemaid'') from dual;' );
8 end;
9 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV> exec dbms_job.run(:n);
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select * from t where job = :n;
JOB TXT
---------- ------------------------------
390 OPS$TKYTE,759
http://asktom.oracle.com/Misc/RolesAndProcedures.html I'm afraid that for this, the person scheduling the job is going to need the grant *directly* to them in 8i
Well too bad for me
Ed Hoelker, September 17, 2004 - 1:27 pm UTC
I got into a 9i db and concur with your findings. Please note that the link above does not really describe this problem with dbms_job vs. executing the proc in sql*plus. I had seen that article before and it contains this little clause that gave me hope:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure (unless you are using Invokers rights in Oracle8i. See the PLSQL documentation for more information on this feature and make sure you understand the ramifications). To be able to perform that operation in a typical procedure, you need to have that privelege granted directly to you.
Anyway, thanks and I'll request specific grants.
Ed
Still dont understand why this wont work
syed, September 18, 2004 - 10:22 am UTC
Tom
user1 has direct grant to execute user2's package
but it still fails on the call to dbms_refresh.
I dont understand whats going on ? (8.1.7 Solaris)
The test case is ..
create user user1 identified by user1;
grant connect, resource to user1;
create user user2 identified by user2;
grant connect , resource to user2;
grant create materialized view to user2;
connect user1/user1
create table a (x int constraint x_pk primary key);
insert into a values (1);
commit;
grant select on a to user2;
connect user2/user2
create materialized view mv_a refresh force on demand as select * from user1.a;
exec dbms_refresh.make (name => 'REFRESH_GROUP', list => 'MV_A', next_date => null, interval => null);
create or replace procedure refresh_mv as
begin
dbms_refresh.refresh('REFRESH_GROUP');
end;
/
grant execute on refresh_mv to user1;
connect user1/user1
exec user2.refresh_mv
ORA-23404: refresh group "user1"."REFRESH_GROUP" does not exist
This implies that the dbms_refresh.refresh procedure is being executed under the security domain of user1 rather than user1 ???
Thanks
Syed
September 18, 2004 - 11:24 am UTC
user2@ORA8I> create or replace procedure refresh_mv as
2 begin
3 dbms_refresh.refresh('"USER2"."REFRESH_GROUP"');
4 end;
5 /
Procedure created.
user2@ORA8I>
user2@ORA8I> grant execute on refresh_mv to user1;
Grant succeeded.
user2@ORA8I>
user2@ORA8I> connect user1/user1
Connected.
user2@ORA8I> exec user2.refresh_mv
PL/SQL procedure successfully completed.
it used the currently logged in users name by default, tell it otherwise.
thanks, but...
Syed, September 18, 2004 - 2:25 pm UTC
Is that expected behaviour ? I dont quite see why it uses
the currently logged in user ?
Thanks
Syed
September 18, 2004 - 5:13 pm UTC
because it does. it is just a "string", not an "object". so at runtime, it is saying "lets add the current users schema name here"
Fair Enough !!!
syed, September 19, 2004 - 2:31 pm UTC
Made the difference
Frederik Damgaard, March 17, 2006 - 2:20 am UTC
I was about giving up on using invoker rights in packages that should be executed using dbms_job as I always got "ORA-01031: insufficient privileges" when trying to manipulate data.
Then via GOOGLE I found this article and it was spot on: adding 'set role ALL' to my code within the package simply made the difference
Reader, April 17, 2008 - 9:40 am UTC
create table tst
(serial_no number
,nm varchar2(5)
,val number
,comments varchar2(30)
);
insert into tst
values
(1,'AB',10,'serial_no=1;nm=AB;val=10;');
insert into tst
values
(2,'BC',20,'serial_no=2;nm=BC;val=20;');
insert into tst
values
(3,'EF',10,'serial_no=3;nm=EF;val=10;');
insert into tst
values
(4,'AB',30,'serial_no=4;nm=AB;val=30;');
insert into tst
values
(5,'BC',40,'serial_no=5;nm=BC;val=40;');
insert into tst
values
(6,'EF',50,'serial_no=6;nm=EF;val=50;');
insert into tst
values
(7,'GH',30,'serial_no=7;nm=GH;val=30;');
insert into tst
values
(8,'PQ',60,'serial_no=8;nm=PQ;val=60;');
commit;
--DATA
serial_no nm val comments
1 AB 10 serial_no=1;nm=AB;val=10;
2 BC 20 serial_no=2;nm=BC;val=20;
3 EF 10 serial_no=3;nm=EF;val=10;
4 AB 30 serial_no=4;nm=AB;val=30;
5 BC 40 serial_no=5;nm=BC;val=40;
6 EF 50 serial_no=6;nm=EF;val=50;
7 GH 30 serial_no=7;nm=GH;val=30;
8 PQ 60 serial_no=8;nm=PQ;val=60;
I have a around 32 million records in tst table. I keep adding about 200,000 records everyday to tst table.
In one report I need to use all distinct values of nm field with maximum value for val column. I created a materialized view for this and refresh this on demand.
CREATE MATERIALIZED VIEW MV_TST
REFRESH COMPLETE ON DEMAND
AS
SELECT nm
, MAX (val) max_val
FROM tst
GROUP BY nm;
MV_TST will have a maximum 15,000 records.
I created materialized view, since this has to be joined with another table which has 20 million records. Join will be based on nm filed.
Another table:
create table ord
(nm varchar2(5),
price number,
last_price number,
created_dt date);
insert into ord
values
('AB',1000,900,to_date('2008-01-01','YYYY-MM-DD'));
insert into ord
values
('BC',7000,5000,to_date('2008-01-02','YYYY-MM-DD'));
insert into ord
values
('EF',900,20,to_date('2008-01-01','YYYY-MM-DD'));
insert into ord
values
('GH',100000,90000,to_date('2008-01-01','YYYY-MM-DD'));
insert into ord
values
('PQ',20000,21000,to_date('2008-01-01','YYYY-MM-DD'));
commit;
And, I have built view on top of MV_TST to get the desired data.
CREATE VIEW v_tst as
select nm
,y.price
,y.last_price
,y.created_dt
from MV_TST x
,ord y
where x.nm = y.nm;
In another report, I want to get all the fields for all the distinct symbols with the maximum value for val column from tst table.
Can you please tell me how to modify this materialized view's query, so as to have one materialized view for both the reports?
April 17, 2008 - 11:47 am UTC
why wouldn't you STORE THE DATA PROPERLY IN THE FIRST PLACE
rather than loading X million pieces of bad data, why don't you process it and load it as it should be in the first place?!?!?!
(I'll never get it...)
Reader, April 17, 2008 - 5:04 pm UTC
I have to store the data as it comes to us. I cannot process it.
Initially, a view was created by someone to get the distinct
values of nm field with maximum value for val colum.
This takes a long a long time to run when it is joined to ord table. So I created materialized view and refresh it on demand.
CREATE VIEW v_tst_old
AS
SELECT nm
, MAX (val) max_val
FROM tst
GROUP BY nm;
CREATE VIEW v_tst as
select nm
,y.price
,y.last_price
,y.created_dt
from v_tst_old x
,ord y
where x.nm = y.nm;
April 17, 2008 - 9:55 pm UTC
... I have to store the data as it comes to us. I cannot process it. ...
please - that is so wrong, even if true (which I doubt, come on - you are telling me you cannot store what you get AND process it into something USEFUL - really? if so, why? who would make such a silly rule??)
kill the materialized view, reformat the existing data (fine, keep the original string, I don't care about that at all - but put the other data into COLUMNS where it belongs - you are doing that (albeit grossly inefficiently) now - just do it)
Big Thanx
A reader, February 06, 2012 - 4:23 am UTC
I was having problem executing job (dbms_job.submit) within stored procedure located on non-sys user, the job is to create private synonyms for each new created user, the procedure was successfully executed on sys and not working fine on other users, so, I've changed my procedure and added authid CURRENT_USER and execute immediate 'set role ALL';
after that everything worked fine with me.
Big thanx
February 06, 2012 - 8:12 am UTC
you should have just granted create synonym to the owner of the procedure.