Re: Resumable transaction strangeness

From: Henry Poras <henry.poras_at_gmail.com>
Date: Mon, 8 May 2017 16:36:06 -0400
Message-ID: <CAK5zhLJYQ+Yr6d5yZN0s7wDoCxfUmg3UsxWkLxFAzR=VoyfAuA_at_mail.gmail.com>

SQL> --spool cr_restxn_admin_user_2
SQL> DROP TRIGGER grant_restxn;
DROP TRIGGER grant_restxn
*
ERROR at line 1:
ORA-04080: trigger 'GRANT_RESTXN' does not exist

SQL> DROP USER restxn_admin CASCADE;

User dropped.

SQL> CREATE USER restxn_admin IDENTIFIED BY restxn_admin DEFAULT TABLESPACE users PROFILE reset;

User created.

SQL> GRANT create session, create trigger, administer database trigger TO restxn_admin;

Grant succeeded.

SQL> GRANT select ON dba_sys_privs TO restxn_admin;

Grant succeeded.

SQL> GRANT resumable TO restxn_admin;

Grant succeeded.

SQL> connect restxn_admin/restxn_admin
Connected.
SQL> _at_cr_logon_trigger_final.sql
SQL> CREATE OR REPLACE TRIGGER grant_restxn   2 AFTER LOGON ON DATABASE
  3 DECLARE
  4 v_match varchar2(1) := 'Y';
  5 sqlstr VARCHAR2(1000) :='';
  6 v_priv dba_sys_privs.privilege%type;   7 BEGIN
  8 SELECT privilege
  9 INTO v_priv
 10 FROM dba_sys_privs
 11 WHERE privilege = 'RESUMABLE'
 12 AND grantee = sys_context('USERENV','SESSION_USER');  13
 14
 15 -- dbms_output.put_line('v_priv = '||v_priv);  16
 17 IF v_priv = 'RESUMABLE'
 18 THEN
 19 sqlstr := 'ALTER SESSION ENABLE RESUMABLE TIMEOUT 600';  20 -- dbms_output.put_line('sqlstr = '||sqlstr);  21 execute immediate sqlstr;
 22 ELSE
 23 -- dbms_output.put_line('null');
 24 NULL;
 25 END IF;
 26
 27 EXCEPTION
 28 WHEN NO_DATA_FOUND
 29 THEN
 30 -- dbms_output.put_line('no data found');  31 NULL;
 32 WHEN OTHERS THEN
 33 -- dbms_output.put_line('exception');  34 NULL;
 35 END;
 36 /

Trigger created.

SQL>
SQL>
SQL> show errors

No errors.
SQL>
SQL> connect / as sysdba
Connected.
SQL> --ALTER USER restxn_admin PROFILE yearly; SQL> ALTER USER restxn_admin ACCOUNT LOCK;

User altered.

SQL>
SQL> -- run test
SQL> DROP USER restxn_test1 CASCADE;
DROP USER restxn_test1 CASCADE
          *

ERROR at line 1:
ORA-01918: user 'RESTXN_TEST1' does not exist

SQL> DROP USER restxn_nores_test1 CASCADE;

User dropped.

SQL> CREATE USER restxn_nores_test1 IDENTIFIED BY restxn_nores_test1 PROFILE reset;

User created.

SQL> GRANT create session TO restxn_nores_test1;

Grant succeeded.

SQL> GRANT execute on DBMS_RESUMABLE to restxn_nores_test1;

Grant succeeded.

SQL> DROP USER restxn_test2 CASCADE;
DROP USER restxn_test2 CASCADE

          *
ERROR at line 1:
ORA-01918: user 'RESTXN_TEST2' does not exist

SQL> DROP USER restxn_res_test2 CASCADE;

User dropped.

SQL> CREATE USER restxn_res_test2 IDENTIFIED BY restxn_res_test2 PROFILE reset;

User created.

SQL> GRANT create session, resumable TO restxn_res_test2;

Grant succeeded.

SQL> GRANT execute on DBMS_RESUMABLE to restxn_res_test2;

Grant succeeded.

SQL> --spool off
SQL>
SQL> show parameter resumable

NAME                                 TYPE


------------------------------------ --------------------------------

VALUE

---

resumable_timeout                    integer

0
SQL>
SQL> SELECT owner, trigger_name, status FROM dba_triggers where trigger_name='GRANT_RESTXN';

OWNER

---

TRIGGER_NAME

---

STATUS

---

RESTXN_ADMIN
GRANT_RESTXN
ENABLED SQL>
SQL> connect restxn_nores_test1/restxn_nores_test1 Connected.
SQL> select DBMS_RESUMABLE.GET_TIMEOUT from dual;

GET_TIMEOUT

---

         -1

SQL>
SQL> connect restxn_res_test2/restxn_res_test2 Connected.
SQL> select DBMS_RESUMABLE.GET_TIMEOUT from dual;

GET_TIMEOUT

---

        600

SQL>
-- But trigger owner needs RESUMABLE for connecting session ALTER SESSION to ENABLE RESUMABLE
SQL> spool off
SQL> REVOKE resumable FROM restxn_admin;

Revoke succeeded.

SQL> connect restxn_res_test2/restxn_res_test2 Connected.
SQL> select DBMS_RESUMABLE.GET_TIMEOUT from dual;

GET_TIMEOUT

---

         -1

SQL> connect / as sysdba
Connected.
SQL> GRANT resumable TO restxn_admin;

Grant succeeded.

SQL> connect restxn_res_test2/restxn_res_test2 Connected.
SQL> select DBMS_RESUMABLE.GET_TIMEOUT from dual;

GET_TIMEOUT

---

        600

SQL> spool off

On Mon, May 8, 2017 at 4:10 PM, Henry Poras <henry.poras_at_gmail.com> wrote:

> 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

Received on Mon May 08 2017 - 22:36:06 CEST