On using Toad against a database

I got this question once again today in a previous post.

What’s wrong by using Toad against a database?

The worst case scenario:
– some non-technical staff is clicking around in your production database with read-write access 🙁

The best-case scenario :
– nobody has access to your database 🙂

Here is a short list on how you could protect your data :
– Give the right privilege to the right person. DBA role to the DBA, CREATE TABLE/CREATE INDEX to the developer, INSERT/UPDATE/DELETE to the application
– Restrict access to your database server. Use some firewall. Allow only the dba workstation and the application server to the Production environment

What if the end-user PC needs access to the Production database with a powerfull user? This often happend in real world. A fat client is installed on the PC, the password is somehow hardcoded, the privileges granted to the hardcoded user are uterly generous…

It is not a bad practice in this case to block access to the database server to Toad/SQLPLUS and thelike. This will very ineffeciently prevent some garage-hacker from corrupting your database, but it will prevent your sales / marketing colleagues from deleting data, locking tables and degrading performance. This could be done by some login triggers or, my preference, some administrative measures like information, auditting and sanctions.

4 Comments

  • I’m pretty biased here, but I’ll share my opinion anyway and let others judge it 🙂

    If you do a good enough job on setting up proper privs and controlling who has access to the database, it shouldn’t matter what program they use to log in.

    Also, if you create a logon trigger to disconnect sessions coming from Toad, be warned the user can make the program look like something else by messing with the binary name and other fun tricks – not that I’ve helped people bypass security checks before!

  • @hillbillyToad Thanks for the comment 🙂 Still I prefer that the end users do not get direct access to the databases and start writing heavy queries (like SELECT * FROM DUAL CONNECT BY LEVEL<1e125 :twisted: ) and use the tool (that is the application) that is designed for them.

    You are absolutly right, controlling who has access to the database is the most meaningful measure !!!

  • We can both agree here – users should access PROD via the application. If they want to do their own evil magical queries, build them a reporting instance!

Leave a Reply

Your email address will not be published.