Monthly Archives: April 2013

DAY-O Presentations

I have finished my presentations at DAY-O and think they went fairly well. I got a good amount of participation and even better questions. Their members do a good job talking with each other about new features or issues they are having, which is really what Users Groups should be about. I look forward to returning in the future, and thank their board for putting together a well run event. The presentations are available in to my presentations section. If you attended and have any feedback for how I can improve in the future, please share in the comments or via email.

Upcoming Presentations

I will be presenting twice at the Dayton Oracle Users Group on Thursday, April 25. My presentations will be on reading execution plans and SQL Tuning. I have never presented at Dayton before, so I look forward to meeting the people there and seeing what kind of group they have going. Check out the full meeting details or join their LinkedIn Group.

I am also very excited that I have been selected to present at the Great Lakes Oracle Conference in Cleveland again this year. I will be presenting my SQL Tuning presentation there as well. This conference was great last year, and looks significantly better this year. The NEOOUG is doing a great job at putting together the best conference in the Midwest… just check out the presentations! Also, Tom Kyte and CJ Date will be there giving the Keynote Addresses. I have never had the chance to see CJ Date in person, but have heard nothing but great things. The conference is May 14 and 15th with pre-conference workshops by Tom Kyte, Craig Shallahamer, and Joel Kallman on May 13. If you are interested in attending (and you should be), use the code “ATTENDGLOC” to get an additional $25 off.

If you happen to be at either one of these events, make sure you stop me and say hello!

Using roles to prevent disaster

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:


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.