Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Best SQL Security Method

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?

Go to Top of Page

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
Go to Top of Page

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...
Go to Top of Page

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.



Damian
Ita erat quando hic adveni.
Go to Top of Page

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 **/
Go to Top of Page

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).


Damian
Ita erat quando hic adveni.
Go to Top of Page

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).
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -