Re: Resumable transaction strangeness

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_at_gmail.com> 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_at_gmail.com> 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> _at_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
>>
>
>

--
http://www.freelists.org/webpage/oracle-l