Security Audit

1
Security Audit
2
Security Audit

• Types of audits
• Alerts
• Audit trails
• Application Audit
• Best practices

3
Types of Audit

• Standard audit
• C2 level audit

4
Standard Audit

• Also known as login audit
• Can be Implemented via an interface for
  Enterprise Manager
• Can also be enabled using
• SQL Distributed Management Objects (DMO)
• modify registry entry for audit level

5
Standard Audit

• SQL Server has four audit levels
• Level 0, None (means that no info is written to
  audit log)
• Level 1, Success (means that only successful
  login attempts are written to audit log)
• Level 2, Failure (means that only failed login
  attempts are written to audit log)
• Level 3, All (means that all login attempts are
  written to audit log)

6
C2 Level Audit

• SQL Server provides C2 level audit capability
• C2 level for security audit is set by the
  National Computer Security Center (NCSC)
• C2 is evaluated against the DoDs Trusted
  Computer System Evaluation Criteria (TCSEC)
• Only NCSC can officially grant C2 level security
  rating
• C2 level audit is enabled using the sp_configure
  procedure

7
C2 Level Audit

• Audit logs are retained in files of size 200 Mb
• The log files are stored as SQL Trace files with
  extension trc
• To see the file contents start the SQL Profiler
  and open the SQL Trace files and select the
  particular file of interest for opening
• By clicking on any row entry, complete audit
  command information can be viewed
• Admin privilege will be needed to execute
  sp_configure in order to activate C2 level
  auditing

8
C2 Level Audit

• Some audit can be performed using aspects of C2
  level audit via manual execution of following
  stored procedures
• sp_trace_create creates trace definition
• sp_trace_setevent identifies events and
• columns for
  trace
• sp_trace_setfilter creates filter definition
  for
• trace
• sp_trace_setstatus starts, stops and deletes
  trace
• definition

9
Alerts

• SQL Server Agent is an integrated tool for
  multiple functions
• Agent can do database backups, database checkups,
  bulk-loading of data and log reader
• Agent can interact with Windows Messaging to send
  email alerts
• Agent can execute a predefined job when an alert
  condition occurs

10
Alerts

• SQL Server Agent is installed as a Windows
  service with each instance of SQL Server
• Agent installation requires admin privilege
• Two types of alerts
• Event alert
• Error message alert
• In order to activate alert, Agent compares
  incoming data with stored data in sysalerts table
• SQL Mail can be avoided using an extended stored
  procedure XP_SMTP for email alerts

11
Alerts

• SQL Server auditing uses
• event ID 17055 for event source
• message ID 18452 for failed login attempt
• message ID 18453 for successful login
• These numbers could be used to trigger alerts via
  email

12
Audit Trails

• Audit trails track user behavior by recording
• user requests for services
• services rendered
• frequency of requests for specific service
• data viewed by user
• data changed by user
• Audit trail is a mechanism for complete
  reconstruction of every action taken against the
  database

13
Audit Trails

• Baseline for audit trail is called event horizon
• Event horizon refers to the number of audited
  events that audit trail analysis system must
  remember at any one time
• Event horizon value 1 means that an event is
  examined without reference to any preceding or
  succeeding events
• With event horizon of 1 data is aggregated for
  statistical analysis after all events are
  considered
• Intrusion Detection System requires a value
  greater than 1 for event horizon

14
Audit Trails

• Audit trail answers the who, what, when and
  in what order concerning data access
• On the user side
• Who initiated a transaction from what terminal
  and when?
• On the transaction side
• What was the exact transaction that was
  initiated?
• On the data side
• What was the result of the transaction?
• What were the database states before and after
  the transaction?

15
Application Audit

• Required to assess
• Business risk
• Internal control
• It is an audit of a single application
• Example audit of an Excel spreadsheet with
  embedded macros
• It could also be an audit of business processes
  that use IT heavily
• Example Payroll processing involving multiple
  servers and databases

16
Application Audit

• Application audit could also be technology
  related
• Example audit of organizational PBX
• Example audit of a data warehouse
• Periodicity of audit
• As the system is developed
• Post-implementation of a new system
• Every n months (n 12)

17
Application Audit

• What does the auditor look for?
• Assurance that the application provides adequate
  control over data being processed
• Level of control related to degree of risk being
  assumed
• Risk coming from incorrect or unauthorized
  processing of data
• job descriptions for
• application developers
• business owners
• production support groups

18
Application Audit

• What does the auditor look for?
• Level of segregation for system access and
  application privileges

19
Application Audit

• SANS recommends checking for following controls
• Application Administration
• Inputs, Processing, Outputs
• Logical Security
• Disaster Recovery Plan
• Change Management
• End user Support
• Third Party Services

20
Application Administration

• Impact of application on the business
• Team members roles and responsibilities are
  defined and documented
• Organizational chart is current
• Charts and roles help managers
• Understand the business implications
• Training tool for new members
• Legal and regulatory compliance issues with
  respect to an application must be specified

21
Application Administration

• Service Level Agreements (SLAs) between the
  application provider and the business must be in
  place
• Auditor will review SLA with respect to customer
  incentives and business objectives

22
Inputs, Processing, Outputs

• What the auditor will look for?
• Evidence of data preparation
• Procedures
• Reconciliation processes
• Handling requirements
• Evidence of control over manual processes
• Verification of certain calculations using
  Computer Assisted Auditing Techniques (CAATs)

23
Inputs, Processing, Outputs

• What the auditor will look for?
• Balancing and reconciliation for outputs
• Traceability of control totals to upstream and
  downstream systems

24
Logical Security

• This is independent of the enterprise system
  security review
• What the auditor will look for?
• Documentation about user ID administration
• Ability of HR department to generate list of new
  employees
• Verification of application access profiles
• Random review of current users access rights
  relative to business functions
• Password management

25
Disaster Recovery Plan

• What the auditor will look for?
• Backup guidelines and processes documentation
• Offsite storage guidelines
• Evidence of regularly scheduled disaster recovery
• DRP training

26
Change Management

• What the auditor will look for?
• Procedures for introducing design changes
• Procedures for handling design changes
• Types of changes
• Break fixes
• Enhancements
• Major revisions
• Outcome of change implementation
• Assessment of the impact of change
• Procedures for handling emergency change process

27
Change Management

• What the auditor will look for?
• Test to see that programmers in production cannot
  make changes to production version
• Documentation of changes handled

28
End User Support

• What the auditor will look for?
• Adequacy of end user support for controls to work
• Evidence of user manuals availability
• User training

29
Third Party Services

• What the auditor will look for?
• Controls around third party services
• Use tools such as SAS70 (Statement of Auditing
  Standards)
• Framework for SLAs
• Evidence of periodic review of SLAs
• Availability of continuation of services agreement

30
Best Practices

• For SQL Server 2000 use Windows authentication
• Authentication could be changed using SQL DMO
• Assign fixed server roles
• Assign fixed database roles
• Assign application roles
• These contain no members
• Activated at run time
• Override standard permissions

31
References

• SANS Audit information http//www.sans.org/rr/whi
  tepapers/auditing/1534.php
• Oliphant, Alan. An Introduction to Computer
  Auditing - part 6. theiia.org. Vol. 2, April 1,
  1999. IT Audit http//www.theiia.org/itaudit/index
  .cfm?fuseactionforumfid192

32
References

• Peterson, Brad et al. Ten Key Questions for
  Developing Effective Service Level Agreements.
  Outsourcing Center, 2001 Everest Partners,
• http//www.outsourcing-best-practices.com/ten.h
  tml
• Best practices http//vyaskn.tripod.com/sql_server
  _security_best_practices.htm