Re: Client/Server Security

In article <32778D86.3776_at_b022.aone.net.au>, Ray Dowling <rdowling_at_b022.aone.net.au> writes
>Problem is:-
>
> To allow a user to access only that data available to him, each user is
>given a username and password. Each user has been granted certain roles
>to perform his work.
>
> The Developer Form ensures data integrity (validation) within the data
>base.
>
> Some users will have access to HP-UX to perform other functionality and
>hence access to SQLPLUS. They may then CONNECT as the granted user and
>perform the data base functions allowed (by the roles).
> OR any ODBC front-end software (eg Excel, Access) will allow updates to
>the data base (thereby bypassing the Developer Form validation) using
>the username and roles provided.
>
> How has this been solved elsewhere???
The usual method is to give each user two roles.one with full access and the other with read-only (or other limited) access. The second role is the default role so when users connect 'unofficially' they have restricted access.

In a pre-form trigger on your startup form, issue an 'alter session set role' command to enable the more powerful role for the duration of the session. When users connect 'officially' through your application they get all the access they need.

This is not perfect and there a few things you need to think about, including -

• you need a table to hold details mapping users to roles for the trigger to work from
• there is nothing to stop an informed user enabling the role themselves if they know about them, unless it is password protected
• there is a need to store passwords either in the form or in the mapping table

I have not used the above method because I inherited an alternative scheme which is to wrap the application in a program which generates the passowrd and then calls forms. This way users do not know their password, so can't log on with SQL*Plus etc. Give all users a common read-only userid for ad hoc work.

The basic functionality of the wrapper program is

Get username from OS
Generate password derived from username
execute (winexec) f45run startform username/password

-- 
Jim Smith