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 Pete Don Granaman wrote: > You have some work to do - starting with research. For starters try: > http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/security.htm#CNCPT1616 > and > http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4007.htm#SQLRF01107 > > 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: > http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/auditing.htm#DBSEG98423] > > However, some things - like "database start" are always audited. > [See: > http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/security.htm#i13053] > > 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 > 11.1.0.7 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 PeteFinnigan.com 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 : http://www.petefinnigan.com 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 PeteFinnigan.com Limited. This email is not intended nor should it be taken to create any legal relations, contractual or otherwise. -- //www.freelists.org/webpage/oracle-l