November 22, 2012 at 1:37 am
Hi everyone,
I am new here and have looked for an appropriate answer to the following two questions but cannot find it, so please can you help ?
I understand the difference between SQL server logins and users and have come across many articles around the use of both, but cannot find the right answer to my scenario (which I am sure is extremely common).
Scenario 1: For various reasons my first application cannot use Windows logins, but must use the SQL Server logins. The users are a small group (max 10) and are known to me. Now, I want to be able to "track", through triggers and audit tables, which user changed what on a database and need to be able to identify the user. The one option is to create a different login and user for each of the applications' users. This I can do and it does not seem difficult. What seems to work the best is to then add those users to a role in the database so I can easily control what access they have. This also gives me the ability to write their username or loginname into an audit table together with what they have changed. Question: Is this the best way of doing this ?
Scenario 2: I have another application where the database is hosted with a third-party Internet Service Provider. They only gave me one login and that login is associated to a specific user on the database I am hosting there. I cannot create new logins or new users. How do I secure the data in that database if I create a web application that will access the data on that database ? If everyone is going to log into that database using the same login as what I have, they all will have ownership access ? Any advice on this ?
November 22, 2012 at 1:54 am
Scenario 1 - Yes, roles is the best way to manage permissions, as in your case if you wanted to make 1 change to what a user could do you either do it 10 times for all 10 users, or once for the role. So managability is a lot easier when using roles to assign permissions.
Scenario 2 - This is a bit tricky as if you cannot do it at the database layer due to only having 1 login and your ISP wont let you create more logins, then you will need to manage security in your application(s) where you must login to your app as a particular user which then says what you can and cannot do in the app to limit what that end user can see from the DB.
November 22, 2012 at 2:17 am
Thanks for your reply Anthony.
I think I am pretty sorted on Scenario 1, but Scenario 2 I need more help ... my question on Scenario 2, is really what is the best practice w.r.t. giving access for multiple unknown users to a database ? Certainly I would not create a login for each user ? (let's for a moment forget that I cannot get my ISP to create a second login, let's say I had FULL control over that database). Would I then create a second login which has minimal privileges for the unknown users that log in through a web front-end ? Do they all log into the database using one login and password and I will then need to create my own User table and store their password and authenticate them that way ?
November 22, 2012 at 2:25 am
As its a web page you handle what a particular user can and cannot do in your application, be that via a users table which has flags to say, I can do everything realting to Invoices but nothing to do with Purchasing etc, or actually written into your source code depending how you want to handle it.
Now depending how you write your webpage, depends on what you can do. If it is one connection string used all the way through then you have to assign everything that you do in your DB to that one login. If you use different connection strings in different areas of the website then you can get cleaver and assign only what is needed to each login for the particular actions of the website where that connection string it used.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply