1 / 26

Creating an Audit Trail of Ad Hoc SQL

Creating an Audit Trail of Ad Hoc SQL. Andrew Novick. Agenda. Introduction Why audit ad hoc SQL Managing Identity with the DataPortal Getting Dependency Information Storing change information Querying the change database. Introduction. Andrew Novick – Novick Software

yitro
Download Presentation

Creating an Audit Trail of Ad Hoc SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating an Audit Trail of Ad Hoc SQL Andrew Novick Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  2. Agenda • Introduction • Why audit ad hoc SQL • Managing Identity with the DataPortal • Getting Dependency Information • Storing change information • Querying the change database. Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  3. Introduction • Andrew Novick – Novick Software • Business Application Development Consulting • SQL Server and .Net specialization • Also VB6, ASP, XML, SQL Optimization • www.NovickSoftware.com • Books: • Transact-SQL UDFs • SQL 2000 XML Distilled Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  4. Why Audit Ad Hoc SQL • Regulatory Compliance • Sarbaines Oxley • HIPPA • GAPP – Accountant Requirements • History of Changes • When did I make that change? • Who made that change? • CYA Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  5. Requirements • Record ad hoc database changes to production • Record all schema changes. • See old and new values for each modified field • Administrator control of who can change what • No impact on the target databases Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  6. Use Cases • Modify production data by changing a grid • Modify production data with a script • Make schema changes to a database. • Search: • What changes have been made • Who made the changes • Manage Security for the SQLManager Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  7. Use Case: Modify with a Script • Execute SQL that the user doesn’t have permission to execute. • Check for Valid SQL • Figure out what gets changed • Decide if the user has permission • Execute the change, store details Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  8. How does a program execute SQL that the user doesn’t have authority to execute? Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  9. Answers? • Permission chaining • Application role • Identity switch in IIS via <identity> Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  10. DataPortal Architecture Identity Switch Happens Here Databases Client SQLManager DataPortal In IIS .Net Remoting Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  11. DataPortal Architecture • Uses CSLA • Component-based Scalable Logical Architecture • Often used for building Business Objects • See Rocky Lhotka’s books: • Visual Basic Business Objects • C# Business Objects Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  12. Identity Switch • Web.config • <identity> section <identity impersonate="true" userName="IDGFINANCE\anovick“ password="zzz" /> Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  13. Identity Switch: More Secure • Use ASPNet_SETREG • Encrypts the UserName and Password and stores them in the registry <identity impersonate="true“ userName="registry:HKLM\SOFTWARE\SQLManagerHost\identity\ASPNET_SETREG,userName“password="registry:HKLM\SOFTWARE\SQLManagerHost\identity\ASPNET_SETREG,password" /> Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  14. Is it Valid SQL? SET PARSE ONLY ON GO Ad hoc SQL Goes here… GO Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  15. What gets changed? Given a SQL DML statement, which base tables does it read or modify? Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  16. sp_depends • Begin Tran • Create Proc XXX with the SQL Statement as the body • EXEC sp_depends ‘XXX’ • Rollback Tran Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  17. Checking Permissions • Security Database • CSLA Generated Classes Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  18. Use Case: Change with Grid • Get data for the user to change • Post the changes • Record the details of what is changed Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  19. Logging Changes • Log4Net • Open Source logging/tracing frameworkhttp://logging.apache.org/log4net • Write to multiple outputs • Text file • Event Log • ADO.Net Connection Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  20. Store Change Details • Create DiffGram • Shred DiffGram • Store changes Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  21. Search Changes Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  22. Search Changes • Normal database query with Dynamic SQL • Since no XML is stored, searching is quick Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  23. Weekly Newsletter about SQL Coding • Theme for April is .Net CLR Programming http://www.novicksoftware.com/coding-in-sql/coding-in-sql-signup.htm Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  24. New England Visual Basic Pro • 1st Thursday of every month MPR- C • 6:15 to 8:30 • Schedule • Dec – Visual Studio 2005 Launch • Jan – Jason Beres – AJAX • Feb – Ben Sabitini – Monitoring ASP.Net • Apr – Jesse Liberty – ASP.Net Personalization Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  25. Slides and Examples http://www.NovickSoftware.com/Presentations Creating an Audit Trail of Ad Hoc SQLAndrew Novick

  26. Thanks for Coming anovick@novicksoftware.com http://www.NovickSoftware.com Creating an Audit Trail of Ad Hoc SQLAndrew Novick

More Related