Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks for this! –  user1578653 May 7 '13 at 16:34

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.

share|improve this answer
    
A bit expensive to host too, as Oracle XE's license limits you to one instance per "server"... see docs.oracle.com/cd/B25329_01/doc/license.102/b25456/… –  Colin 't Hart May 7 '13 at 17:38
    
@Colin'tHart Indeed, thanks for adding that bit. –  Leigh Riffel May 8 '13 at 12:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.