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.