I received a very interesting question from a reader the other day, which is awesome for two reasons. One, it gives me something to think about and play with. And ‘B’, it provides me with material for today’s blog post.
So here’s the original question -
We would like to capture inofmation like who, when and why a user connected to a database in production using SQL developer, so i was wondering if is it possible to customize the “Connect” option to prompt the user to enter the “Why” information at the instance when he/she connects to a database? I know “When” and “Who” can be captured behins the scenes via monitoring but we are also intersted to see if there is a capability to put additional information such as “Why” within the connect option in the SD interface.
The direct answer to this question is ‘No – we do not have that ability today.’
We could add it I suppose, but it probably wouldn’t receive my support. Why is that? Surely it would be a good reason for the DBAs to know why there are developers in production, right? My gut reaction can be boiled down to a few bullet points:
- DBAs shouldn’t accidentally happen upon a developer in Production. They should be contacted by the dev before the connection takes place. ‘Hey buddy, we got a coolant leak down here in engineering, and I need to crawl around in the jeffries tubes to replace some bad isolinear chips.’
- If you don’t trust your end users in production instances, don’t give them production accounts
- There’s already a way to do this
DBAs shouldn’t accidentally happen upon a developer in Production. Tweet this!
I want to spend a second on that last bullet point -
There’s already a way to do this!
DBMS_APPLICATION_INFO.SET_ACTION() allows the user or program to identity what task they are currently working on. I don’t think it was originally intended to be ran ad hoc by an end user in an IDE, but I can’t think of a good reason why you shouldn’t.
There’s another reason why I’m not a big fan of building this into the SQL Developer connection dialog. Well, two actually.
- People are logging in with tools other than SQL Developer, build something that works for everyone
- People are logging in to do X but end up doing X, Y, & Z
So once you’re logged in, take a second to run
begin DBMS_APPLICATION_INFO.SET_ACTION (action_name => 'Your job, apparently'); end; /
I highly recommend you use a better string. I’d hate to hear that I have somehow increased any tension between developers and production DBAs. Once you’ve ran this, you can see in V$SESSION what’s going on. I imagine most DBAs have some sort of monitoring solution or script that routinely gives them a run down of ‘who, where, when, etc.’
Now you can supply the ‘why!’
The nice thing with this solution is that you can run it as many times as you want. If you force someone to use it at connect time, they’re going to start getting wise on you. Like folks that have to account for their time in 15 minute increments for work and log the 25 minutes they spent that week on timesheets. You may end up getting some joker like me telling you that ‘need to check the time, my watch died.’
Now, you can make this a bit easier in SQL Developer – just add a ‘Code Snippet.’ Then you can drag it to the Worksheet anytime you’re about to do something else in production.
But wait Jeff, why in Codd’s name are Devs in Prod?
I’ve got a blog/rant on that very topic. We don’t like it, and in most cases we probably have to learn to live with it.