Oracle 10XE: Stop schemas seeing the all_users table?

Question

I've got an Oracle 10g XE Database that I want clients to be able to view remotely via SQL*PLUS or SQL Developer. Each schema has a name which includes the name of the client, so anyone looking at the all_users table would be able to see the names of all my other clients! Is there any way to restrict permissions on certain tables? And are there any other tables like this which would be a good idea to restrict access to? I'm not particularly concerned about our clients trying to hack things, I just don't want them to see a list of all our other clients!

Thanks!

Answer 1

You have a few options, some of which will make your database unsupportable by Oracle (if you care!) as you need to fiddle with SYS owned objects.

The first option is to revoke select privs from everyone:

revoke select on all_users from public;

Then grant it back to certain users, plus all other internal Oracle users.

The 2nd option is to look at the source for the view, drop it, then recreate an altered version that supports your needs.

Another option that would trick casual users would be to create a table called ALL_USERS in each schema. Unless the logged in person explicitly qualified ALL_USERS (ie: SYS.ALL_USERS) they would get the resultset from the table in their own schema. This trick could also be done with a synonym pointing to a view that does:

select * from all_users where username = (SELECT sys_context('USERENV', 'SESSION_USER') from DUAL);

Not much else you can do with XE I'm afraid.

Answer 2

Another option in your case would be to put each user in a separate XE database. Each database could use database links to another database for either for common data or for all their data. While this would satisfy the requirement and perhaps be more secure all around, it would be more complex to build and maintain.