Managing Permissions accessing Data

Question

Managing Permissions accessing Data

SuperJB

SSChasing Mays

Points: 628
More actions
April 13, 2010 at 8:37 am

#233872

Hello,
I'm got a Windows Application for a client that will of series of Users (User1,User2,User3). These users will be saved in my main DB.
There will be different levels of permissions that will be attributed to each User (Admin, Normal, etc...). No matter the permissions of each User, i want him/her to be able to do anything my UserInterface allows: Select, Insert, Update or Delete data (as long as the UserInterface allows to do so). So far, so good...
My problem is that, i want to limit the access of these same users in SQL Server Management Studio 2008! They can do whatever they want on the UserInterface, but i don't want anyone handling the data directly (unless the user's a DB Admin).
I've never invested much effort in SQL security, Roles, Users, Permissions, Schemas, Encryption, etc... because it was never my job 🙂 So how can i allow a certain User to access data from outside SQL Server 2008 but not within?!
Can anyone give me some pointers please?! Any idea is welcome...
Thanks in advance,
SuperJB 😎

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

Create an account for SQL access for your Windows application. Use that account in your application. Don't give any information about that account to your users.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SuperJB SSChasing Mays Points: 628 More actions · Answer 2

First off, thanks for the reply.

I gotta be honest though, i have no idea how that all works out! I'm good at anything that has to do with t-sql but i never really tried to do any kind of configuration when it comes to this "area" fo Administration...

Can you be a little more specific please?!

SuperJB

Ben Teraberry SSChampion Points: 11845 More actions · Answer 3

Generally your application will use one particular account (either Windows or SQL) that has the permissions on SQL Server that you need it to have. That's not an account that your users know anything about, so they can't use it apart from the application. Ergo, you don't need to worry about them using that credential to access the database directly.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SuperJB SSChasing Mays Points: 628 More actions · Answer 4

Hello once again...

Thanks again for your reply... I understand what your trying to say. The problem is, my system is gonna be "audited", therefore i'm gonna have SQL recording every activity with the UserInterface. One of the fields i'm gonna have save is the USER that requests the info...

So, i think your approach won't solve the problem. Or is there a way to verify the USER that's requesting the info and have SQL then use another account to return whatever is needed?

Thanks once again for your patience,

JB 😎

sap55 Old Hand Points: 322 More actions · Answer 5

Why don't u invest in a DBA? Take my advice, make your life simple, hire a DBA.

As for the solution, two options:

1. create an application role.

2. create a user on your windows active directory, grant it privs to connect to sql server...i.e create a sql server login for the user, and use the user credentials for your application.....does that make sense to you? I'm not a programmer, so not quite sure what to suggest for use in your connection string!@@!

Ok, Coming back to application role, the only disadvantage of using application roles is that the session is going to be persistent, so if your application needs to time out....well...not an option with application roles.

However, if you are innovative, im sure you can create an SP that can be called to close the session once the execution of the sql is over from your application.

SuperJB SSChasing Mays Points: 628 More actions · Answer 6

Hiring a DBA is out of the question at my company! I'm the next best thing :p

I understood what you said and that's the approach i was thinking of, create a login for all users in Windows AD and give permissions to do whatever. But this permission is just from the UserInterface point of view.

This application's gonna be installed at a client's server and i don't want ANYONE accessing SSMS2008 directly - this is what's baffling me.

How can i allow a user to do "whatever" with the data from the UI, but not allow him/her to access this data directly from SSMS2008?!

SuperJB 😎

sap55 Old Hand Points: 322 More actions · Answer 7

ok john, i have a question for you. what would you give in your connection string(windows application) if your application was set up as a user on the AD and were to connect as a user?

get this for me, and i might just be able to solve it all.

SuperJB SSChasing Mays Points: 628 More actions · Answer 8

Well, i guess my connection string would not require user or pass so that any given user could access the data. Of course, somewhere along the way between the actual request and actually accessing the data in SQL Server, there would have to be some kind of confirmation to see if user has permission to access the DB.

I may be going about this the wrong way. So please, any feedback is great feedback 🙂

And yes, this will be a WinApp, no sessions required...

SuperJB 😎

sap55 Old Hand Points: 322 More actions · Answer 9

What i mean is IF you had to set up your application,such that it passes a user&password to AD which in turn is set up as a User access to SQL Server, what would be your connection string?? Hope your getting what im trying to explain

Ben Teraberry SSChampion Points: 11845 More actions · Answer 10

john.acb (4/16/2010)
Hiring a DBA is out of the question at my company! I'm the next best thing :p
I understood what you said and that's the approach i was thinking of, create a login for all users in Windows AD and give permissions to do whatever. But this permission is just from the UserInterface point of view.
This application's gonna be installed at a client's server and i don't want ANYONE accessing SSMS2008 directly - this is what's baffling me.
How can i allow a user to do "whatever" with the data from the UI, but not allow him/her to access this data directly from SSMS2008?!
SuperJB 😎

I really don't understand what the confusion is. Are you writing your application with interpreted code like VBScript or are you writing a real Windows application? If it's a real Windows application then your connection string will be compiled ... the users will have no idea what the credentials are.

If you're using SQL authentication you will also need to configure your SQL Server to force encrypted connections to avoid the possibility of someone stealing the connection string. (Doing this is outside my realm of knowledge as I've always worked in Windows authentication environments.)

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ben Teraberry SSChampion Points: 11845 More actions · Answer 11

john.acb (4/14/2010)
Hello once again...
Thanks again for your reply... I understand what your trying to say. The problem is, my system is gonna be "audited", therefore i'm gonna have SQL recording every activity with the UserInterface. One of the fields i'm gonna have save is the USER that requests the info...
So, i think your approach won't solve the problem. Or is there a way to verify the USER that's requesting the info and have SQL then use another account to return whatever is needed?
Thanks once again for your patience,
JB 😎

The audit is a custom function anyway ... it's not like SQL Server would produce some kind of a table for you about who updated records in a table.

So ... for your issue, the actual connection you use to hit the database is always the same. Via that one connection 'Bob' will authenticate (or not) based on whether or not the info he provides matches up against Bob's data in your user table. Bob's user data in that table doesn't mean anything to SQL Server as it's not SQL logon info, it's just data in a table.

Now when Bob successfully validates against your table, your app now has the information that it's working with Bob. Whatever the app does on behalf of Bob can be logged as Bob's doing. Handling this custom "validation" is simply a function of your application.

If you can use Windows authentication it's even easier. Your application can pass the current LOGON_USER which can be checked against a list of authorized LOGON_USERs in your database. Again, this isn't a permission in SQL Server, it's just a list of values. Again, all it's doing is allowing your application to verify who it is and what they're permitted to do. You would still be using your one connection to the database that your users know nothing about. What this method would do is relieve you of the need to manage a password for each user and it would allow your users to have seamless access to this system without having to remember another user/password.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 12

You cannot give users access in SQL Server and force them to only use your application. That's not how SQL Server works.

So if you want to limit them to just your application, you can't give users rights in SQL Server. The only ways to handle this are to have your application have the rights in SQL Server, preferably through a role you create, and then store the users' names in a table and use that to drive what access you allow them to do in the application.

You can use an application role to do this, but the password has to be kept secret. If it gets out, users can connect to SQL Server, invoke the application role, and do what they wish.

SuperJB SSChasing Mays Points: 628 More actions · Answer 13

OK guys... i've been reading up on this whole PERMISSION issue. I appreciate all the feedback that I've received on the matter... I'll try my best 😀

SuperJB 😎