Connect Role without Alter Session privilege (ORA-01031: insufficient privileges)

When i am 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

You can look the official document linked below for the effects of this important change on connect privilege .

References Used :

Oracle® Database Security Guide 10g Release 2 (10.2)

Oracle® Database Security Guide 10g Release 1 (10.1)

About these ads