Re: Restricting ODBC access to Oracle

From: Andreas Necker <Andreas.Necker_at_isb-ka.de>
Date: Wed, 10 Jan 2001 13:26:01 +0100
Message-ID: <3A5C54D9.3495E3C4@isb-ka.de>

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.)

-- 
Andreas Necker

ISB AG              Tel: +49 (0)721/82800-0
Karlstrasse 52-54   Fax: +49 (0)721/82800-82
76133 Karlsruhe     mailto:Andreas.Necker_at_isb-ka.de
Germany             http://www.isb-ka.de

Received on Wed Jan 10 2001 - 06:26:01 CST