August 16, 2016 at 11:26 am
ScottPletcher (8/16/2016)
Even so, it's likely that for in-house audits, or less formal audits, the current table could be freely accessed, and then INSERTs might not need logged. But verify that before you exclude them, because obviously it would be a royal pain to try to add them in later!
For whole row auditing, it's actually not a pain if it came up later. EVERYTHING in the audit table would have been, at one time, an INSERT. Anything missing from the audit table would still be in the original table as an untouched INSERT.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2016 at 12:27 pm
Jeff Moden (8/16/2016)
ScottPletcher (8/16/2016)
Even so, it's likely that for in-house audits, or less formal audits, the current table could be freely accessed, and then INSERTs might not need logged. But verify that before you exclude them, because obviously it would be a royal pain to try to add them in later!For whole row auditing, it's actually not a pain if it came up later. EVERYTHING in the audit table would have been, at one time, an INSERT. Anything missing from the audit table would still be in the original table as an untouched INSERT.
The pain would be getting the specific time and sequence of initial INSERT for each row, within the context of other actions.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 16, 2016 at 4:39 pm
ScottPletcher (8/16/2016)
Jeff Moden (8/16/2016)
ScottPletcher (8/16/2016)
Even so, it's likely that for in-house audits, or less formal audits, the current table could be freely accessed, and then INSERTs might not need logged. But verify that before you exclude them, because obviously it would be a royal pain to try to add them in later!For whole row auditing, it's actually not a pain if it came up later. EVERYTHING in the audit table would have been, at one time, an INSERT. Anything missing from the audit table would still be in the original table as an untouched INSERT.
The pain would be getting the specific time and sequence of initial INSERT for each row, within the context of other actions.
Gosh... no pain there if the table and related audit trigger is designed correctly. It's JUST a Slowly Changing Dimension method that's needed along with a History/Audit table.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2016 at 8:22 am
Jeff Moden (8/16/2016)
ScottPletcher (8/16/2016)
Jeff Moden (8/16/2016)
ScottPletcher (8/16/2016)
Even so, it's likely that for in-house audits, or less formal audits, the current table could be freely accessed, and then INSERTs might not need logged. But verify that before you exclude them, because obviously it would be a royal pain to try to add them in later!For whole row auditing, it's actually not a pain if it came up later. EVERYTHING in the audit table would have been, at one time, an INSERT. Anything missing from the audit table would still be in the original table as an untouched INSERT.
The pain would be getting the specific time and sequence of initial INSERT for each row, within the context of other actions.
Gosh... no pain there if the table and related audit trigger is designed correctly. It's JUST a Slowly Changing Dimension method that's needed along with a History/Audit table.
If you say so. But, gosh, you don't seem at all familiar with the rules of a formal audit. FDA rules were very strict. You couldn't make up history later that didn't appear in the audit logs.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 17, 2016 at 8:56 am
ScottPletcher (8/17/2016)
Jeff Moden (8/16/2016)
ScottPletcher (8/16/2016)
Jeff Moden (8/16/2016)
ScottPletcher (8/16/2016)
Even so, it's likely that for in-house audits, or less formal audits, the current table could be freely accessed, and then INSERTs might not need logged. But verify that before you exclude them, because obviously it would be a royal pain to try to add them in later!For whole row auditing, it's actually not a pain if it came up later. EVERYTHING in the audit table would have been, at one time, an INSERT. Anything missing from the audit table would still be in the original table as an untouched INSERT.
The pain would be getting the specific time and sequence of initial INSERT for each row, within the context of other actions.
Gosh... no pain there if the table and related audit trigger is designed correctly. It's JUST a Slowly Changing Dimension method that's needed along with a History/Audit table.
If you say so. But, gosh, you don't seem at all familiar with the rules of a formal audit. FDA rules were very strict. You couldn't make up history later that didn't appear in the audit logs.
As a matter of fact, the FDA is one of our clients and we pass the audits with flying colors. 😉
You misunderstand. I'm not suggesting that anyone "make up history". As a DBA, I'd forbid it just on principle alone. If you follow TYPE 6 SCDs and hybrid it with a History Table, you don't have to make a thing up. All original inserts will either be in the original table (with an action date column, also enforced by a trigger) or in the History Table if rows are updated. The latest update for any given row will always be in the original table and all previous states of rows will be in the history table. They take no exception to that.
You just don't need to duplicate or triplicate data by capturing the rows from the logical INSERTED table in the audit trigger. You ONLY need to capture the rows from the logical DELETED table, which will always show the previous state prior to the update. Because TYPE 6 SCDs require both a start and end date, there's no problem with identifying the original insert. It'll either be in the original table (with an action date of when inserted) or the row with the lowest start date (the original action date) in the history table.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2016 at 9:37 am
Jeff Moden (8/17/2016)
ScottPletcher (8/17/2016)
Jeff Moden (8/16/2016)
ScottPletcher (8/16/2016)
Jeff Moden (8/16/2016)
ScottPletcher (8/16/2016)
Even so, it's likely that for in-house audits, or less formal audits, the current table could be freely accessed, and then INSERTs might not need logged. But verify that before you exclude them, because obviously it would be a royal pain to try to add them in later!For whole row auditing, it's actually not a pain if it came up later. EVERYTHING in the audit table would have been, at one time, an INSERT. Anything missing from the audit table would still be in the original table as an untouched INSERT.
The pain would be getting the specific time and sequence of initial INSERT for each row, within the context of other actions.
Gosh... no pain there if the table and related audit trigger is designed correctly. It's JUST a Slowly Changing Dimension method that's needed along with a History/Audit table.
If you say so. But, gosh, you don't seem at all familiar with the rules of a formal audit. FDA rules were very strict. You couldn't make up history later that didn't appear in the audit logs.
As a matter of fact, the FDA is one of our clients and we pass the audits with flying colors. 😉
You misunderstand. I'm not suggesting that anyone "make up history". As a DBA, I'd forbid it just on principle alone. If you follow TYPE 6 SCDs and hybrid it with a History Table, you don't have to make a thing up. All original inserts will either be in the original table (with an action date column, also enforced by a trigger) or in the History Table if rows are updated. The latest update for any given row will always be in the original table and all previous states of rows will be in the history table. They take no exception to that.
You just don't need to duplicate or triplicate data by capturing the rows from the logical INSERTED table in the audit trigger. You ONLY need to capture the rows from the logical DELETED table, which will always show the previous state prior to the update. Because TYPE 6 SCDs require both a start and end date, there's no problem with identifying the original insert. It'll either be in the original table (with an action date of when inserted) or the row with the lowest start date (the original action date) in the history table.
Again, auditors would not have access to the original table. We manufactured human implants, so perhaps the auditing rules were stricter. I know the retention rules were severe too: the life of the person plus 20 years minimum, iirc. The source code had to have comments on each line indicating when and why it was changed and who changed it. And a serial number had to be on every product. We once had to look up one when some guy was found in a barrel and our implant was one of the only possible sources of id. Poor operators got to load tapes for two days to find that specific serial number and its history.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 17, 2016 at 11:41 am
ScottPletcher (8/17/2016)
Again, auditors would not have access to the original table.
But they have direct access to the audit table? That doesn't make sense to me but, of course, I didn't write those requirements. You do have to do what you have to do.
For us, the auditors don't have access to either the original or the history table unless they have a special request along with a gauntlet of approvals that they need to run through. Neither does the application. The original table can only be modified through the application and then only certain columns via a stored procedure. Any updates cause the original rows to be preserved in the history table. The auditors and certified users can only see the history that we present through a UNION ALL view between the two tables.
We even have a trigger to protect the audit table from direct updates by someone blessed with direct privs even though no one has been given such privs except those DBAs that have sysadmin privs.
Like you, we have to keep all the data virtually forever.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2016 at 12:05 pm
Jeff Moden (8/17/2016)
ScottPletcher (8/17/2016)
Again, auditors would not have access to the original table.But they have direct access to the audit table? That doesn't make sense to me but, of course, I didn't write those requirements. You do have to do what you have to do.
For us, the auditors don't have access to either the original or the history table unless they have a special request along with a gauntlet of approvals that they need to run through. Neither does the application. The original table can only be modified through the application and then only certain columns via a stored procedure. Any updates cause the original rows to be preserved in the history table. The auditors and certified users can only see the history that we present through a UNION ALL view between the two tables.
We even have a trigger to protect the audit table from direct updates by someone blessed with direct privs even though no one has been given such privs except those DBAs that have sysadmin privs.
Like you, we have to keep all the data virtually forever.
Not that's more in line with what I've seen. The auditors never got access to the database at all. They audited what we gave them. We collected their requirements, wrote the queries, provided them data and they audited it. The SQL was, of course, available for them to inspect if they wanted it. They did an occasional audit of what we gave them by watching us run a query and copy the results to compare what we gave them originally, but nothing further.
I can't imagine giving an auditor permissions to query the database themselves. I'm not saying it doesn't happen, but I've never seen and I don't really want to.
August 17, 2016 at 12:37 pm
Ed Wagner (8/17/2016)
Jeff Moden (8/17/2016)
ScottPletcher (8/17/2016)
Again, auditors would not have access to the original table.But they have direct access to the audit table? That doesn't make sense to me but, of course, I didn't write those requirements. You do have to do what you have to do.
For us, the auditors don't have access to either the original or the history table unless they have a special request along with a gauntlet of approvals that they need to run through. Neither does the application. The original table can only be modified through the application and then only certain columns via a stored procedure. Any updates cause the original rows to be preserved in the history table. The auditors and certified users can only see the history that we present through a UNION ALL view between the two tables.
We even have a trigger to protect the audit table from direct updates by someone blessed with direct privs even though no one has been given such privs except those DBAs that have sysadmin privs.
Like you, we have to keep all the data virtually forever.
Not that's more in line with what I've seen. The auditors never got access to the database at all. They audited what we gave them. We collected their requirements, wrote the queries, provided them data and they audited it. The SQL was, of course, available for them to inspect if they wanted it. They did an occasional audit of what we gave them by watching us run a query and copy the results to compare what we gave them originally, but nothing further.
I can't imagine giving an auditor permissions to query the database themselves. I'm not saying it doesn't happen, but I've never seen and I don't really want to.
We never gave auditors access to any part of our live database either. Partly because, we have hyper security people. But also because, from what they said, it could taint the audit somehow. I don't know why, but then again, I've never been a professional auditor, just a DBA.
There was also the performance aspect. They tended to run lots of queries many times and different ways, and I didn't want all that activity on our active prod system. Also, the format of older data was sometimes different from current data, as the table structures changed, and so merging in current data was not a "plug and play" kind of thing.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply