Additonal Data for SQL Server Login/User
-
Hi, How can I add additional data to SQL Server 2k login. I have a database with logins/users for each person that can login I need to add extra information and create a relation (based user login or index) to other tables in my database. Sample: "document" has an author, author is a user that can login to SQL server. How can I add relationship from "document" table to SQL server login? Thanks for any suggestions how to do that. If this is not a good idea to create a relationship between SQL server login and other tables, is there other solution for similar problem? regards dobrzan
-
Hi, How can I add additional data to SQL Server 2k login. I have a database with logins/users for each person that can login I need to add extra information and create a relation (based user login or index) to other tables in my database. Sample: "document" has an author, author is a user that can login to SQL server. How can I add relationship from "document" table to SQL server login? Thanks for any suggestions how to do that. If this is not a good idea to create a relationship between SQL server login and other tables, is there other solution for similar problem? regards dobrzan
dobrzan wrote:
How can I add relationship from "document" table to SQL server login?
What sort of relationship? You can, of couse, use commands like:
GRANT SELECT ON Document TO AuthorUserName
See also: GRANT[^] DENY[^] and REVOKE[^] If this is not what you want you will have to explain what you want to do with this "relationship"
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
dobrzan wrote:
How can I add relationship from "document" table to SQL server login?
What sort of relationship? You can, of couse, use commands like:
GRANT SELECT ON Document TO AuthorUserName
See also: GRANT[^] DENY[^] and REVOKE[^] If this is not what you want you will have to explain what you want to do with this "relationship"
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
What I need is to extend the basic login/account. There is a number of accounts/logins that have access to database. Each user (which is a login/account) can do some things depending on other data collected in database. Example: There is a "customer" table in database. Each "customer" can order "product". There is relationship between customer and product - lets say it's done by "order" table. Now when customer connect to database (using his SQL Server login) I want to have a view that shows what "product" "customer" ordered. So there must be a some kind of way to link SQL Server login with entries from "customer" table. It's quiet simple thing, when I log to codeproject I can track my posts. I can do that without SQL Server, but I want to create secure app - based on SQL Server Login authentication.
-
What I need is to extend the basic login/account. There is a number of accounts/logins that have access to database. Each user (which is a login/account) can do some things depending on other data collected in database. Example: There is a "customer" table in database. Each "customer" can order "product". There is relationship between customer and product - lets say it's done by "order" table. Now when customer connect to database (using his SQL Server login) I want to have a view that shows what "product" "customer" ordered. So there must be a some kind of way to link SQL Server login with entries from "customer" table. It's quiet simple thing, when I log to codeproject I can track my posts. I can do that without SQL Server, but I want to create secure app - based on SQL Server Login authentication.
Since all this will be done through an application* you let the business logic in the application (or in stored procedures) filter the data returned to the user of the application.
dobrzan wrote:
but I want to create secure app - based on SQL Server Login authentication.
Current advice is not to use SQL Server Authentication unless you are connecting it to systems that are not windows based. You should be using trusted connections with Windows Authentication. What you are suggesting is less likely to secure your system because your users will have some direct access to your SQL Server (even if they don't know it). This access can be used by an attacker to compromise your system. All an attacker needs is an account to your application and you automatically give them an account to the datbase. If you have a web application, it will probably try to connect to SQL Server using the ASPNET account if you let it use a trusted connection. Let it do that. Create your own user tables to handle the users of your application. Do not use the sysusers table in SQL Server, it is not designed for that purpose. you are not going to give your customers direct logins to your SQL Server as that would be nuts - Imagine if Code Project added a login in its SQL Server for each of its 3million+ members - it also screws with connection pooling making it very inefficient
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Since all this will be done through an application* you let the business logic in the application (or in stored procedures) filter the data returned to the user of the application.
dobrzan wrote:
but I want to create secure app - based on SQL Server Login authentication.
Current advice is not to use SQL Server Authentication unless you are connecting it to systems that are not windows based. You should be using trusted connections with Windows Authentication. What you are suggesting is less likely to secure your system because your users will have some direct access to your SQL Server (even if they don't know it). This access can be used by an attacker to compromise your system. All an attacker needs is an account to your application and you automatically give them an account to the datbase. If you have a web application, it will probably try to connect to SQL Server using the ASPNET account if you let it use a trusted connection. Let it do that. Create your own user tables to handle the users of your application. Do not use the sysusers table in SQL Server, it is not designed for that purpose. you are not going to give your customers direct logins to your SQL Server as that would be nuts - Imagine if Code Project added a login in its SQL Server for each of its 3million+ members - it also screws with connection pooling making it very inefficient
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
So you suggest that I should use one login or windows based auth. to give users access to database (SQL Server) and distinguish each user by my own login logic in my app or web page. Am I right? Is this what others do? Anyway thanks for help.
dobrzan wrote:
So you suggest that I should use one login or windows based auth. to give users access to database (SQL Server) and distinguish each user by my own login logic in my app or web page.
Yes.
dobrzan wrote:
Is this what others do?
Of course.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog