Creating an Audit Trail of Ad Hoc SQL.ppt.ppt

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. 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 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 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 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 How does a program execute SQL that the user doesn’t have authority to execute? Answers? Permission chaining Application role Identity switch in IIS via identity DataPortal Architecture 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 Identity Switch Web.config identity section Identity Switch: More Secure Use ASPNet_SETREG Encrypts the UserName and Password and stores them in the registry Is it Valid SQL? What gets changed? sp_depends Begin Tran Create Proc XXX with the SQL Statement as the body EXEC sp_depends ‘XXX’ Rollback Tran Checking Permissions Security Database CSLA Generated Classes Use Case: Change with Grid Get data for the user to change Post the changes Record the details of what is changed Logging Changes Log4Net Open Source logging/tracing framework/log4net Write to multiple outputs