Re: Auditing statements

  • From: Pete Finnigan <pete@xxxxxxxxxxxxxxxx>
  • To: DonGranaman@xxxxxxxxxxxxxxx
  • Date: Thu, 04 Aug 2011 20:20:25 +0100

Hi Don,

Audit create session will show if a schema is connnected to but not if
any of its objects are used. A schema may be a "schema" or "user", if a
"schema"/"user" has no objects then you could rely on a long term audit
that no one ever connects to the user and mark it for dropping.For
schemas you could audit all objects owned by it and if none are accessed
and the schema is not connected to, then maybe its unused. The problem
is also the interpretation of what "unused" means. Does it mean
connected to, does it mean its objects are not used or does it mean that
the account should not exist and actually is a duplicate/unauthorisec
(but used) - ie removing accounts does not necessarily mean that they
are not used. Conversly an account may not be used/connected to but
maybe cannot be removed for other reasons - historical audit based on
user ID springs to mind. There are solutions to this by creating a
mapping table and then removing the account. But you are right Don the
OP needs to do some research and also clarify the requirements.

Kind regards


Don Granaman wrote:
> You have some work to do - starting with research.  For starters try:
> and
> Object auditing is always for all users (except of course SYS, ... as SYSDBA, 
> .... as SYSOPER, etc.).  Some auditing is for all users (with the usual 
> exceptions) unless you specify otherwise.  For example:
> "audit session" versus "audit session by system" or "audit session by system 
> whenever not successful".
> To audit actions by highly privileged users (SYS, ... as SYSDBA, ... as 
> SYSOPER) see the initialization parameter AUDIT_SYS_OPERATIONS at:
> [see: 
> However, some things - like "database start" are always audited.
> [See: 
> I sincerely doubt that anyone is going to be both willing and able to 
> interpret your security division's "business requirements" into fully 
> functional code.  [E.G.  How can you tell if a schema is "unused"?]
> Don Granaman | Phone: 402-361-3073 | Cell: 402-960-6955 | Fax: 402-361-3173 | 
> Solutionary | Relevant . Intelligent . Security
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of P D
> Sent: Wednesday, August 03, 2011 2:29 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Auditing statements
> We have been asked by our security division to run these specific statements 
> on a database for auditing purposes.   They don't work.     These are 
> databases on Standard Edition.    Are there some other broad-based 
> generic commands that can be run that will capture the purpose of what is 
> listed here?     If they want it to capture information from every user in 
> the database, wouldn't we have to also explicitly state each user name, 
> otherwise all we are really auditing is actions by the sys user since that is 
> where the command is being run from?
> Audit drop unused schemas
> Audit trap autonomous transactions
> Audit any create statement
> Audit any drop statement
> Audit insert failures
> Audit grant any object
> Audit database start or stop


Pete Finnigan
CEO and Founder Limited

Specialists in database security.

Makers of PFCLScan the database security auditing tool.
Makers of PFCLObfuscate the tool to protect IPR in your PL/SQL

If you need help to audit or secure an Oracle database, please ask for
details of our training courses and consulting services

Phone: +44 (0)1904 791188
Fax  : +44 (0)1904 791188
Mob  : +44 (0)7759 277220
email: pete@xxxxxxxxxxxxxxxx
site :

Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
Company No       : 4664901
VAT No.          : 940668114

Please note that this email communication is intended only for the
addressee and may contain confidential or privileged information. The
contents of this email may be circulated internally within your
organisation only and may not be communicated to third parties without
the prior written permission of Limited.  This email is
not intended nor should it be taken to create any legal relations,
contractual or otherwise.

Other related posts: