Sorry, I didn't see that part, i concentrated on the first part.
The above situation is
very very bad. Terrible. A DBA has extremely powerful privileges. In your installation, I could fire up any GUI database tool I wanted, connect over that odbc connection, and do
anything i wanted to any object i wanted to.
It is, in my opinion, a security hole big enough to fly the space shuttle through -- 10 of them across even.
What I suggest is
o you immediately change the password for this account, disabling all access to it except by valid users.
o setup another account, say "APPLICATION". Grant CREATE SESSION and CREATE SYNONYM to this account only for now. Nothing else.
o in the SHAILESH account, grant the barest minimal set of priveleges the people need on the tables. For example, if people should only QUERY table T and use a procedure X to insert/update it --
GRANT SELECT ON T to APPLICATION
GRANT EXECUTE ON X to APPLICATION
do not "over" grant -- don't give them insert or anything on a table if they do not ABSOLUTELY need it.
o in the APPLICATION schema, now create synonyms for all of the objects
CREATE SYNONYM T for SHAILESH.T
CREATE SYNONYM X for SHAILESH.X
Now, change your ODBC connection setup to connect as application/application.
that is step #1. Now you have at least made it so that your end users cannot accidently drop your tables or really do some serious damage. It is not optimal but it is something you can do right now, right away.
Now, as you move on into to future, you should figure out how to use multiple schemas (i am sure that every end user does not need access to every object -- you probably have some set of roles). These schemas would be setup like application above -- with the barest minimal set of privs to do their job. People would start using these schemas instead of all in one. You might have a "clerk" schema, an "admin" schema , a "manager" schema and so on -- each with different capabilities.
To take this further, you would start removing the ability to insert/update/delete tables from the application. You would move ALL of that logic into stored procedures. Now end users must use your logic to change the data, they cannot log in using sqlplus or any other ODBC tool and doing direct inserts/updates/deletes. You would create these procedures, grant execute on them to the appropriate schemas and then REVOKE insert/update/delete on these objects from these schemas.
To take this yet further, you would do the same to queries. Instead of having the query in the application -- the application calls a procedure that returns a ref cursor (see
</code>
http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>
for examples). Now, you can remove SELECT priv on tables as well. They must go through your application to query the data. You could create reporting views and what not in the SHAILESH schema and grant select on them to the correct schemas but in general, the queries the application does are stored in stored procedures.
Now, when you find a bug -- you fix the procedure. All applications are IMMEDIATELY updated. Tune a query -- fix the procedure and all applications are IMMEDIATELY updated. No more do you have to install and reinstall and reinstall for patches and such. Your system will be more secure, more robust and easier to maintain and tune.