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 |
|
ramdas
Posting Yak Master
181 Posts |
 Posted - 2002-09-25 : 16:32:16
|
| Hi Folks,We have an application which uses SQL Server 2000 database. As part of the application we want to implement security in such a way that the users will have access to certain modules based on security settings in the database. For this we are planning to to create tables which would maintain permissions for different users. Is this is a good approach to take.ByeRamdasRamdas NarayananSQL Server DBA |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-25 : 16:43:50
|
| Without more details, it's hard to say, but yes, I've done it myself and seen others do it. Depending on the structure of the database(s) and how the application is structured, it might be redundant though. In other words, setting up separate logins for SQL Server might satisfy your needs and be more secure and easier to manage.If you can provide more details it would help. |
 |
|
|
ramdas
Posting Yak Master
181 Posts |
Posted - 2002-09-25 : 16:59:52
|
| Hi,In the application there certain modules which certain types of users can use and others cannot. So depending on the user logged in he application I want to control the access for modules for a user based on a table maintained in the Database. For example:Module User AccessFinance abc Read only.Maintainance admin Read & WriteFinance admin Read & WriteReports abc Read only.ByeRamdasRamdas NarayananSQL Server DBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-25 : 17:14:18
|
| You can still store the user information in a table and query it that way, but it might be easier to use database roles for something like this.You can set up a role for each module and add users to their respective roles. That make maintenance easier because you can modify permissions for the entire role, not just individual users. It also helps keep the tables secure from external access outside of your application (Query Analyzer, linked servers, MS Access linked tables, etc.)Your application can then run sp_helprole, sp_helprolemember, and sp_helpuser to get information on which role a user belongs to, and then disable those modules that don't apply. This can be set up as a very generic function that you can use in just about any application, which will make each module consistent as far as security is concerned, and make it easier to add new modules. |
|
|
|
ramdas
Posting Yak Master
181 Posts |
Posted - 2002-09-25 : 17:38:49
|
| Hi,Thank you for sticking it out with me. I really appreciate your input.I like this forum a lot.ByeramdasRamdas NarayananSQL Server DBA |
|
|
|
|
|
|
|
|
Security Using Database