Problem solve Get help with specific problems with your technologies, process and projects.

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?

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

Dig Deeper on Linux servers

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.