| Author |
 Topic  |
|
jaustin817
Starting Member
12 Posts |
 Posted - 10/20/2001 : 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?
|
|
|
smccreadie
Aged Yak Warrior
USA
505 Posts |
Posted - 10/20/2001 : 06:56:29
|
We have created some generic accounts in SQL Server f0r this purpose. These accounts have very limited rights. Basically, all we allow the accounts to do is execute the stored procedures. We do not give them any rights on the tables. If anyone were to hack these accounts, they couldn't do much in the database.
You certainly could create either SQL Server or domain accounts for each person. That's a lot of work for not a lot of gain. We use folks domain accounts within groups to allow access to asp pages instead. Once they have access to the page, we don't care to know which account is pulling info from the SQL Server.
Hope this helps.
|
 |
|
|
dtong004
Constraint Violating Yak Guru
USA
281 Posts |
Posted - 10/22/2001 : 11:14:27
|
Is Application role help you?
Daniel
|
|
|
|
zippy
Yak Posting Veteran
New Zealand
69 Posts |
Posted - 10/24/2001 : 18:44:25
|
I was about to ask the same question...
I have about 400 users on a database I admin.
It's not feasible to create seperate SQL Server logins for each,
so I guess I just have to use a generic login.
At the moment I have a login for selecting stuff which is viewable to anyone.
And I have a seperate login for people who have to update stuff.
Also I just store these passwords in an asp file,
should I encrypt it or anything?
Thanks
Check out the worlds fastest computers at http://www.ocgurus.com |
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 10/24/2001 : 18:59:36
|
quote:
I have about 400 users on a database I admin.
It's not feasible to create seperate SQL Server logins for each,
so I guess I just have to use a generic login.
Are you saying that you have 400 users who each has a NT login that also gets them into SQL Server? I don't do things this way, but can't you just use the Trusted Connection=True attribute in your ConnectionString?
-------------------------------------------------------------------
Just because you CAN do something does not mean that you SHOULD! |
|
|
|
zippy
Yak Posting Veteran
New Zealand
69 Posts |
Posted - 10/24/2001 : 19:05:56
|
no they dont have nt logins,
at the moment i have 2 logins to my database,
Admin - has update insert rights,
and General - has select rights on certain sprocs
what is the trusted connection thingie?
Check out the worlds fastest computers at http://www.ocgurus.com |
|
|
|
robvolk
Most Valuable Yak
USA
15566 Posts |
Posted - 10/24/2001 : 19:09:51
|
Trusted connection allows you to manage security through an NT domain network. Basically, if the user is logged in and validated through an NT domain, SQL Server will accept them as a valid user. You add their NT login in SQL and manage their permissions as usual.
You may not be able to use this through an internet setup, at least not without jumping through some security hoops. It might be easier to authenticate if you're running Windows 2000, using Active Directory.
|
|
|
|
zippy
Yak Posting Veteran
New Zealand
69 Posts |
Posted - 10/24/2001 : 19:13:26
|
maybe this would be a better question to ask,
how do you control access with people using asp on the net?
anyone know of any articles dealing with asp and sql security?
Check out the worlds fastest computers at http://www.ocgurus.com |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 10/24/2001 : 19:16:06
|
Are you talking about SQL Server security ? Or user management / permissions within your ASP app ?
Damian |
|
|
|
zippy
Yak Posting Veteran
New Zealand
69 Posts |
Posted - 10/24/2001 : 19:24:00
|
umm I guess a bit of both,
Why don't I explain what I do at the moment?
When someone enters their login/password,
then I check it agains the database,
if it matches they have a session assigned to them with their userid in it.
this then allows them to access a whole bunch of pages which can be used to update the database.
these pages have the passwords hardcoded into them.
So basically my ASP pages control access to my SQL Server passwords
I guess what I want to know is, is that an ok way to do it?
or is there some big thing that I am missing here 
Check out the worlds fastest computers at http://www.ocgurus.com |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 10/24/2001 : 19:30:17
|
eeeeeewwwwwww
I might be alone here, but the way I do this sort of thing, is run the database connection as a specific single user. Then handle all my "web" users by creating a Users table and managing them myself.
The other advantage of that is ADO can pool connections so your data access is faster.
Damian |
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 10/24/2001 : 19:30:21
|
The way we run most of our sites here is similar. We have one login that all ASP pages use to get into the SQL Server. That Login only has SELECT permissions on tables and EXECUTE permissions on sprocs. Currently the connection string (with password) is buried in a commonly included ASP page (with .asp extension!). All Updates/Inserts/Deletes are only done through stored procedures, so even if somebody were to hack this common SQL login they wouldn't be able to change our data haphazardly.
Granted, we don't store any highly confidential information, but this seems to work pretty well.
-------------------------------------------------------------------
Just because you CAN do something does not mean that you SHOULD! |
|
|
|
zippy
Yak Posting Veteran
New Zealand
69 Posts |
Posted - 10/24/2001 : 19:37:18
|
AjarnMark
well that sounds pretty much like what I am doing,
this data isnt that sensetive either,
thanks for your help everyone
Check out the worlds fastest computers at http://www.ocgurus.com |
|
|
|
sumwanlah
Starting Member
Singapore
43 Posts |
Posted - 02/06/2002 : 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
USA
504 Posts |
Posted - 02/06/2002 : 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
United Kingdom
841 Posts |
Posted - 06/01/2005 : 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!
Australia
4970 Posts |
Posted - 06/01/2005 : 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. |
|
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 06/01/2005 : 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!
Australia
4970 Posts |
Posted - 06/01/2005 : 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. |
|
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 06/01/2005 : 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
USA
3246 Posts |
Posted - 06/01/2005 : 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 |
|
|
|
Topic |
|
Best SQL Security Method
