September 20, 2006 at 1:38 pm
I am in the process of converting a local database into web app with many more users. The database currently has a SQL login for each user and each user is a member of a specific role which is applied to each Stored Proceedure & View. (The Roles determine who can run stored proceedures)
We will be changing users to a single login and then access a user table for login credintials and roles. My question is...is it possible to assign a role to a user, but it only affect the current user when you use a shared login???
If not what would be the alternative?
Thanks.
fryere
September 21, 2006 at 2:34 am
"The database currently has a SQL login for each user and each user is a member of a specific role which is applied to each Stored Proceedure & View. (The Roles determine who can run stored proceedures)"
Okay.
"We will be changing users to a single login and then access a user table for login credintials and roles. "
Why?
"My question is...is it possible to assign a role to a user, but it only affect the current user when you use a shared login???"
You have one login - there is no such thing as a 'current' user.
"If not what would be the alternative?"
Without knowing the problem (why and what you are trying to achieve), it's hard to offer a solution.
Overall there are two models - individual user access or an application role. As long as you don't care about user level access and auditing go for an application role.
The only other way is to have the application handle security - not SQL server.
September 21, 2006 at 6:32 am
Let me repost my question again.
From my understanding, databases with a high number of users use a single login to gain access to the database then reference a user table instead of using a Sql login for EACH user. What is the common strategy for issuing database roles? Is it possible to assign a user a specific role when they have the same login as everyone else?
In my existing database, a user can have one of four roles. All of my stored procedures have permissions associated so only users that need to run it can do so. The front end app also checks for these roles to determine what the user sees on the screen. Therefore, I would like to keep the roles and permissions linked together instead of opening everything up to a guest or public account and managing the roles/security on the application side.
Therefore, when a user logs in I would like to be able to temporarily assign them a role that only lasts for their session and would only be tied to them instead of everyone that logs in under the same account. I am not an experienced SQL developer, so if I am totally missing the mark here please be specific as possible or simply point me in a direction to research.
Thank you very much.
fryere
September 22, 2006 at 3:14 am
Thanks for the clarification - basically, no.
Using a single user account is fine, but the application needs to manage permissions, not SQL server. MS profer using a single account only if your users can all have the same access.
You can't have it both ways - single account, single permission set.
Lots of apps do have their own security set up off the shelf, implementing it well in an application of your own design is a bit of a challenge.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply