Also turns out both the trigger owner and connecting session need to be
granted RESUMABLE in order to ENABLE RESUMABLE in the connecting session.
No resumable for trigger owner, no enable resumable in connecting session.
I'll come back and add my test scripts soon.
On Mon, May 8, 2017 at 1:01 PM, Henry Poras <henry.poras@xxxxxxxxx> wrote:
OK, cleared that up, but I still think the behavior is a bit strange.
When I changed the logon trigger to collect some data, I found that
sys_context('userenv','current_user') is the trigger owner, and that
sys_context('userenv','session_user') is the connecting session.
What this does is a bit confusing:
- the ALTER SESSION command in the trigger is run by the session_user, the
connecting session
- the user_sys_privs query return the privileges of the trigger owner.
If I run the query from dba_sys_privs adding a filter on
grantee=sys_context('userenv',session_user') everything works.
Presumably, resumable is enabled for sessions which do not have the
resumable priv because for the period in time where the ALTER SESSION is
executed, the privileges in question are those belonging to the trigger
owner.
A bit counterintuitive.
Henry
On Thu, May 4, 2017 at 6:41 PM, Henry Poras <henry.poras@xxxxxxxxx> wrote:
I am on 12.1.0.2
The issue is that with RESUMABLE_TIMEOUT init parameter set to 0, I am
enable resumable for session via a logon trigger. I have tried a few
incarnations of logon trigger syntax with the same results. Timeout is
being set and resumable is being enabled even if the resumable privilege
has not been granted.
SQL> show user
USER is "SYS"
SQL> show parameter resum
NAME TYPE
VALUE
------------------------------------ --------------------------------
------------------------------
resumable_timeout integer 0
SQL> connect restxn_admin
Connected.
SQL> @cr_logon_trigger_final.sql
Trigger created.
SQL> revoke resumable from hrp
2 /
Revoke succeeded.
SQL> connect hrp/hrp
Connected.
SQL> select dbms_resumable.get_timeout from dual;
GET_TIMEOUT
-----------
600
SQL> connect restxn_admin
Connected.
SQL> grant resumable to hrp
2 /
Grant succeeded.
SQL> connect hrp/hrp
Connected.
SQL> select dbms_resumable.get_timeout from dual;
GET_TIMEOUT
-----------
600
SQL> spool off
$ cat cr_logon_trigger_final.sql
CREATE OR REPLACE TRIGGER grant_restxn
AFTER LOGON ON DATABASE
DECLARE
v_match varchar2(1) := 'Y';
sqlstr VARCHAR2(1000) :='';
v_priv user_sys_privs.privilege%type;
BEGIN
SELECT privilege
INTO v_priv
FROM user_sys_privs
WHERE privilege = 'RESUMABLE';
-- dbms_output.put_line('v_priv = '||v_priv);
IF v_priv = 'RESUMABLE'
THEN
sqlstr := 'ALTER SESSION ENABLE RESUMABLE TIMEOUT 600';
dbms_output.put_line('sqlstr = '||sqlstr);
execute immediate sqlstr;
ELSE
-- dbms_output.put_line('null');
NULL;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- dbms_output.put_line('no data found');
NULL;
WHEN OTHERS THEN
-- dbms_output.put_line('exception');
NULL;
END;
/
I have also tailed the alert.log while running a test query to confirm
that the dbms_resumable.get_timeout function isn't the issue. When the
output from the function is 600, I am seeing a suspended txn. When the
output is -1, the statement fails.
What is going on here?
Henry
