Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: