Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Syed Suhail.

Asked: November 25, 2006 - 6:53 pm UTC

Last updated: January 11, 2010 - 8:24 pm UTC

Version: 9i

Viewed 50K+ times! This question is

You Asked

Tom,

Could you provide me some guidelines on use of ALTER SESSION and ALTER SYSTEM privileges. Under what circumstances we should give these provleges to end users? What could go wrong if a malicious user get access to ALTER SESSION or ALTER SYSTEM privilege. Could he /she be able to delete files form unix file system, could he/she be able to dump the database structure in some other database or file system?

Thank you.

Suhail

and Tom said...

I would say - end users should never have either privilege.

Alter system - not a chance, that is a DBA thing, period.

Alter session - perhaps in a development system (so they can turn on sql_trace), but not in a real production system - there is no need for it there (if you need to do something in production, you can grant it for a moment and then revoke it).



Alter system - they could set utl_file_dir (to *, scope=spfile) among many other nasty things

Alter session - they could fill up trace, do malicious things

Better question would be for you: why do you think an end user should have it :)

Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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


Tom Kyte
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.


Tom Kyte
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.

Tom Kyte
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".



Tom Kyte
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
Tom Kyte
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 ?
Tom Kyte
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




Tom Kyte
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

which was part of the "great CONNECT shift" between 9i and 10g...
http://www.oracle.com/technology/deploy/security/pdf/securitynote210317.1_altersession.html

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..