Home » SQL & PL/SQL » SQL & PL/SQL » trigger and procedure privileges ??!!
trigger and procedure privileges ??!! [message #238476] Fri, 18 May 2007 04:18 Go to next message
cefb
Messages: 56
Registered: March 2007
Member
Hi there.

I have a trigger on before insert that calls a procedure that only checks if the values that someone is trying to insert are valid.

In the procedure i have

select count(*) into counter from all_tables


The counter returns 50 if the procedure is called inside the trigger, if e just execute the procedure it returns 629.

thanks
Re: trigger and procedure privileges ??!! [message #238515 is a reply to message #238476] Fri, 18 May 2007 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because role are not enabled in procedure.
Execute "set role none" and you get the same number in both cases.

Regards
Michel
Re: trigger and procedure privileges ??!! [message #238526 is a reply to message #238476] Fri, 18 May 2007 07:56 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
still getting different values.
Re: trigger and procedure privileges ??!! [message #238528 is a reply to message #238526] Fri, 18 May 2007 08:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is the procedure created with invoker rights?
Re: trigger and procedure privileges ??!! [message #238529 is a reply to message #238476] Fri, 18 May 2007 08:06 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
i create the procedure with AUTHID CURRENT_USER
Re: trigger and procedure privileges ??!! [message #238541 is a reply to message #238529] Fri, 18 May 2007 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! You don't tell us all the truth!
Post your code and how you use it.

Regards
Michel

[Updated on: Fri, 18 May 2007 08:27]

Report message to a moderator

Re: trigger and procedure privileges ??!! [message #238544 is a reply to message #238476] Fri, 18 May 2007 08:36 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
sorry!!!!!

CREATE OR REPLACE TRIGGER MY_LIST BEFORE
INSERT ON LISTS FOR EACH ROW 
BEGIN
Teste;
END;


create procedure teste authid current_user
as
counter number;
begin
select count(*) into counter from all_tables;
dbms_output.put_line('Counter -> '||counter);
end;


i execute this as a dba user

insert into lists('ce','Teste');

[Updated on: Fri, 18 May 2007 08:37]

Report message to a moderator

Re: trigger and procedure privileges ??!! [message #238552 is a reply to message #238544] Fri, 18 May 2007 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table lists (col1 varchar2(10), col2 varchar2(10));

Table created.

SQL> create or replace procedure teste authid current_user
  2  as
  3  counter number;
  4  begin
  5  select count(*) into counter from all_tables;
  6  dbms_output.put_line('Counter -> '||counter);
  7  end;
  8  /

Procedure created.

SQL> CREATE OR REPLACE TRIGGER MY_LIST BEFORE
  2  INSERT ON LISTS FOR EACH ROW 
  3  BEGIN
  4  Teste;
  5  END;
  6  /

Trigger created.

SQL> select count(*) from all_tables;
  COUNT(*)
----------
      1506

1 row selected.

SQL> set role none;

Role set.

SQL> select count(*) from all_tables;
  COUNT(*)
----------
        86

1 row selected.

SQL> insert into lists('ce','Teste'); 
insert into lists('ce','Teste')
                  *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into lists values('ce','Teste'); 
Counter -> 86

1 row created.

I got the same number: 86.

Your trigger is always executed with definer rights without roles and so is your procedure.

Regards
Michel
Re: trigger and procedure privileges ??!! [message #238553 is a reply to message #238476] Fri, 18 May 2007 08:53 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
sorry for the errors.

But is there anyway of getting the 1506 value from the procedure?
Re: trigger and procedure privileges ??!! [message #238556 is a reply to message #238553] Fri, 18 May 2007 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not from a trigger.

Regards
Michel
Re: trigger and procedure privileges ??!! [message #238559 is a reply to message #238476] Fri, 18 May 2007 09:05 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
thanks for all your help.
Re: trigger and procedure privileges ??!! [message #238578 is a reply to message #238559] Fri, 18 May 2007 10:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is the trigger owned by the user that is executing the procedure directly?
Re: trigger and procedure privileges ??!! [message #238580 is a reply to message #238476] Fri, 18 May 2007 10:58 Go to previous message
cefb
Messages: 56
Registered: March 2007
Member
yes
Previous Topic: Holding tables
Next Topic: how can we use date in for loop
Goto Forum:
  


Current Time: Sun Dec 04 06:27:26 CST 2016

Total time taken to generate the page: 0.13785 seconds