Using roles to prevent disaster

By | April 8, 2013

I was reading Rene Antunez’s post about some things you can do with tools you use to help make you aware when you are in Production. The idea is that you want to do everything you can to prevent yourself from getting confused when you are working with several different windows open at the same time. Running that TRUNCATE command in the PROD window instead of the ALPHA window by accident is not what you want to find yourself staring at.

This reminded me of a trick one of my old colleagues taught me.. Set up one role that provides “safe” privileges (e.g. select-only) and one that provides elevated privs (e.g. insert,update,alter, etc). The key behind this is the little known fact that not all roles have to be enabled by default. If you check the *_role_privs views, you will see the column “DEFAULT_ROLE”. This indicates whether each role is enabled by default (when the user logs in). The trick is to set up the “safe privilege” role as being enabled by default and the “elevated privilege” role as not being enabled by default on Production. You can do this with the ALTER USER command:

ALTER USER cmartin2 DEFAULT ROLE read_only_role;

Now on Prod, when you log in you are restricted to only doing selects and such until you intentionally enable your other roles:

SET ROLE ALL;

The idea is that your user still has all the privs that you need to do your job, but it ensures there is a conscious decision being made that you do indeed want to use your higher privs at this time. Obviously this isn’t fool-proof either, but if it helps to prevent one statement from accidentally running in Prod, it will be more than worth it.

There are a couple other options (like specifying multiple roles, ALL, NONE, or ALL EXCEPT) that you can read about in the ALTER documentation and SET documentation.

Leave a Reply

Your email address will not be published. Required fields are marked *

Turn on pictures to see the captcha *