End users on our warehouse log in using their own, personal oracle account and have the ability to create objects in their own schema - so they can test out analysis ideas without harming anyone else. I had to tune one of the pieces of SQL a user was running the other day and it involved links between some tables the user had created in their own schema and those in the public warehouse schemae and unfortunately, when I went to do get an execution plan using EXPLAIN PLAN it stopped me in my tracks because I couldn't see the objects in the end user schema - I don't have SELECT ANY TABLE privileges in my personal Oracle account.

So, the options I thought of were:

1. Get SELECT ANY TABLE privilege granted to me so that I could do the tuning for this query and, indeed, any query that I will come across. Not something the security people would be happy about which I can understand.

2. Get the end user to grant me SELECT access privilege on the objects - also possible but a bit painful if there are lots of objects and I'd need to go through this hassle every time I had a tuning requirement

3. Get the login details of the user and log in as them - a security minefield and not really practical.

So, no brilliant solutions there, until my teamleader Tank suggested that we create a trigger AFTER CREATE ON SCHEMA that would do the necessary grant(s). Phil from the DBA team had a look at it and found some code from Tom to do this and it works great.

It interested me as a solution because I remember reading recently on Tom's blog how much he hates triggers and that he'd love to have them removed from the database. He did say that "triggers are so abused - and used so inappropriately" but there are some occasions when they are useful and perhaps this is a good example.

Labels: code