Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> Restricting access to  ALL_USERS
        
(Message started by: Pete Finnigan on Dec 19^th, 2005, 2:38pm)

Title: Restricting access to ALL_USERS
Post by Pete Finnigan on Dec 19^th, 2005, 2:38pm

Hello,

I've been given a very reasonable request to restrict a specific user from viewing other user accounts in the database -- specifically their account names. I don't want the user to even know that their is a 'SYS', 'SCOTT', etc. This could be done pretty easily with VPD using a view and an application context. Unfortunately, this user has access to TOAD. When a user starts schema browser in TOAD, it lists all the users in the database and we want to control what users (if any) the user can see.
TOAD uses a query 'SELECT USERNAME FROM SYS.ALL_USERS' to generate this list. The qualification of the view is what's killing me I believe. I could create a view in the users schema named "ALL_USERS", but TOAD would circumvent this by qualifying it with 'SYS'. Any ideas of where I'm going wrong with this would be greatly appreciated.

ps.

I tried RLS, but received an error saying that objects owned by SYS cannot have policies created on them.

Thanks,

Malcolm.

Title: Re: Restricting access to ALL_USERS
Post by Pete Finnigan on Dec 19^th, 2005, 5:26pm

Dear Malcom

You could change the view all_users in the sys-schema directly to hide database users from TOAD (and other tools).

But keep in mind that databases with modified data dictionary views are no longer supported by Oracle.

Regards

Alex

---
Alexander Kornbrust

Title: Re: Restricting access to ALL_USERS
Post by Pete Finnigan on Dec 19^th, 2005, 7:32pm

Hi Alex,

I've been wondering about how far I can go with modifying something like ALL_USERS. Oracle has already gone so far as to tell me that I could revoke PUBLIC from it although they didn't mention (and I haven't asked) if they'd still support it. Just for the sake of discussion, how crazy would it be to customize ALL_USERS? I have no idea what else would depend on this view and then there are the maintenance issues around upgrades, etc. Certain tools could have some big issues as well.

Thank You,

Malcolm.

Title: Re: Restricting access to ALL_USERS
Post by Pete Finnigan on Dec 20^th, 2005, 6:56pm

Hi,

I would not think that it is a good idea to alter SYS owned views in a production environment. This would not be supported by Oracle as Alex says. There are two possibilities from my side.

1 - revoke select on ALL_USERS from PUBLIC and grant back to only those users that need it. This will likely break TOAD. What does TOAD do if it is unable to read ALL_USERS? - have you tried? If it still works then take this approach. I do not use TOAD so cannot test

2 - ban TOAD!

cheers

Pete

Title: Re: Restricting access to ALL_USERS
Post by Pete Finnigan on Dec 28^th, 2005, 12:34am

If you are in 10G and have enterprise edition, you could look into the dbms_advanced_rewrite package.
You could 'translate' the SELECT from ALL_USERS into something safer

http://asktom.oracle.com/pls/ask/f?p=4950:8:9108124877490766677::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:16873041382355

Title: Re: Restricting access to ALL_USERS
Post by Pete Finnigan on Dec 28^th, 2005, 8:41am

on 12/20/05 at 18:56:30, Pete Finnigan wrote:

revoke select on ALL_USERS from PUBLIC;

sounds wise!

Title: Re: Restricting access to ALL_USERS
Post by Pete Finnigan on Dec 28^th, 2005, 8:45am

on 12/28/05 at 00:34:23, gamyers wrote:

dbms_advanced_rewrite package

this will maybe work for "select * from all_users", but will not rewrite query like

"select username from dual,all_users where dummy is not null";

imho, dbms_advanced_rewrite is not supposed to be a security package

it also require to have QUERY_REWRITE_INTEGRITY=trusted or QUERY_REWRITE_INTEGRITY=stale_tolerated. When query_rewrite_integrity is enforced (the default and probably the safest), it will not work