| Author |
Topic |
|
jaustin817
Starting Member
12 Posts |
 Posted - 2001-10-20 : 03:36:03
|
| I am running sql 2000 server and I am allowing several users to access the db via asp pages. I am wondering what is the best method of providing a secure login for each user. I do not want to have the users to logon to each page. Any ideas? |
|
|
sumwanlah
Starting Member
43 Posts |
Posted - 2002-02-06 : 05:27:25
|
ok... maybe i'm lazy, or i'm just DARN lazy...    Then again maybe i'm trying to rack up more submission points!!! Hehehehe!!! How many years would it take me to reach "Yak-ker" status?!?!?! Hmmm.... *wonder ponder*I know I deserve a whump up the behind  , but can someone please gimme an example of how I can implement stored procedures to CONTROL inserts/updates/deletes?Am i right to say that the other added feature in using stored procedures is in using the stored procedure-level permission settings? |
 |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-02-06 : 09:13:59
|
In general if you don't want to create login for every user in the database then assign them permissions have a user schema that is something like this to allow maximum flexibility (at least I think it would)TABLE Users - UserID - LoginName - Password TABLE Permissions - PermissionID - Name - Property* TABLE UserGroups - UserGroupID - Name - Property* TABLE UserGroupMembers - UserGroupMemberID - UserID - UserGroupID TABLE UserGroupPermissions - UserGroupPermissionID - UserID - PermissionID TABLE UserPermissions - UserPermissionID - UserID - PermissionID The head of our internet security team here suggested I try this type of approach and I must admit it makes plenty of sense if you have to store user names in the database... the Property* columns I devised as a way to make multi-selectable columns with some easy coding... a few articles back I asked for a CHECK constraint that made sure the value in a column was a single bit set for each value... 2^(x) basically... make the unique key for Name, Property and you have a nice coding tool... say you wanted to get a set of permissions... just OR the values together then use a where to (Property AND @desiredProperty) = Property and boom, you have only the properties with those bits set... it's easier than saying ... Name IN ('abc','xyz',etc...) or worse, hardcoding the permission ID's into your code :-o ... I just thought I'd put that in there because it saved me soooo much time coding the permissions middle tier object... As for storing the userid in a session.. I guess this is ok... you could store it as a cookie and offload the session information to the client ... however i would then also add a UserLogins table that looks something like this:TABLE UserLogins - UserLoginID - UserID - DataOfLogin - KeyValue (unique identitfier or some unique value in the table) Then everytime the user logs in make sure you update this table, if its there first login then get the KeyValue value and store it with the UserID and then for each interaction with a sp make sure it checks the userid with the KeyValue that was stored with it to make sure it is a valid client request, this is just a way to minimize potential cookie hacks... I never did this because I didn't need to (on a intranet) but for parts of our extranet this is the method security mandated we use... (not so much madated, but approved of) phew... what a rant... any opinions on this method of database users would be very welcomed, as if they seem logically sound and valid I will pass on to our security guys ;-) ... Onamuji |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-06-01 : 07:05:38
|
| I see one problem with this approach of having one connection string for everyone instead of using a login for each user:How would you implement audit tables using triggers? You would have no idea which user is making what change! Unless I am missing something... |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-06-01 : 07:52:01
|
Wow, a 5 year old thread....and a dtong one at that! What inspired you to drag this up ?  Using this approach you wouldn't implement a standard audit with triggers. You'd build it into the app. Depends on your approach and environment.DamianIta erat quando hic adveni. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-06-01 : 08:51:49
|
| Well, I'm trying to work out which way to go.Do you implement the "one connection string" approach? What other things do you lose besides audit tables from triggers if you go down this route? (It would seem to me that audit table implementation in the app would be much more involved than just using triggers.)Or do you map an account to every internal user and have one account for webusers? How much of a nightmare is it to have to administer all those logins?Any ideas? links?/** sql sui generis **/ |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-06-01 : 09:02:10
|
| Personally, as I said here 4 years ago... I work on web apps mainly, I usually implement my own user management and authentication, and auditing depending on the application requirements.By using one connection string I gain connection pooling, which makes a huge difference when the application is very high volume (which some of mine are).DamianIta erat quando hic adveni. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-06-01 : 10:07:51
|
| Yes we also already have implemented our own user management and authentication.But I use to find it very usefull to map those to actual sql logins (by calling sp_addLogin from my own mp_CreateUser proc).Because if there was any problem with performance or locks or anything else I could go and look at that and instantly find the guilty party, find out what they were doing at that exact moment and solve the problem. When everyone is logged on as the same login it becomes harder to debug who is causing a problem (ofcourse in the ideal world the problem would not be happening in the first place). |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-06-01 : 14:42:49
|
| What about the idea of using a common SQL Logon, but custom Workstation ID in the connection string to identify the user inside SQL Server?---------------------------EmeraldCityDomains.com |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-06-02 : 03:41:42
|
| Yeah that's all we can do at the moment. But "John Smith" is a whole lot easier to read and act on than F4J5T6KP. Is that what you are talking about? The windows network identification id? The word "custom" threw me a bit. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-06-03 : 19:33:37
|
| When you build your connection string, one of the parameters you can include is "workstation id". You can set it to anything you want, so you could dynamically assign in the user's actual login name from NT. And then in SQL Server, you can use the HOST_NAME() function to retrieve it. Unfortunately, if somebody connects to your database directly or from some other application, then the workstation id will just reflect its default settings, which I think is the PC name.---------------------------EmeraldCityDomains.com |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-06-06 : 04:17:18
|
| That's brilliant! I thought that would make SQL Server a bit upset. But I guess not. It's a bit of a hack but it will work for us! Thanks. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-12-12 : 05:32:27
|
quote:
Originally posted by AjarnMark
What about the idea of using a common SQL Logon, but custom Workstation ID in the connection string to identify the user inside SQL Server?---------------------------EmeraldCityDomains.com
I would just like to say thanks to AjarnMark again for this suggestion. It's brilliant in it's simplicity I thought. |
|
|
|
my_aro
Yak Posting Veteran
50 Posts |
Posted - 2005-12-12 : 11:16:34
|
i would just like to suggest for you to have accessed roles on each pages for each users so that you can have your control quote:
can you breathe using you're nose and mouth at the same time?
|
|
|
|
|
Best SQL Security Method