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> @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@xxxxxxxxx> 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@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
