ON LOGON trigger not working
I'm trying to create a ON LOGON trigger that runs a stored procedure which grants privileges to a particular user upon logon. The stored procedure works well when it is ran independently, however when the user logs in the stored procedure is not executed by the trigger. No errors either.
Here's the trigger and the stored procedure.
CREATE OR REPLACE TRIGGER ON_LOGON AFTER LOGON ON DATABASE WHEN ( USER = 'TEST' )
SELECT USER INTO USERID from dual;
WHEN OTHERS THEN NULL;
CREATE OR REPLACE PROCEDURE grants_restore
(USERID IN varchar2)
CURSOR tabpriv_cur IS
SELECT UC_DBA_FIELD_DESC,RECNAME,USER_NAME from sysadm.ps_uc_dba_details where USER_NAME='TEST';
FETCH tabpriv_cur INTO priv_source,object_source,grantee_source;
IF tabpriv_cur%FOUND THEN
EXECUTE IMMEDIATE 'GRANT ' || priv_source || ' ON ' || object_source ||' TO '|| grantee_source;
Am I missing anything here?
Thanks in advance.
does that trigger even compile - im guessing not
And an EXCEPTION WHEN OTHERS THEN NULL; for the pièce de résistance : oh , day....
The trigger and the procedure compiles without any errors. The procedure runs independently without any errors. The trigger is not working for some reason though.
SQL> alter trigger on_logon compile;
SQL> alter procedure grants_restore compile;
Add some error messages with DBMS_OUTPUT and see if its working, step by step.
But - as mentioned above - an error handling EXCEPTIONS section might be quicker
How often do you see that?
Originally Posted by JChiappa
Tom Kyte says it's tantamount to saying "and if this doesn't work then I don't care"
Click Here to Expand Forum to Full Width