Control Adhoc access to production environments

Overview

As much as we may want to disallow all adhoc access to production environments, it is often a requirement in order to provide adequate support - especially when there is a problem. That is why we have tools like WinMSQL or Server command operations available by RedPrairie. On the other hand the mere existence of such access is perceived negatively by the auditors and system administrators.

In this blog, I will list some features that could make providing such access manageable. All of these features are available in the latest version of Oracular MOCA Client which is available at http://autoupdate.oracular.com/mocaclient

Essential Features

• Access Control
RedPrairie "Server Command Operations" application essentially provides the same capabilities as any other command level access tool like winmsql. But access to this can be controlled via RedPrairie option access control. The option name is "SALSrvCmdOpr" and only users who have access to this can access the "Server Command Operations". Every external tool like winmsql should respect this access as well. Both Oracular MOCA client and latest version of RedPrairie winMSQL respect this access control.
• Log commands to sys_audit table
RedPrairie provides a table called "sys_audit" that is supposed to provide a record to the auditor regarding access to the system. Oracular MOCA Client allows the sysadmin to force that everything that is executed is recorded in this table.
• Force a specific version
We are bound to find issues in any product and some of those issues can be critical for a production environment. A system administrator should be able to force the inbound connections to be at a certain version level.
Currently Oracular MOCA Client allows for this control where the administrator can set the minimum version level on the server and if the user's Oracular MOCA client version is not at that level, access will not be granted.
• Force auto-commit to be off
All moca clients offer control where the user can set auto-commit to be off. This implies that after running the command, the user needs to manually execute a commit. This can be a life saver if someone made a mistake! The issue is that often auto-commit is on.
Oracular MOCA client allows the server administrator to force this setting to be off. If the setting is done on the server, then the client will have it off and the user is not able to change it either. This can cause an adverse side-affect where someone may forget to commit the changes. In order to address this, Oracular MOCA Client respects another setting that allows the administrator to set the maximum time for an uncommitted transaction. If the user does not commit within that time, the transaction is automatically rolled back.
• Only allow read-only access
Oracular MOCA Client also allows for a setting where changes are simply not allowed. In this case every command is automatically rolled back. Oracular MOCA Client executes the command as follows:

   try
   {
      entered command
   }
   finally
   {
      [rollback] catch(@?)
   }
   

If the command has an embedded commit, which is generally a bad idea, that will still happen but otherwise any changes are immediately rolled back.
• Detect if the user executed a DML or DDL
The system administrator may want to fine tune some control for DML (data manipulation) commands like insert, update or DDL (Data Definition) commands like drop table, create table etc. Oracular MOCA Client allows the sysadmin to maintain a file on the server where she can provide regular expressions to define what is a DDL and what is a DML. She could, for example, put commands like "sl_change" and "create record" etc.
Along with this Oracular MOCA client then provides some additional features:
• Confirm each DML
This is an integer and user will be asked to confirm this many times with default option being No every time.
• Confirm each DDL
This is an integer and user will be asked to confirm this many times with default option being No every time.
• Additional documentation for DML and DDL
Sysadmin can define some prompts which the user will have to respond to before executing the DML or DDL. This can include stuff like "Case#" and "Description" etc. This information is logged in sys_audit table along with the executed command.
• New roles for allowing DML or DDL
Sysadmin can utilize roles to control which users can execute DMLs and which users can run DDLs.

Conclusion

Features described above offer a good starting point to provide controlled access to production instances. The sysadmins can use other techniques where they grant the full access to named users for short periods of time and then revoke it.
Bottom like is that access to production environments should be managed conservatively. It is better to provide too little rather than too much. When access is to be provided, then Oracualar MOCA Client offers some features to manage it so that mistakes can be avoided. Oracular MOCA Client also has several features for a development environment which I would list in a separate post.