December 8, 2003 at 9:14 pm
I have built necessary logic in to my application to have a separate User Table for the application. I have only one user to log in to SQL server and the individual users have separate User Name in the application. The SQL-server-user-name is hard-coded in the application, and on log-in the Application-User-Name is entered by the users. The password is tested against what is there in the User table in the db and permissios are verified for each form and report from a permission table which has the user-ids and screen/report ids.
Any body have any comments on this ? Will the single-user affect the performance of SQL-Server?
December 9, 2003 at 4:29 am
To the best of my knowledge no. Each user will connect to SQL server using there own SPID.
If it aint broke don't fix it!
Andy.
December 9, 2003 at 9:49 pm
Thank you AdiCohn for your comments.
I am experiencing two problems in changing the application-user to Sql-user.
1) My system is too complex having almost 350 tables and 80 or more data entry screens, 200 more report screens. It is something like an ERP system. In Sql server I can give only table level permissions. So if i want to give permission for a data entry screen, in SQL server i have to give permission for the underlying tables. In many cases, more than one table is updated in a single data entry screen, and also the same table is used in more than one entry-screens. In this case table level permission is nearly impossible. I have a user table, a screens table (with names of forms, reports etc) and a permissions table. Whenever a user is selecting a screen, I am just checking the permissions table for his user_id and the screen_id he selected. if a row is there with both these, then he is allowed to use that screen.
2) Actually this 2nd one is a problem which I found during the early days of developing this system. I haven't tried to overcome this as I am using a single User Name for SQL. The problem is like this. User1 has created a table 'Supplier' and he can pass the querry 'Select * from Supplier'. But User2 has to pass the querry "Select * from user1.Supplier". Is this a problem??
December 10, 2003 at 9:58 pm
AdiCohn, I am not disagreeing with you. The problem may be that I have not used the options in SQL Server. When I am developing the next system I will try to utilise the SQL server provisions. Thank you for sharing your experience.
December 10, 2003 at 10:12 pm
A few points. None of this is meant to be harsh, so I apologize if it may come across that way.
1) Utilizing the same user to login to SQL Server allows for OLE DB resource pooling. This reduces the number of connect/disconnect operations which require both memory allocation and processor utilization. That's why this is a common technique. In this case you do trust the application to handle security.
2) If the username/password is not given to the users, they cannot use it to log in through Query Analyzer. As long as the username/password is kept protected, this is typically not a concern. That said, it can be difficult to keep the username/password protected from the savvy user, depnding on the type of app.
3) SQL Injection boils down to an input validation issue. Even restricting permissions to stored procedures still allow for an attacker to do an awful lot of reconnaisance with the default permissions of various system objects and stored procedures. While it is a best practice to control all database access through stored procedures, not doing so does not immediately open up the database to SQL Injection attacks. As long as proper input validation is performed by the application, SQL Injection can be prevented. The reconnaissance issue is often missed. SQL Server 2000 is not well-protected from information disclosure of the schema.
4) Hard-coding the username/password is typically not a best practice. If the username is compromised, changing out the username/password may prove impossible. What can be done is have the conenction string encrypted and stored in the registry. The application would know how to decrypt the string and establish the connection. Should the user account be compromised, a new connection string can be generated and the registry key modified with its encrypted value. Getting the username/password isn't necessarily hard, but it's not always trivial, either. It really depends on this one.
5) When you have the option, you might have a service account and rely on Windows authentication. You'll still get the benefit of OLE DB resource pooling but the password won't be hardcoded in the application.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 11, 2003 at 1:54 am
Mr.Bkelly,
Please correct me if i am wrong on assuming the following, based on your queries:
1) Having a single user to log-in to SQL server will not have any adverse effect on the peroformance.
2) The user name and password is not disclosed to the users. So to a certain extent, the system is secure.
3) In my case, Proper input validation is made by the application for each user. Evenif we are depending on SQL server security, there are some loopholes.
4) Can anybody give some help on storing the encrypted connection string in the registry ?
5) I cannot rely on windows authenticaion, as we have other servers running on the same network and there are users having the same username and password for all the servers!!
December 11, 2003 at 6:54 am
It is important to remember that the application is vulnerable to SQL Injection, not the database. The root of the problem is input validation. Should all input be properly validated, SQL Injection cannot occur from a given application.
I don't disagree with you at all on the way to handle permissions in the database. Best practices say to create stored procedures, grant permissions to execute those stored procedures, and ensure no permissions are granted to the tables. But this, in and of itself does not prevent a SQL Injection attack. An attacker may garner a lot of information by appending SELECT @@VERSION to a recordset returned by the app or by querying sysusers. SELECT @@VERSION tells the SQL Server version to the hot fix as well as the OS to the service pack. Knowing these two bits of info means an attacker could come at the SQL Server using a known vulnerability if anything isn't up to the latest and greatest.
The username/password issue is why Windows Authentication is often considered a best practice. If you have COM+ objects, they can be configured to run under the identity of the Windows account. Similarly, if the app is a web site, the anonymous user account for the web app can be the Windows app. This typically prevents the username/password retention issue.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply