Alter Session privilege (ORA-01031: insufficient privileges)

While trying to trace an event from user Scott who has connect and resource role privileges i have faced with ORA-01031 error when running the command “alter session set events ’10132 trace name context forever, level 8′;”. After a bit googling i saw that the connect role has changed for 10G R2 and does not have alter session sys privilege. Official Document says “beginning in Oracle Database 10g Release 2 (10.2), the CONNECT role has only the CREATE SESSION privilege, all other privileges are removed.”

The error stack (code ran on 10.2.0.1 Express Edition)

SQL> connect scott/tiger;

Connected.

scott@XE> alter session set sql_trace=true;

alter session set sql_trace=true

*

ERROR at line 1:

ORA-01031: insufficient privileges

scott@XE> alter session set events ’10132 trace name context forever, level 8′;

ERROR:

ORA-01031: insufficient privileges

scott@XE> select * from USER_ROLE_PRIVS;

USERNAME GRANTED_ROLE ADM DEF OS_

—————————— —————————— — — —

SCOTT CONNECT NO YES NO

SCOTT RESOURCE NO YES NO

scott@XE> select * from USER_SYS_PRIVS;

USERNAME PRIVILEGE ADM

—————————— —————————————- —

SCOTT CREATE VIEW NO

SCOTT UNLIMITED TABLESPACE NO

SCOTT CREATE SESSION NO

Investigation for the source of the error

In 10.2.0.1

SQL> select version from v$instance;

VERSION

—————–

10.2.0.1.0

SQL> select * from DBA_SYS_PRIVS where privilege=’ALTER SESSION’ ;

GRANTEE PRIVILEGE ADM

—————————— —————————————- —

DBA ALTER SESSION YES

RECOVERY_CATALOG_OWNER ALTER SESSION NO

CTXSYS ALTER SESSION NO

HR ALTER SESSION NO

FLOWS_020100 ALTER SESSION NO

XDB ALTER SESSION NO

6 rows selected.

SQL> select * from ROLE_SYS_PRIVS where role=’CONNECT’;

no rows selected

In 10.1.0.2

sys@XE> select version from v$instance;

VERSION

—————–

10.1.0.2.0

sys@XE> select * from DBA_SYS_PRIVS where privilege=’ALTER SESSION’ ;

GRANTEE PRIVILEGE ADM

—————————— —————————————- —

IX ALTER SESSION NO

DBA ALTER SESSION YES

SYS ALTER SESSION NO

XDB ALTER SESSION NO

DMSYS ALTER SESSION NO

WKSYS ALTER SESSION NO

CONNECT ALTER SESSION NO

PERFSTAT ALTER SESSION NO

OLAP_USER ALTER SESSION NO

RECOVERY_CATALOG_OWNER ALTER SESSION NO

10 rows selected.

sys@XE> select * from ROLE_SYS_PRIVS where role=’CONNECT’;

ROLE PRIVILEGE ADM

—————————— —————————————- —

CONNECT CREATE VIEW NO

CONNECT CREATE TABLE NO

CONNECT ALTER SESSION NO

CONNECT CREATE CLUSTER NO

CONNECT CREATE SESSION NO

CONNECT CREATE SYNONYM NO

CONNECT CREATE SEQUENCE NO

CONNECT CREATE DATABASE LINK NO

8 rows selected.

After giving alter session privilage to scott the error resolved;

sys@XE> GRANT ALTER SESSION TO SCOTT;

Grant succeeded.

sys@XE>connect scott/tiger

Connected.

scott@XE> alter session set sql_trace=true;

Session altered.

scott@XE> alter session set events ’10132 trace name context forever, level 8′;

Session altered.

scott@XE> select * from USER_SYS_PRIVS;

USERNAME PRIVILEGE ADM

—————————— —————————————- —

SCOTT CREATE VIEW NO

SCOTT UNLIMITED TABLESPACE NO

SCOTT ALTER SESSION NO

SCOTT CREATE SESSION NO