SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Importance of the Segregation of Duties with Respect to Internal Controls

I diverge a little from the typical coding-based/oriented best practice to one that is focused on governance within public or government organisations with respect to the security of the data in databases used for annual reports. 
Internal controls fall under the general COSO framework, and are more explicitly defined in the U.S. SOX Act. One of most important points of SOX compliance is the segregation of duties (SOD) - which basically means developers do not have access in production and that specific roles are followed when developing code before placing in production. Territorial as it may seem, it is necessary for Physical DBAs to control production databases, and without fail, follow change management practices such as is defined by the COBIT framework (or ITIL) to avoid the risk of data being inadvertently, mistakenly, or maliciously changed due to ad hoc changes.  Methods, in which we govern ourselves in a controlled environment, with respect to the production systems, are by following procedure and documentation  by the COBIT or ITIL standards. Each task has to be validated by a third person (code walk-through/fresh eyes), who is not writing the actual code. The database developer themselves should not be able to execute anything in production directly without an independent review of the documentation/code for the work which being performed.  Typically, the role of the developer is to pass on their code to a database administrator, which, knowing this economic climate, might not be at easy reach (at least have a peer review if a DBA is not involved). This division is to ensure that the role of the creator / executor is clearly segregated. 

CDP Capitol, Caisse de Depot Montreal Office

If your organisation is in denial or lacks understanding of internal controls, please remind management that databases which fall under internal controls (that is the data us used for public reporting, annual reports, etc.) are subject to the segregation of duties. Furthermore, to track all changes and have recoverability, a bare minimum for a SQL database recovery mode is Bulk Recovery - so if your respective ‘friendly’ DBA states he can recover all those transactions that developers have gone into production to do a quick 'fix' while under Simple recovery mode, then it's simply not possible.  Make sure to backup all your transaction logs and keep them indefinitely, the auditors will be very happy that way too.

Another point of internal control is the adherence to the principle of providing the least amount of privileges, especially in production. To allow developers more access to get their work done, it is much safer to use impersonation for exceptions that require elevated privileges (see EXECUTE AS to do that temporarily), instead of being the typical lazy DBA and giving DBO to everyone:) Giving full access means you are not managing security, needless to say. 
Often developers may dismiss this as 'overhead' while on their path to coding glory, or to shorten the treadmill - I do not want to judge - but please be aware that DBAs are going to do all that is considered responsible since we are the data stewards of the organisation (not to mention comply with regulations and the law).

I shall be updating this posting progressively with more references thanks to the help of my ex-colleague from Dell, independent Senior SQL Server DBA Pollus Brodeur (whose father is co-architect of the building in the above photo, in fact), since he’s the one who originally taught me all about auditing/SOD/Internal controls a couple of years back during our stint together for Tata Communications.  Along the same lines, Robert Pearl, for the sake of not adding yet another reference link below, gives a great SOX compliance checklist also.

 Don't take my word for it, check out the reference material here (ah, such a Liberal Arts student...):

http://www.mssqltips.com/tip.asp?tip=1300  SOX for SQL Server DBAs

http://www.microsoft.com/downloads/details.aspx?FamilyId=6E1021DD-65B9-41C2-8385-438028F5ACC2&displaylang=en Reaching Complaince (PCI/SOX)       









Posted by bob.willsie on 19 February 2009

SOD has been a basic principle in well managed organizations for accounting transactions for years.  But, what about non-transactional coding, IE, reporting?

Should it also always fall under strict SOD controls?

If not, where does one draw the line?  

Having spent years creating "quick" reports and data extracts, it's always been extremely productive to just "make it happen"  without oversite.

However, I have also had instances when someone would want me to "tailor" the report or data extract to show a less than truthfull picture. My response has always been "the report is the report", or "I'll give you the full data extract to do with as you see fit, but be aware that I will also give the full data extract to anyone else to double check your work."

So, if reported or extracted data is readily and easily verifiable by third parties, is SOD oversite really necessary?

Thanks, BObw

Posted by Hugo Shebbeare on 19 February 2009

Very good point BObw, thank you for your input.  For reporting, I always use a complete copy of the production data elsewhere to 'make things happen quickly' if a specific report is required.  Just to take the snapshot away from production for the slicing and dicing of the information.

Of course, politics spills over into data management, as you take financial reports into consideration it's paramount that external verification can happen without influence over the numbers - since one can imagine how easily statistical reports can be portrayed to paint a pretty picture.

Example: The United Nations Institute for Statistics became autonomous in 1999; before that time it was a division of UNESCO.  The move away from the Head Office in Paris to Montreal came after a less than flattering report about the statistics being produced (quality and accuracy).  It was felt that by becoming completely autonomous it would be shielded from the political pressures within UNESCO.

As you say, if the numbers are suspected of being influenced (‘tailoring’ reports) by political motives, your credibility just flew out the window;  and once that happens, the reports are no longer relevant.  Nothing can be worse for reporting credibility.

I believe if you have the external verification in place, that's great, but it is separate from how code is put into production.  So SOD is not simply preventing oversight, but a pretty solid method for controlling the environment.  Perhaps it is just like democracy's imperfections, however at least the fresh eyes (analogy to the Opposition) can ensure things will not bring down production for a failure to mitigate risk.

Posted by Anonymous on 30 March 2010

As mentioned in a previous post , between the months of January and September 2008 I was hired to be

Leave a Comment

Please register or log in to leave a comment.