We have an Oracle database that has one main Schema "MAIN" (we will call it that for now). I need to allow all the users in the company to access this schema like it is there own.
ex: SELECT * FROM TABLE
I can not have them fully qualify the Table like "MAIN.TABLE". The question is what is the best way to accomplish this task?
At first we were just going to use the user "MAIN" and use that connection string, but that has many obvious downfalls. There is no way to audit user actions on the database. Also with our report engine that exists on an Oracle Appserver Portal all of the users end up with the same SID, which is not good. If one user is running a long report all other users must wait until that report is done, then their report will run. Our server has 4 CPUs and one SID will only use 1 of those, so it really hurts performance for those users.
My current thought is to create roles for the different types of users that will be accessing the DB so we can stop a user from doing something they should not be doing. The one problem with this is that I was still going to have to fully qualify tablenames. So the next step that I was going to do was to create PUBLIC SYNONYMS for all of the tables, procedures, functions, etc in the "MAIN" schema.
So the question is: How do you let users access your database schemas? Do they have to fully qualify the tablenames? What steps should I take for user admin?
I can increase points if need by. BTW - the reason I can not fully qualify names is because I am creating Forms and Reports applications.
Start Free Trial
