Preventing programs from connecting to the database

We have an application designed to insert, update and delete data, and a user's code is inserted in a column called OPER_NO to show who recorded this row. But there are two programs (SQL Advantage and Powerbuilder) that users can use to connect to database, and they can insert, update and delete anything without tracing. My questions are: 1) How can I prevent these two program from making any changes in the database if users connect with them, and 2) How can I prevent them from connecting to the database?

    Requires Free Membership to View

    Login

This is often a problem where the Sybase username and password is provided to the user without the application somehow encrypting it. Normally the users have an account on the application, which is different from their Sybase logins.

It appears that your application has an auditing feature, which is invoked when the user logs in through the application, but no such thing when the user connects directly to the Sybase. There are few ways of stopping this from happening if you are using Sybase 12.5 or higher by means of login triggers. In login triggers, you can easily write the code which identifies what program the user is accessing the server with (isql, DBArtisan etc) and then can kill the process immediately.

If you are using the older versions of Sybase, you can lock these logins by means of sp_locklogin <login_name> to stop them accessing the database. You can write a simple shell script which runs in the server every minute and identifies from master..sysprocesses.program column which user process is logged in via isql, etc., and kill the spid of the process (make sure that you exclude 'sa' login etc). This should be fairly simple to write and test.

For More Information


This was first published in April 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top