SQL Server 2000 Web Logins 1

[nidgep]

Hi

Can anyone tell me the best way to manage SQL Server logins from a web page?

I need to manage several logins, each will have their own level of permissions in order to manage a website using ASP
and SQL Server.

Thanks in advance to anyone who may have some advice or links to resources etc.

 

[cheng8950]

i have never done this before, what you can do is you can have your ASP page execute a sql server stored procedure that grant user rights to perform certain actions to particular objects within that database. check for GRANT in the book online. e.g:
USE DBNAME
GRANT SELECT ON TABLENAME TO [USERNAME]

Hope this helps.

-cheng

 

[nidgep]

Hi Cheng8950

Sorry, I may have mislead you.

what I intended to say was - what is the best strategy to use to manage permissions in SQL server.

I am trying to develop a small scale CMS system where the whole site content is published and managed through ASP and uses SQL Server 2000.
The site administration is managed through ASP using a different SQL server login to that which will be used when the content is 'LIVE' and published.

I was really looking for tips on how best to implement the logins and maintain a good level of security, so as not to compromise the content of the site.

The creation of SQL logins and/or grant permissions was not the issue as the users/admin will be connecting remotely and will not be connecting on the same domain so that SQL Server could use windows authentication and apply permissions through script/stored procedures.

Thanks for your help so far.

 

[Corran007]

The best way i have found for this is to build your own security system. Have a main connection that can do everthing to the database. Then when a user logs in, authinicate them and assign them the permissions you want based on thier login. Then when a user tries to do something, check thier permsions. Id recommend page based security, but you can have multiple permssion checks on a page, it just slows it down a litte bit. The downside is it creates more work on development by have to do the permision checks and build the security system. The upside howver is usually worth it as you have absolute control over it all. When you need absolute control over the system, it is well worth it.

 

[nidgep]

Hi CORRAN007

I agree with you about building my own security 'system'.

Off the top of my head, the following approach is the one I think that I will take....see what you think...

Create two logins, one SQL Server and one using the IUSR_Servername. The IUSR_Servername windows account will be used to connect to the database and serve up the pages.

The second login will be a SQL Server login which will have all the permissions it needs to administer the site remotely. As each user logs in, use a lookup to gather up their permissions and present a template with the functionality to match the allowed activites.
For example, if the admin user only has base rights then the template will only allow the creation of new content and the updating of existing content. If the login has the rights to set the content 'LIVE' or add new users/folders etc then their template will be much 'richer' so to speak.

Both of the logins will be assigned to roles in the database and access will always be through stored procedures and views etc. No-one is to have direct table/data access.

In addition to this and each time a request is made, their permissions can be double-checked through code using a matrix which equates to their functionality.

How does this sound to you? OK? Feasible? Too much - too cumbersome?

Your feed back is appreciated.