As part of our ongoing staff development programme, we have a couple of our more experienced Deski users who have now attended an intorductory SQL*Plus course with the aim of them having a better understanding of how BO constructs queries and what is actually going on ‘under the hood’. In the longer term wwe’d like them to take up some of the simple maintenance of our Universes (new object additions on schema updates, etc)
However, we’re struggling to find a way of giving them access to the data without introducing some element of risk to the database. We’d really like to be able to give them a tool that allows a tree view of the database objects and gives coloured formatting options (as most developer tools do) but allows us to restrict them to select statements only. We’ve looked at logins with read only permissions and roles but in our current environment (not something we have any control over ultimately) they could still log in using their regular username and gain full access.
We do have a test environment for them to use but again, no way to ensure that this is the one they actually log into.
So, in summary, an application that:
Has all the neat features of Toad, PL/SQL Develper, SQL Developer, etc with the facility to take out all the dangerous bits, or at least force thewm to use a single login that they can’t edit. Enough rope to learn but not quite enough to hang themselves (or us!)
All users require insert, update and select in order to use the database via the application.
Whilst we could set up a restricted user for them to use, we still wouldn’t have any guarantee that they couldn’t log in with their regular user name.
With our next version it’ll be single sign on with network user id’s mapped to a small number of roles, so at that point it’ll be far easier to implement a sql_user profile or similar, but in the interim I need to look for something else.
I know something like PLSQL developer etc would require you to setup a connection before hand. Wouldnt you just be able to set this connection up for them (pointing to a test db), and not set a connection up to the TNS entry for the live database. Your kind of trusting them then not figuring out how to create their own connections in the 3rd party app, so its a bit of security by obscurity.
I use SQL developer from Oracle and you have to know the concepts of Oracle connectivity (TNS etc) before you can actually build a connection to the database. Coz its a developer tool though, you cant really secure any of the functionality in any way.
The problem is, the TNS names file already exists on all machines (so that users can connect to the relevant databases thru the application) so its not as if they need to have knowledge of how to create the connection in order to break things.
They simply have to type in their username and password, add the name of the live database (its prepopulated on the app login screen that they normally use) and waheey, disaster just around the corner…
Knowing how i sometimes get confused which instance i’m in with multiple windows open, I’m not sure thats a risk we’re willing to take. As you say, developer tools exist to ‘allow’ you to do things, not prevent them.
We did (briefly) toy with the idea of them using freehand SQl in deski, but as the window is so small and not resizable, nor showing colours or decent formatting (teach them right from the start I reckon ) or easy access to catalogue we did feel that at this early stage we should be trying to help them rather than hinder them.
As a rule of thumb - if you don’t believe users what they do (or could do) in the system/application, then do not allow them to use production environment.
So I suggest having a test (or better said playground) environment with all tables users need for their learning, testing, … and where they can do what they want without breaking anything in the production.
Then, when they develop (basically in any SQL tool) a query they like, all they need to do is the copy and paste it into the freehand SQL box of BO.
They are users of the system, along with the other 1500 regular users so have a login that allows select, insert, delete, update etc (via the UI), and have a TNS names with all of the required connections. I can’t remove their ability to connect to the live environment without stopping them using the live system entirely by any means.
We tend to do our querying and ‘playing’ in our query environment which is copied from live every night, so even if it got accidentally truncated then we’d get it back the next day, no problem. But there’s only a small line between them logging into CFQUERY or CFLIVE, and they are most used to typing CFLIVE when logging in via the application
How about this idea, although I don’t know how difficult it is to implement or whether it is possible at all:
Ask your Oracle DBA whether it’s possible to identify which tool/program a user uses when connected to the database (I suppose this should be possible because in Toad, in session browser I can list all sessions along with the program each session uses).
Then ask DBA if it’s possible to create an on-login script/trigger that assigns a role to a user that is being logged into the database.
If this is possible then the next steps would be - give your power users any SQL tool that is unique only to them. Have the DBA to create an on-login script that checks which program a user is connecting to the DB from. If it is your SQL tool then the script assigns them a role with a limited access to data, tables, …
For someone who was once an accountant and migrated to IT ,my building blocks for learning about tables, queries and basic SQL was MS Access. It was a great playground for learning about databases. We hooked up to live tables via ODBC with a readonly user and felt we were in a very safe environment for running queries
Toad used to have a license file called READONLY.LIC in the installation media that you could place in the License directory of the developers workstation. That makes that Toad installation read-only. Of course, if the developers know it’s there, they can just remove it. But if you are trying to prevent the accidental problem as opposed to the intentional problem, then this should work.
I haven’t used Toad in a couple of years, so I’m not sure this option even still exists, but it’s worth checking into.
Thanks for that tip, it might’ve put me in the right direction.
We use Allround Automations PLSQL Developer and I just had a look in my install folder and there is a a policies.cfg file where its possible to disable the ‘drop’ option from the menu. Will have to explore a bit further and see just how much functionality can be controlled as we could then generate a custom policy file for these users.
I don’t think that you can justify the price for just training, but iGuard from Teleran is a great product which can help with stopping “bad” queries from ever hitting your database.
(BOB member since 2003-03-19)
(BOB member since 2002-06-06)
) or easy access to catalogue we did feel that at this early stage we should be trying to help them rather than hinder them.
(BOB member since 2003-03-19)
(BOB member since 2003-11-27)
(BOB member since 2006-03-30)