Re: Oracle Security

In article <3u6q6m$3ug_at_mother.usf.edu>, smorrow_at_dotrisc.cfr.usf.edu says...
>
>Greetings All,
>
>We want to ensure that our users access a database thru a particular
 application,
>and not thru SQL*PLUS, or some other Oracle or 3rd party reporting tool or
>database residing on another host. Users will be restricted from the UNIX
>command line, and thus won't be able to execute commands.
>
>I understand that the PRODUCT_USER_PROFILE table will allow you to 'disable'
>what user's can do within SQL*PLUS, but can other tools be specified? And
>moreover, is there a way of restricting access from other programs/DBs/tools?
>Does SQL*NET have such a capability to filter out users NOT coming in thru
>this application?
>
>Any answers and/or comments are appreciated!
>
>Thanks,
> STEVE
> ____ ___ ________ ________
> /_ _] /_ ]/ ______)/ ______) _/| Steve Morrow, Sr. Programmer/Analyst
> / _/ / / / (____ / /___ _/ | University of South Florida
> / _/ / / \____ ) / _____) _/ | Internet: smorrow_at_dotrisc.cfr.usf.edu
> / _/___/ / ______/ / / / _/ | Voice : (813) 974-6889
>(_________)(________)(___)______/ |______________________________________

1st, I know of no way to absolutely insure that no one can access the database. If you're on client-server, there are a lot of client-server tools that allow access.

If its a machine with database and tools on one box, removing or limiting access to all executables might work.

Internal to Oracle, you have a couple of other choices.

        In 7.0 and up, use a trigger on all the tables. Look at v$session and v$process and determine if the user is using the correct application. If not, don't allow changes. Unfortunatly, this does not prevent selects.

        Use stored procedures to do much of the work. The calling application can pass in values to validate the call. Combine this with roles enabled by the application can be very effective.

        Also, use views to encapsulate the data. A single table view with the "with check option" included can be a powerful way to prevent even select access. For example - create a validateuser table which allows a user access to data. A procedure can pass the logon id and program data and store the username in the table - and ensure that only the person is only on once. Then create a view on the actual data table with the following sub-query:  where exists (select 'x' from validate_table v where user=v.login_id and v.program_name ='Secure Program' with check option.

The data from the view is only accessible if the table "VALIDATE_TABLE" has an entry for the current user and that row has the words "Secure Program" in the program_name column.

-- 
Bob King - rking_at_dfw.net
business ph. - (817) 551-8223