This discussion is archived
6 Replies Latest reply: Sep 6, 2012 6:44 AM by KuljeetPalSingh RSS

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

jim dc Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points