alter session
A reader, November 26, 2006 - 12:02 am UTC
Tom,
If a user has create session privilege, doesn't he automatically get privilege to alter his own session? I tested and found this to be true. I am confused that if alter session privilege should not be granted, then how a user with just create session gets it automatically? I tested on 10gr2.
Thanks
November 26, 2006 - 9:22 am UTC
ops$tkyte%ORA10GR2> create user a identified by a;
User created.
ops$tkyte%ORA10GR2> grant create session to a;
Grant succeeded.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> alter session set sort_area_size = 100000;
Session altered.
a%ORA10GR2> alter session set sql_trace=true;
alter session set sql_trace=true
*
ERROR at line 1:
ORA-01031: insufficient privileges
<b>you do not get the ability to do all "alter session" things </b>
ALTER SESSION
Suhail Ahmad, November 26, 2006 - 12:04 am UTC
Well, I am doing a security assessment for an insurance company and I found that ALTER SESSION privilege has been granted to several users such as RECOVERY_CATALOG_OWNER, and to some application schema owners. These privileges are granted in production environemnet and I could not think of any good reason why one need it production.
Anyway thank you for the clarification.
Suhail
Greg Norris, November 26, 2006 - 2:02 am UTC
From what I understand, the only thing the ALTER SESSION privilege actually conveys is the ability to enable SQL_TRACE. Every non-trace-related variation of the ALTER SESSION command seems to work perfectly well, whether or not the privilege has been granted.
November 26, 2006 - 9:32 am UTC
that is the documented thing, yes.
a%ORA10GR2> alter session set events '10046 trace name context forever, level 12';
ERROR:
ORA-01031: insufficient privileges
there are the non-documented
a%ORA10GR2> alter session set events '10046 trace name context forever, level 12';
ERROR:
ORA-01031: insufficient privileges
hence, the unknown to you is what will get you. If you do not need it, why would you want to grant it?
alter session
Suhail, November 26, 2006 - 10:08 am UTC
Tom,
You said following:
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> alter session set sort_area_size = 100000;
Session altered.
a%ORA10GR2> alter session set sql_trace=true;
alter session set sql_trace=true
*
ERROR at line 1:
ORA-01031: insufficient privileges
Is there a way to find that user does not have sql_trace to TRUE enabled?
Thank you.
November 26, 2006 - 11:33 am UTC
I don't know what you mean - if you mean "can I find sessions where sql trace is enabled" - we'd have to look in the dump destinations for trace files (they have the session id information in the names)
If you mean "can I find sessions that cannot set sql trace on", that would be all sessions owned by accounts that do not have alter session granted to them directly or via a role.
review
Suhail, November 26, 2006 - 11:54 am UTC
Sorry,
I was not clear, I wanted to ment "can I find sessions where sql trace is enabled".
November 26, 2006 - 12:00 pm UTC
I answered above... I anticipated what the two possible questions could be and addressed them...
With 10g Identifing Tracing Enabled Sessions
Tonguc, January 02, 2007 - 7:16 am UTC
SQL_TRACE% columns in V$SESSION view now enables us to identify which sessions are producing trace files in the database at that time.
-- first lets check the initial conditions
SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE osuser = 'TURKCELL\TCHASYILMAZ' ;
SID SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
184 14563 DISABLED FALSE FALSE
1047 21161 DISABLED FALSE FALSE
1068 11068 DISABLED FALSE FALSE
-- then lets enable sql tracing on the first session
BEGIN
dbms_monitor.session_trace_enable(session_id => 184,
serial_num => 14563,
waits => TRUE,
binds => FALSE);
END;
/
-- and re-check the status of the sessions
SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE osuser = 'TURKCELL\TCHASYILMAZ' ;
SID SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
184 14563 ENABLED TRUE FALSE
1047 21161 DISABLED FALSE FALSE
1068 11068 DISABLED FALSE FALSE
Thought this may help,
Best regards.
Tonguc
January 02, 2007 - 8:08 am UTC
very nice, thanks much!
Alberto Dell'Era, January 02, 2007 - 9:12 am UTC
Unfortunately it doesn't work with the "traditional" ways to enable trace:
dellera@ORACLE10> alter session set events '10046 trace name context forever, level 12';
Session altered.
dellera@ORACLE10> select SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS from v$session where sid=(select sid from v$mystat where rownum=1);
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
------------------------ --------------- ---------------
DISABLED FALSE FALSE
dellera@ORACLE10> alter session set sql_trace=true;
Session altered.
dellera@ORACLE10> select SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS from v$session where sid=(select sid from v$mystat where rownum=1);
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
------------------------ --------------- ---------------
DISABLED FALSE FALSE
dellera@ORACLE10> select sid from v$mystat where rownum=1;
SID
----------
141
dellera@ORACLE10> exec dbms_monitor.session_trace_enable(141,null, waits => TRUE);
PL/SQL procedure successfully completed.
dellera@ORACLE10> select SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS from v$session where sid=(select sid from v$mystat where rownum=1);
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
------------------------ --------------- ---------------
ENABLED TRUE FALSE
How about alter session set current_schema ?
Mihail, January 02, 2007 - 4:35 pm UTC
Hi Tom,
what are your thoughts on having alter session priviledge for the reason of doing
execute immediate 'alter session set current schema=appschemaowner';
in an
after logon
trigger when using Oracle dababase users for authentication?
Can you think of a way to allow a user to do only
alter session set current user
and not any other alter session option ?
January 05, 2007 - 7:41 am UTC
you do not need alter session to do that.
ops$tkyte%ORA9IR2> create user a identified by a;
User created.
ops$tkyte%ORA9IR2> grant create session to a;
Grant succeeded.
ops$tkyte%ORA9IR2> connect a/a
Connected.
ops$tkyte%ORA9IR2> alter session set current_schema=scott;
Session altered.
ops$tkyte%ORA9IR2> alter session set sql_trace=true;
ERROR:
ORA-01031: insufficient privileges
With 10g Identifing Tracing Enabled Sessions
Patrick Mealey, January 07, 2010 - 10:23 am UTC
Note: this thread as displayed on the AskTom site seems to be an incorrect merging of two separate threads. One is about the alter session privilege and the other is about identifying a traced session in 10g. My followup is directed towards the sql tracing topic.
The sql_trace* columns in the v$session table are very handy, but they do not change from ENABLED to DISABLED, TRUE to FALSE, etc. and vice-versa until the traced session changes from an inactive state to a active state.
Is there a way for one session to "wake up" the other (inactive) trace enabled session so that it briefly becomes active and changes the sql_trace* columns in the v$session table?
For example:
dba@ora10g> exec dbms_monitor.session_trace_enable(141, 25, true, true);
dba@ora10g> select STATUS, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS
from v$session where sid = 141 and serial# = 25;
STATUS SQL_TRAC SQL_T SQL_T
-------- -------- ----- -----
INACTIVE DISABLED FALSE FALSE
dba@ora10g> exec dbms_magic_procedure.wakeup(141, 25);
dba@ora10g> select STATUS, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS
from v$session where sid = 141 and serial# = 25;
STATUS SQL_TRAC SQL_T SQL_T
-------- -------- ----- -----
INACTIVE ENABLED TRUE TRUE
January 11, 2010 - 8:24 pm UTC
there is not.
RE: How about alter session set current_schema ?
Duke Ganote, January 12, 2010 - 3:51 pm UTC
Excellent question, confusing terminology!
The documentation implies the
ALTER SESSION command is available with the CONNECT privilege. Most session-level parameters can be easily changed with only CONNECT-implied privileges. For example
ALTER SESSION SET nls_date_format = 'yyyy-mon-dd';
However, the
ALTER SESSION privilege has to be granted in order to set events via the
ALTER SESSION command, e.g.
ALTER SESSION SET EVENTS ...
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/appendixa.htm#CHDIBAJE
"alter session" privilege vs "alter session" command
Duke Ganote, January 12, 2010 - 3:57 pm UTC
How do I block specific alter session commands
Patazaki, March 25, 2015 - 2:54 pm UTC
I have come across situation where developers abuse ALTER SESSION ability to change SORT_AREA_SIZE and HASH_AREA_SIZE to astronomical numbers by first setting WORKAREA_SIZE_POLICY to MANUAL.
Is there any way to prevent it? You do not need explicit ALTER SESSION privilege to change these settings..