SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Best SQL Security Method
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

jaustin817
Starting Member

12 Posts

Posted - 10/20/2001 :  03:36:03  Show Profile  Reply with Quote
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  Show Profile  Visit smccreadie's Homepage  Reply with Quote
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.

Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 10/22/2001 :  11:14:27  Show Profile  Visit dtong004's Homepage  Reply with Quote
Is Application role help you?

Daniel

Go to Top of Page

zippy
Yak Posting Veteran

New Zealand
69 Posts

Posted - 10/24/2001 :  18:44:25  Show Profile  Visit zippy's Homepage  Send zippy an ICQ Message  Reply with Quote
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
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/24/2001 :  18:59:36  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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!
Go to Top of Page

zippy
Yak Posting Veteran

New Zealand
69 Posts

Posted - 10/24/2001 :  19:05:56  Show Profile  Visit zippy's Homepage  Send zippy an ICQ Message  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 10/24/2001 :  19:09:51  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

zippy
Yak Posting Veteran

New Zealand
69 Posts

Posted - 10/24/2001 :  19:13:26  Show Profile  Visit zippy's Homepage  Send zippy an ICQ Message  Reply with Quote
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
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/24/2001 :  19:16:06  Show Profile  Visit Merkin's Homepage  Reply with Quote
Are you talking about SQL Server security ? Or user management / permissions within your ASP app ?


Damian
Go to Top of Page

zippy
Yak Posting Veteran

New Zealand
69 Posts

Posted - 10/24/2001 :  19:24:00  Show Profile  Visit zippy's Homepage  Send zippy an ICQ Message  Reply with Quote
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
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/24/2001 :  19:30:17  Show Profile  Visit Merkin's Homepage  Reply with Quote
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
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/24/2001 :  19:30:21  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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!
Go to Top of Page

zippy
Yak Posting Veteran

New Zealand
69 Posts

Posted - 10/24/2001 :  19:37:18  Show Profile  Visit zippy's Homepage  Send zippy an ICQ Message  Reply with Quote
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
Go to Top of Page

sumwanlah
Starting Member

Singapore
43 Posts

Posted - 02/06/2002 :  05:27:25  Show Profile  Reply with Quote
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

USA
504 Posts

Posted - 02/06/2002 :  09:13:59  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
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

United Kingdom
841 Posts

Posted - 06/01/2005 :  07:05:38  Show Profile  Reply with Quote
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!

Australia
4970 Posts

Posted - 06/01/2005 :  07:52:01  Show Profile  Visit Merkin's Homepage  Reply with Quote
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

United Kingdom
841 Posts

Posted - 06/01/2005 :  08:51:49  Show Profile  Reply with Quote

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!

Australia
4970 Posts

Posted - 06/01/2005 :  09:02:10  Show Profile  Visit Merkin's Homepage  Reply with Quote
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

United Kingdom
841 Posts

Posted - 06/01/2005 :  10:07:51  Show Profile  Reply with Quote
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

USA
3246 Posts

Posted - 06/01/2005 :  14:42:49  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000