Re: Restricting ODBC access to Oracle

As I think may have been mentioned earlier, give your users no access directly..

Have your application ( in the first form presented to the user) grant the needed roles and have your application revoke them upon exiting ...That way no user can access the data outside of the app.....( a tricky user could, if they have sqlPlus or an ODBC program, start it while still in your app and use the granted role that way, of course - limit this with the restrictions set up by Product User Profile - disable all commands for that user...see the docs since its been a long time since I used it )  

Andreas Necker <Andreas.Necker_at_isb-ka.de> wrote:

>hi,
>
>bhogak wrote:
>>
>> Thats what we do too. All our end-users use our Forms to access the
>> database and we grant roles to all users based on their Job duties.
>> However, lets say a user has a role which allows him to update a table,
>> it would also enable him to update that table using ODBC without any
>> validation. Hope my question is more clear now.
>> Bhogak
>
>you can protect your tables with separate views for read access
>(odbc, sqlplus) and read/write access (your application).
>
>in the where clause of the read/write views can you check the progam
>which wants to access the table.
>
>CREATE OR REPLACE VIEW read_write_on_table AS
>SELECT * FROM table
>WHERE EXISTS ( SELECT 1
> FROM V$SESSION
> WHERE UPPER( PROGRAM ) LIKE '%APP.EXE'
> AND USERENV( 'SESSIONID' ) = AUDSID )
>WITH CHECK OPTION;
>
>(it only works if nobody renames his sqlplus.)
  Received on Thu Jan 11 2001 - 14:17:44 CST