Wednesday, September 10, 2008

Segregation of Duties / Ségrégation des tâches with respect to Controlled Environments

Best Pratices for a controlled environment / environnement sous contôles internes
September / Septembre 08

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 to avoid the risk of data being inadvertently, mistakenly, or maliciously changed.  Methods, in which we govern ourselves in a controlled environment, with respect to the production systems, are by following procedure and documentation ( e.g. 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. 

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.  Ceci constitue un risque de ne pas pouvoir récupérer une transaction une fois exécutée - alors le problème est un risque de nature technologique au niveau de la sécurité. Make sure to backup all your transaction logs and keep them indefinitely, the auditors will be very happy that way too.

nother 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 very shortly with more references thanks to the help of my ex-colleague from Dell, independent Senior SQL Server DBA Pollus Brodeur, since he’s the one who originally taught me all about auditing/SOD/Internal controls a couple of years back.

References:
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)       
 http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=161&messageid=212223

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=161&messageid=118657&p=1
 http://www.sarbanes-oxley-forum.com/modules.php?name=Forums&file=viewtopic&t=988  
http://www.krell-software.com/omniaudit/data_auditing_for_sarbanes_oxley_compliance.asp

http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=7888

http://www.databasejournal.com/features/oracle/article.php/3650681

http://www.sqlservercentral.com/articles/Miscellaneous/3178/