Re: Client/Server and Oracle Security

In article <38h65b$4ea_at_usenet.srv.cis.pitt.edu>, Chuck Seibert <seibert_at_isd.upmc.edu> wrote:
>I have a custom client/server application using a DOS 6.2 PC running SQLNet 2.0 as
>the client and a Sun Solaris 2.3 Oracle 7.0.15 database server. As this is a rather
>complex application I would like my users to use this custom front end to update the
>database.
>
>Questions:
>
>1. How can I prevent users from using other tools like Paradox for Windows,
>MS-Visual Basic, Powerbuilder, ... from updating my data as their database usernames
>are already granted privs on the tables.
>
>2. Are there 3rd party tools/products to help lock this down?
>
>Thanks,
>
>Chuck Seibert
>
>

    I dont know of any bullet-proof way of doing this but here are two     suggestions.

1. Make sure that all update operations are done only through stored procedures. Then revoke all privs on the applications tables from the user but grant execute privs on the stored procedures only.
2. grant the application privs to a role and make sure that the role is granted to the user but is not a default role. Then from within the application set the role.

     Method 1 ensures that the tables are updated in a consistent way. Ofcourse
     the stored procedures could be executed by another client, but this is
     better than just random updates.

     Method 2 makes it more difficult for the user or even impossible
     (if the role is protected by a password that only the application 
     knows :-)), But then I am not comfortable with  embedding passwords
     in code.

• Amit