Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 67 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Pete Finnigan's Oracle security weblog


Home » Archives » December 2004 » Post about setting up and using autotrace

[Previous entry: "Edwards post on Java running in the database"] [Next entry: "Disabling Oracle writes into NT event log"]

Post about setting up and using autotrace

December 19th, 2004 by Pete


I just found this post in Haris Ali's blog entitled "Autotrace in SQLPLUS" this evening.

This is an excellent short description / step-by-step of how to setup autotrace in SQL*Plus. The steps to use autotrace are quite simple. From a security aspect we need to ensure that the user has access to a PLAN_TABLE and also to the PLUSTRACE role. As Haris points out this role is usually granted to PUBLIC.

This is where my interest is. I have written many times before about the risks of allowing users to race their own sessions and create trace files or get execution plans and wait information. Autotrace is useful for developers, consultants etc who may be investigating bugs or issues. Of course access to SQL*Plus is also required (normally not a problem). There is no reason why general users should be able to create trace files, even to create autotrace sessions. Therefore ensure that if you enable autotrace or if its enabled already, then do not grant the PLUSTRACE role to PUBLIC. Grant it only to those users that need it. Also ensure that only the same users that need to have access to the PLAN_TABLE.

Use the script who_can_access.sql to find out which users can access the PLAN_TABLE. Check first who owns the PLAN_TABLE and how many there are in the database:

SQL> col owner for a10
SQL> col object_type for a30
SQL> col object_name for a30
SQL> select owner,object_type,object_name
2 from dba_objects
3 where object_name='PLAN_TABLE';

OWNER OBJECT_TYPE OBJECT_NAME
---------- ------------------------------ ------------------------
SH TABLE PLAN_TABLE

SQL>

Next use who_can_access.sql

who_can_access: Release 1.0.1.0.0 - Production on Sun Dec 19 21:51:30 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK [USER_OBJECTS]: PLAN_TABLE
OWNER OF THE OBJECT TO CHECK [USER]: SH
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Checking object => SH.PLAN_TABLE
====================================================================



PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

In my test database there is just one PLAN_TABLE and no users have been granted access to it. Let's now check which users have been granted the PLUSTRACE role. This time we can use the script who_has_role.sql as follows:

who_has_priv: Release 1.0.1.0.0 - Production on Sun Dec 19 21:53:40 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

ROLE TO CHECK [DBA]: PLUSTRACE
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Investigating Role => PLUSTRACE (PWD = ) which is granted to =>
====================================================================

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

Again on my test database no one has been granted the role. If you find users that have been granted access to the table or role review who they are and revoke the access that is not necessary.

I have also written a paper that details the many ways to set trace for your own session, others sessions and also to set trace levels to record bind and wait data. The paper is called "How to set trace for others sessions, for your own session and at instance level"

December 2004
SMTWTFS
   1234
567891011
12131415161718
19202122232425
262728293031 

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Weblog Home
Weblog Archives


Home
Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

RSS 1.0 FEED
RSS 2.0 FEED
Atom 0.3 FEED
Powered by gm-rss 2.0.0


Valid XHTML 1.0!