6 Replies Latest reply: Sep 6, 2012 8:44 AM by 415289 RSS

    Privileges needed to start SQL trace in your own session at 11g versus 10g

    jim dc
      Hi all,
      What privileges do I need to be able to start an SQL trace im my own session? I have searched the documentation but couldnt find the answer.

      Typically we grant users connect,resource on our development databases. At 10.2 there was never a problem, but at 11.2 we get a ORA-01031 error when trying to start the trace. I cant see any difference in the privileges granted to connect, resource.

      Heres what happens. First at 10.2.0.1
      SQL> create user jim identified by jim;
      
      User created.
      
      SQL> grant connect, resource to jim;
      
      Grant succeeded.
      
      SQL> select * from DBA_SYS_PRIVS where grantee in ('CONNECT','RESOURCE');
      
      GRANTEE                        PRIVILEGE                                ADM     
      ------------------------------ ---------------------------------------- ---     
      RESOURCE                       CREATE TRIGGER                           NO      
      RESOURCE                       CREATE SEQUENCE                          NO      
      RESOURCE                       CREATE TYPE                              NO      
      RESOURCE                       CREATE PROCEDURE                         NO      
      RESOURCE                       CREATE CLUSTER                           NO      
      CONNECT                        CREATE SESSION                           NO      
      RESOURCE                       CREATE OPERATOR                          NO      
      RESOURCE                       CREATE INDEXTYPE                         NO      
      RESOURCE                       CREATE TABLE                             NO      
      
      9 rows selected.
      
      SQL> connect jim/jim@dev
      Connected.
      SQL> ALTER session SET tracefile_identifier ='jim';
      
      Session altered.
      
      SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE();
      
      PL/SQL procedure successfully completed.
      
      SQL> quit
      Now at 11.2.0.1
      SQL> create user jim2 identified by jim2;
      
      User created.
      
      SQL> select * from DBA_SYS_PRIVS where grantee in ('CONNECT','RESOURCE');
      
      GRANTEE                        PRIVILEGE                                ADM     
      ------------------------------ ---------------------------------------- ---     
      RESOURCE                       CREATE TRIGGER                           NO      
      RESOURCE                       CREATE SEQUENCE                          NO      
      RESOURCE                       CREATE TYPE                              NO      
      RESOURCE                       CREATE PROCEDURE                         NO      
      RESOURCE                       CREATE CLUSTER                           NO      
      CONNECT                        CREATE SESSION                           NO      
      RESOURCE                       CREATE OPERATOR                          NO      
      RESOURCE                       CREATE INDEXTYPE                         NO      
      RESOURCE                       CREATE TABLE                             NO      
      
      9 rows selected.
      
      SQL> grant connect, resource to jim2;
      
      Grant succeeded.
      
      SQL> connect jim2/jim2@dev2
      Connected.
      SQL> ALTER session SET tracefile_identifier ='jim';
      
      Session altered.
      
      SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE();
      BEGIN DBMS_SESSION.SESSION_TRACE_ENABLE(); END;
      
      *
      ERROR at line 1:
      ORA-01031: insufficient privileges 
      ORA-06512: at "SYS.DBMS_SESSION", line 276 
      ORA-06512: at line 1 
      
      
      SQL> connect system/manager@dev2
      Connected.
      SQL> grant alter session to jim2;
      
      Grant succeeded.
      
      SQL> connect jim2/jim2@dev2
      Connected.
      SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE();
      
      PL/SQL procedure successfully completed.
      
      SQL> quit
      I'm confused - granting alter session solved the problem at 11.2, though I could already alter the session to set the tracefile identifier. Alter session isn't listed in the privs granted to connect or resource in either database.

      Thanks in advance for any help.