March 6, 2011 at 1:40 am
i want to make web site where user will login using sql authentication and then they can access the databases created by them.... so i want query to get all the databases name created by a particular user..... also the user can give privileges to other user so he can either read, modify or delete the table columns.... so plz help me with this... should i use sql login authentication or database user or both????? plz give me the query???
March 6, 2011 at 2:23 am
First, you normally don't want to give your web application authority so high that it can create the databases. This would be very bad and has huge security implications which can lead to the database server being hacked. however, it appears that you want to make a hosting site so you will need to use a high authority SQL login to create databases, etc.
There is a key separation in security for what you are trying to accomplish.
1. The un/pw that your users use to connect to your web application. This is the un/pw collected by your app. They are usually not the credentials used to access the SQL server itself, but are used to select from a [User] kind of table of sorts. The data in the tables are used by your app to determine the users authentication and authorization in the app.
2. The SQL login used by your web app to access the SQL database. This is a SQL login (not stored in a [User] table) that an application will use to access the tables in a database. The application could be SSMS (SQL Server Management Studio), or in this case, your web app.
NOTE: this is way more complex than this and I am simplifying the concepts and avoiding discussions on impersonation, ldap, etc., which will still have the same security issues based on the questioners desired implementation.
So all of your user's activity piggybacks on a single SQL login between your web server and the SQL server (again, excluding impersonation for topic simplicity).
There are many ways to implement what you need, all with various goods and bads. One easier way, is to create one SQL login with access to each database desired (usually just a single database in many cases). Then create a , role and user_role, role_permission, etc. tables to define the access for that user within the application.
This is a more common, simplier, and more secure design to do what it sounds like you are asking for.
It sounds like you are wanting to create a hosting sort of site like discountasp.net, etc. You can easily control the database and logins per user like this, but you'll still need your own database to hold metadata for the users and which db they own.
Research commands like CREATE DATABASE and CREATE LOGIN, etc.
You may want to check out a website like http://www.codeproject.com for the development and application design side of things. But hopefully, this gave you a clearer understanding (yet simplified) of how authentication and authorization in SQL Server usually works as related to a web app, and gives you a few new directions to research to implement your solution.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 6, 2011 at 3:15 am
[Jim].[dba].[Murphy] (3/6/2011)
First, you normally don't want to give your web application authority so high that it can create the databases. This would be very bad and has huge security implications which can lead to the database server being hacked. however, it appears that you want to make a hosting site so you will need to use a high authority SQL login to create databases, etc.There is a key separation in security for what you are trying to accomplish.
1. The un/pw that your users use to connect to your web application. This is the un/pw collected by your app. They are usually not the credentials used to access the SQL server itself, but are used to select from a [User] kind of table of sorts. The data in the tables are used by your app to determine the users authentication and authorization in the app.
2. The SQL login used by your web app to access the SQL database. This is a SQL login (not stored in a [User] table) that an application will use to access the tables in a database. The application could be SSMS (SQL Server Management Studio), or in this case, your web app.
NOTE: this is way more complex than this and I am simplifying the concepts and avoiding discussions on impersonation, ldap, etc., which will still have the same security issues based on the questioners desired implementation.
So all of your user's activity piggybacks on a single SQL login between your web server and the SQL server (again, excluding impersonation for topic simplicity).
There are many ways to implement what you need, all with various goods and bads. One easier way, is to create one SQL login with access to each database desired (usually just a single database in many cases). Then create a , role and user_role, role_permission, etc. tables to define the access for that user within the application.
This is a more common, simplier, and more secure design to do what it sounds like you are asking for.
It sounds like you are wanting to create a hosting sort of site like discountasp.net, etc. You can easily control the database and logins per user like this, but you'll still need your own database to hold metadata for the users and which db they own.
Research commands like CREATE DATABASE and CREATE LOGIN, etc.
You may want to check out a website like http://www.codeproject.com for the development and application design side of things. But hopefully, this gave you a clearer understanding (yet simplified) of how authentication and authorization in SQL Server usually works as related to a web app, and gives you a few new directions to research to implement your solution.
Jim
first of all thanks for your quick reply....
actually i am making "web based database design tool" for my final year project...
it will provide GUI based database management for users without any knowledge of SQL queries... so its like phpmyadmin...
now i want to add feature of privileges where one user can give read,insert,update,delete etc. privileges on his database tables to other users... so other users have only limited access...
so thats why i wanted to make separate login for each user and then use 'grant' query's to do the privileges thing...
but i wanna know how to get all the databases created by a particular login??? and only sql login is required or database user or both???
i hope you understood my problem... waiting for your replies... bye... take care...
March 6, 2011 at 8:23 am
Ok, thanks for the clarification.
Google sp_grantlogin. This will help you begin the research process to do what you are doing. The sql login that executes sp_grantlogin and the many other system stored procedures that you will need, needs to be executed under a higher authority SQL login. the info is out there and all over the web already.
The above stored proc will help clarify the SQL pieces, but I think you really suffer from an unclear software design, which is beyond the scope of these forums. Try http://www.codeplex.com or similar.
Also, please do not quote the entire previous post. Only quote a small piece if you are addressing a specific part of the post. Or quote none at all if there are not a lot of posts to confuse.
I hope you enjoy your design!
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply