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.

Tx

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.

  1. People are logging in with tools other than SQL Developer, build something that works for everyone
  2. 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!’

Ahh, so that's what Jeff is doing today.

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.

Code Snippets save you typing. Use them!

But wait Jeff, why in Codd’s name are Devs in Prod?

tweet this quote

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

4 Comments

  1. Seriously, this is why I want to disable SYSTEM, SYS, and exterminate the use of shared accounts and “standard passwords.”

    I worked in one place where they used logon triggers to block non-DBA users on certain machines – based on their client machine name, because everyone logged in as SYSTEM. The idea of the logon trigger was to stop the rampant tweaking and customization and alteration happening on customers’ hosted instances. Not surprisingly, the “power devs” had authority to log on anywhere, and the instances and schemas continued to fragment.

    • shared accounts – bad

      Why disable SYSTEM/SYS when all you need to do is change the passwords? Why write triggers when all you need to do is take the car keys away from the 12 year olds?

  2. “People are logging in to do X but end up doing X, Y, & Z”

    This reminds me of the shutdownrestart reasons on Windows Server. Who actually ever fills those out?

Write A Comment