tag:blogger.com,1999:blog-8884584404576003487.post6863761754848764458..comments2024-02-29T09:43:12.251-05:00Comments on ORACLENERD: How Do You Audit?oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-8884584404576003487.post-50344363219389117082008-04-19T14:46:00.000-04:002008-04-19T14:46:00.000-04:00Lewis C did some Auditing with streams and change ...Lewis C did some Auditing with streams and change data capture to send the audit information to an audit DB that was out of control of the DBAs. This is much better than triggers which are abused A LOT in auditing situations.<BR/><BR/>People wonder why their throughput went down after adding triggers. With CDC it's asynchronous instead of syncronous like triggers.Tomhttps://www.blogger.com/profile/09600876443323337792noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-51999095042161543042008-04-16T22:27:00.000-04:002008-04-16T22:27:00.000-04:00In the past I used DBMS_CRYPTO along with a modifi...In the past I used DBMS_CRYPTO along with a modified VPD (Standard Edition). I created a view on top of the table that called the column and the column the the decrypt function and only if the user was authorized could they view the decrypted column, otherwise it just displayed 'XXX-XX-XXXX' or something.<BR/><BR/>I have always wondered about having a single audit table and using that key, but I will still need a create_audit_key and a end_audit_key (and possibly an update_audit_key) column.<BR/><BR/>Ultimately, if you could build your application in parallel with your datawarehouse, you could just update the old record in your type 2 dimension and create a new current row there, only maintaining one record at a time (thus being able to use the SSN as a PK) in the base table.oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-10064089451499813222008-04-16T09:55:00.000-04:002008-04-16T09:55:00.000-04:00Yes, Use DBMS_CRYPTO or Transparent Data Encryptio...Yes, Use DBMS_CRYPTO or Transparent Data Encryption. TDE needs to be licensed under ASO and it only encrypts the data at the file and log level. It's good for packaged apps because it is hard to get them to change things as quickly as you need it done. Still, if someone gets access to the data inside the DB, they can see it.<BR/><BR/>DBMS_Crypto is slick in that they still need to provide a key to unlock/decrypt the value along with a decryption function that you build using DBMS_Crypto.<BR/><BR/>You do risk having the table grow a large amount. <BR/><BR/>What if you had a sequence generated or some other generated value for the PK. You can derive it from a number of things. For contention issues you might even look at making it a reverse key index if you see a lot of buffer busy waits on the end block of the index. This way you can have high concurrency and be able to use a sequence.<BR/><BR/>Then have an auditing table that you insert the old values and new values and maybe even what field changed. You can then log when it changed and have a foreign key that links back to the primary key of the main record table so you could always go back and build a history. If you foresee you are doing a lot of joins, build a and index on the FK column and see if Oracle uses your index : )...Tomhttps://www.blogger.com/profile/09600876443323337792noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-41200439938212704102008-04-16T01:43:00.000-04:002008-04-16T01:43:00.000-04:00I agree Tyler. I would encrypt it at the very lea...I agree Tyler. I would encrypt it at the very least and only allow users with access view the plain-text. <BR/><BR/>It was probably a bad example, I should have known better with the Oracle community. ;)<BR/><BR/>Let's assume it's encrypted and you didn't have the surrogate key to find the record to update so you had to use SSN (in this bad example anyway). How would you capture changes to the other fields (first name, last name, dob)?oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-1748765326829745402008-04-16T01:32:00.000-04:002008-04-16T01:32:00.000-04:00I'm actually a big fan of surrogate keys. SSNs ca...I'm actually a big fan of surrogate keys. SSNs can change you know. I never, ever use a meaningful column as the PK and I never run into this issue.<BR/><BR/>Also, looks like you're implying that it's OK to store a SSN in clear text. That would be a very, very bad thing. Make sure it's encrypted or even better, hashed.Anonymousnoreply@blogger.com