Re: Keeping users out ...

Hi Michael,

You wrote:
>
> Well, not keeping them totally out, but only allowing access through
> an application.
> Has anyone any suggestions how to deal with/improve on the following?
>
> The application will give users access to the database and grant them
> rights as appropriate to their known roles, but I want to ensure they
> have no rights whatsoever as users who connect from outside the
> application.
>
> So, the application goes through a login process (username & password)
> to allow the user to be identified and granted use of the application
> according to its already-defined menu system. The application will
> also connect them to the database (essential for the user to actually
> use the application) via ODBC. Some users will need read-only access,
> some others will need insert/update/delete. All their needs are known.
> I'm expecting to be able to give all users execute rights to a stored
> procedure which itself has dba rights so that it can grant/revoke
> access levels according to the user's profile. The initial actions of
> the application will be to call that SP for the user. Any comments?
>
> If the above works OK, there is one (perhaps more?) other
> consideration -- I don't want them to be able to connect to the
> database from outside the application and have any rights whatsoever.
> I'm assuming that the rights they'd be granted when connecting through
> the application could be session-limited - i.e. temporary and
> available only through that instance of a connection. If that's so,
> then their user-id could be set to have a default of no access at all,
> and that would probably work ok. Except they'd still have execute
> rights for the SP ...
>

If I follow you this far, I see an easier solution in the definition of a single database account for the application. This account can do anything, at least on its own tables, but it might even be a DBA-account. Application users would not get a database account.

Application users would simply connect to the application, where an authorisation structure is in place for them (that would be the basis for the DB account, in your plan). In my plan this authorisation structure would be used to autorise database actions, instead of creating the additional accounts.

Unless I am missing something, this seems a lot easier to me...

Regards,

Ruud.

> I'd like to be able to connect them to the database (when via the
> application) using a dummy id, perhaps their real id + a random
> suffix, so that they'd not have any notion of a useable login id ...
> perhaps a little paranoid ... perhaps not actually achievable.... (The
> app. would first have to connect as dba, add the dummy user-id,
> disconnect, connect as dummy user-id, allow user normal use,
> disconnect, connect as dba, erase dummy user-id ... but if the scheme
> fails somwhere, there may be potential for said user to end up
> connected as a dba ... and for dummy user-ids to persist because the
> dba housekeeping didn't complete)
>
> Any ideas and contributions?
>
> Michael Russell

-- 
--------------------------------------------------------------------------------------
Ruud de Koter                    HP OpenView Software Business Unit
Senior Software Engineer         IT Service Management Operation
Telephone: +31 (20) 514 15 89    Van Diemenstraat 200  
Telefax  : +31 (20) 514 15 90    PO Box 831
Telnet   : 547 - 1589            1000 AV  Amsterdam, the Netherlands
Email    : ruud_dekoter_at_hp.com

internet: http://www.openview.hp.com/itsm
          http://www.openview.hp.com/assetview
intranet: http://ovweb.bbn.hp.com/itservicemanager
--------------------------------------------------------------------------------------