Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Utpal
Posting Yak Master
179 Posts |
 Posted - 2003-05-16 : 03:12:23
|
| Hi everybody,I have developed an inventory application in VB 6.0 - SQL Server 7.0. For user security, I am planning to give users access to the database only through the front-end application. I have created an user master in the application where users can be created and the permissions for the entry & report modules of the application that they could access could be set. I am storing the user and permissions information in tables in the database. The front-end application will always connect to SQL Server using a fixed user login and password to which only I will have access. Am I thing right? Are there any drawbacks of this approach? |
|
|
pmr
Starting Member
37 Posts |
Posted - 2003-05-16 : 04:52:01
|
| There are benefits and drawbacks as in most situations.One drawback that I have run into with a system like this is that you cannot determine which users have which locks in the system. This can help you troubleshoot conflicting locks in the future.Another drawback is that it hinders a the ability to filter by user in the profiler again making it difficult to troubleshoot down the road.One benefit might be that you could build a system that can be more easily be ported to another system since the user right are not intermingled with the database engine.An apparent benefit is that you more easily modify user access and add and subtract users.There are two other options:One is to use Windows Authentication which can be convenient if all of the future database users are already users in your Windows Network Environment.The second is to use SQL Authentication. This may seem very tedious if you are only familiar with the Enterprise Manager method of user management, but there is another way. You can keep a list of users and roles and their respective rights in a table as you were already planning to do. Then you can simply create triggers on those tables that run the scripts to create and modify and delete real rights directly on the Database Engine. You may even be able to do away with these tables all together depending on whether you can find satisfactory functions to perform the audits of user rights you are going to want down the road. To begin researching whether this method will be best for you, look up GRANT and REVOKE in the Books Online help system on the index tab.Looks like you have an exciting project ahead of you.Well, these are the basic concepts. Check out the following links for more thourough information:[url]http://www.sqlteam.com/SearchResults.asp?SearchTerms=user+security&SUBMITs1=Search[/url][url]http://www.sqlteam.com/item.asp?ItemID=753[/url]Peter |
 |
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2003-05-16 : 05:14:37
|
Thanks a lot!  |
|
|
|
|
|
|
Regarding user security