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
- Dozens more answers to tough SQL Server questions from Mich Talebzadeh are available here.
- The Best Sybase Web Links: tips, tutorials, scripts, and more.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, Sybase, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Open source databases
Related Q&A from Mich Talebzadeh
Sybase expert Mich Talebzadeh explains how to log on in ASE.continue reading
Sybase expert Mich Talebzadeh gives fifteen reasons for why Sybase will definitely be around for years to come.continue reading
Sybase expert Mich Talebzadeh explains the syntax for user-defined Sybase functions.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.