February 18, 2008 at 5:09 am
Let me first come clean: the practice in our firm is to install SQL Server 2000 on client networks with a blank password 'sa' by default and then set the login option to 'Mixed' mode. No login accounts are created and all application users connect to the db via ODBC System DSN using 'sa' account.
I know this is wrong, although the practice has been there since the firm's inception long before I joined. I have read up on SQL Server administration and familiarised myself with user accounts, rights, roles, and permissions.
I created a user account "user1" on a target db and set my ODBC connection to use it but then I was not able to do any db processing from my app even though I had assigned all roles (on a test basis, though I honestly wouldn't know which ones should be restricted in the real world). I could only get the new account to work with the app by giving it all rights to all tables in the target db.
My app has a security module that allows admin users to grant/deny users access to selected modules. Each app user has a unique login which is used to log activity, so the 'sa' account does not appear in log columns.
My questions are:
1. Why is it necessary to manually assign rights to each table even after assigning roles?
2. In a real world implementation, it would be difficult to know how many user accounts to create and what combination of rights and roles to assign to them. How do others get round this?
3. I want to shift activity logging from the app to table triggers. Do I need to use Windows login to achieve this? If not, how can I pass the app user's login to SQL Server to insert the log entry.
4. At the moment, the ODBC DSN connection login data is fixed and resides in a string constant in the VB6 app. The same login data (UID=sa;PWD=;) resides in the DSN connection on the System DSN entry in the ODBC Data Sources page. If I create multiple accounts or switch to Windows authentication, how would I pass the different data for each user to make the ODBC connection?
All help will be highly appreciated.
February 18, 2008 at 9:51 am
If you assigned all roles that were there, then you have db_reader and db_denyreader, meaning you've granted yourself rights to read from all tables and then removed them.
The way it works is that you create a role and assign the rights needed to that role. The role might not need rights to every table, nor every right. So you create those roles as needed. It doesn't take that long, but it does take some time.
Then you grant users that roll to get rights.
If everyone connects as "sa" to SQL Server, regardless of what the app does, then you can't really log things in SQL Server. Every user appears as the login they connect with.
As far as logging, what is logged? Triggers might not work, depending on what you are trying to achieve.
As far as DSNs go, if you use "user DSNs", then you set them up for each user. If you use Windows Authentication in the DSN (user or system), then the credentials from the Windows login process are passed to SQL Server.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 19, 2008 at 1:57 am
Steve, thanks for shedding light. I had almost despaired.
Ours are small business apps where the client can give assorted rights (any combination) to their staff. We handle it on the app side by disabling access to denied options. I can't even begin to think of the number of ways that could be translated in db rights (the number of roles it would entail creating). And since the users are usually too basic to even understand the apps, leave alone SQL Server, it is not such a great risk.
Say I create two roles, 'user' and 'admin', with 'admin' having pretty much the same rights as 'sa' and 'user' with a few less on sensitive tables. Then I create a user account for each role and an associated DSN. How can I configure my app to select the appropriate DSN/user/role for connecting to the DB at the time of login based on the app user?
And how would the alternative way of going through Windows authentication work with the DSNs?
February 20, 2008 at 2:11 am
goodguy -
You've come to the right place for advice and it sounds like you're making some good progress at this point.
However, the one question that I just have to ask after having read your initial post is whether or not you've raised the insecurity of your companies application as an issue with management? (and curious as to what their response was if you have). You've mentioned that your clients are usually small businesses and that their use of the application/knowledge of SQL somewhat limits the risk of running as SA, but...
I don't know if others will agree with me or not but in my opinion continuing to install/failing to correct a known security issue with a commercial application could very well make your company liable should the shit ever hit the fan (e.g. client database server gets hacked/owned).
I'm no lawyer, but running with a blank SA password is such a clear violation of best practices/recommendations which have been around for years that I'm pretty sure that a plaintiff's lawyer/prosecutor could make a pretty decent case for professional malfeasance/malpractice on your companies part should push come to shove.
If your clients are in financial or healthcare related businesses (or any business where they store certain types of personal or bank information) or a substantial number of your clients serve citizens of states with strong privacy laws like California it could get really ugly under the wrong circumstances.
I know it sounds like a stretch but in our sue happy society you'd be very surprised what people will sue over and the costs/damages that can result. A few years back I was very peripherally involved when a small company (a client of a client) "lost control" of a database containing information on 8 million California residents - the small company had to send out 8 million first class letters to the affected people at a cost of more than $3 million after which they tried to sue everyone in the food chain - we were clear as was our client but I'm pretty sure the small company eventually got something from their IT support contractor(s) years after having gone out of business due to the $3 million hit.
Joe
February 20, 2008 at 5:13 am
Thank you, Clifford, for your valuable contri. I just hope there are no malpractice lawyers snooping these forums to come after my employer using this post.
Your views are very valid, and yes, I have alerted management to the pitfalls. I have carte blanche to remedy the situation and that is why I need answers to my questions.
Any help appreciated.
February 23, 2008 at 1:32 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply