Coskan’s Approach to Oracle

March 14, 2007

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

Filed under: Basics — coskan @ 11:54 am

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

14 Comments »

  1. I corrected the script.Please try it will run on all version. Only quotes appears to be an issue.

    select a.ksppinm name,
    b.ksppstvl value,
    b.ksppstdf deflt,
    decode
    (a.ksppity, 1,
    ‘boolean’, 2,
    ‘string’, 3,
    ‘number’, 4,
    ‘file’, a.ksppity) type,
    a.ksppdesc description
    from
    sys.x$ksppi a,
    sys.x$ksppcv b
    where
    a.indx = b.indx
    and
    a.ksppinm like ‘\_%’ escape ‘\’
    order by
    name

    Comment by Ramnik — October 19, 2007 @ 4:03 pm

  2. Thanks ur site really helped me to teach my students.

    Manish Gupta

    Comment by Manish — March 20, 2009 @ 3:05 am

  3. I feel that is one of the so much significant info for me.
    And i am happy reading your article. But want to commentary on some
    basic issues, The web site taste is ideal, the articles is really excellent :
    D. Just right job, cheers

    Comment by Garcinia Cambogia Advanced — July 24, 2013 @ 12:14 am

  4. It’s actually a cool and useful piece of info. I’m satisfied
    that you simply shared this helpful information with us.

    Please stay us up to date like this. Thank you for sharing.

    Comment by youtube to mp3 — August 6, 2013 @ 9:48 am

  5. Wow, awesome blog format! How lengthy have you ever been running a
    blog for? you made blogging glance easy. The full glance of your website is wonderful, let alone the content!

    Comment by อาหารญี่ปุ่น — September 19, 2013 @ 11:23 am

  6. If you desiee to grow your experience just keep visiting this site and be updatrd with the latest gossip posted here.

    Comment by garcinia cambogia blood pressure — March 16, 2014 @ 6:04 pm

  7. I was excited to fid this website. I need to to thank you for ypur time due tto
    this fantastic read!! I definitely liked every little bit of it and I have you book marked to see new things in your web site.

    Comment by advanced garcinia & solpria — March 16, 2014 @ 9:18 pm

  8. Hey! Quick question that’s totally off topic.

    Do yyou know how to make your site mobile friendly? My website looks weird when viewing from my iphone4.

    I’m tryiung to find a templatee or plpugin that might be able to correct this problem.

    If you have any recommendations, please share.

    Cheers!

    Comment by garcinia cambogia surface pro review — April 23, 2014 @ 11:14 pm

  9. Someone essentially lend a hand to make severely articles I’d
    state. That is the very first time I frequented your web
    page and up to now? I amazed with the research you made to
    create this actual post extraordinary. Great process!

    Comment by casino — May 30, 2014 @ 7:50 am

  10. Hmm it seems like your website ate my first comment (it was super long) so I guess I’ll
    just sum it up what I wrote and say, I’m thoroughly enjoying your blog.
    I as well am an aspiring blog blogger but I’m still new to the whole thing.
    Do you have any recommendations for novice blog writers?
    I’d genuinely appreciate it.

    Comment by ครีมมาร์คหน้า — June 17, 2014 @ 1:32 am

  11. Asking questions are truly pleasant thing if you are not understanding something totally, but this post provides nice understanding
    yet.

    Comment by Scooby Doo Movie — June 17, 2014 @ 9:13 am

  12. Right here is the perfect web site for anyone who would
    like to understand this topic. You realize so much
    its almost hard to argue with you (not that I actually
    will need to…HaHa). You definitely put a
    new spin on a topic that has been discussed for years.
    Excellent stuff, just excellent!

    Comment by fish oil capsules — September 2, 2014 @ 11:20 am

  13. Hello friends, its enormous article concerning cultureand fully defined, keep it
    up all the time.

    Comment by tighten Sagging Skin — September 8, 2014 @ 6:32 pm

  14. Hi my friend! I want to say that this post is amazing, nice written and include approximately all vital infos.
    I would like to see more posts like this .

    Comment by sexy Faster — September 15, 2014 @ 3:20 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 205 other followers

%d bloggers like this: