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.