Printed 2017/08/20 07:18PM

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

By Hugo Shebbeare, 2009/02/15

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...):  SOX for SQL Server DBAs Reaching Complaince (PCI/SOX)

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.