| Author |
Topic |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2007-04-14 : 03:01:03
|
| I have windows application (Basically a accounting system). I want to use either SQL authenticaion, or Windows authentication for my users of the application (Basically each db user will have their own account in Users for that db, so I can have sql control the access).All access to the db is via stored procedures, but I want to have a set schema for each user type:SupervisorManagerData Entryetc.All with a predefined access to the proper stored procedures(So far fairly simple).In addition to this I want to be able to set the specifics of that user in my .net application via a interface (so a user by default will have the pre-defined schema, when a supervisor opens this interface, they can add/remove access to other stored procedures)Here's the issue. Is this the proper methodology to achieve this, because it's quite of bit of programming work. I don't want to do it then need to re-due everything.Also should I use Windows Authentication, or SQL, or have a completly seperate table in the DB where I do manual authentication then have the .net application restrict the access?Any help or comments on this approach will be helpful. Also ifanyone has found any articles or information similiar to what I am trying to accomplish, please point me in the correct direction.Thanks. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-14 : 09:04:43
|
quote:
All access to the db is via stored procedures, but I want to have a set schema for each user type:SupervisorManagerData Entryetc.
Do you really need this? Are these users logging in from Query Analyzer or SSMS and executing stored procedures directly themselves? I doubt it... probably they use an application which calls the procs, right? I would just handle security in your application, since in the app's UI you'll need to do it there *anyway*, since you'll need to present each type of user with the options regarding what they can do. Why also restrict the stored procedures as well?Typically, your app uses one login to access the database, and the app itself controls what different types of users can do. Now, that login into the database might indeed be restricted only to stored procs so that developers can't randomly execute any SQL that they want, but usually that's all you need to do in terms of database security .. unless you expect to have many users directly writing code against the database themselves. Again, I doubt that your data entry people will be writing .NET code or calling stored procedures directly themselves in Query Analyzer, right?- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-04-14 : 14:33:42
|
| You are correct in that the application calls the procedures. I have used a lot of enterprise level accounting packages, and they all use sql authentication (BEST MAS 500, and Microsoft Great Plains). That is why I opted for that option. Does everyone agree that it should be handled at the application level and not sql? If I did the access conrol in sql, I wouldn't need access control in the app. Anything that was restricte would return the error stating that, so I would just catch the error and re-word it.Please keep in mind this a Windows app, not a web app, so I probably have a little more flexibility with access control in sql. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-04-14 : 19:39:46
|
| If you only have application security, then the user can simply fire up excel (or word) and hack away...DavidMProduction is just another testing cycle |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-04-14 : 21:07:55
|
| I think I'm in agreement with byrmol, that I should use SQL security, but what jsmith8858 is saying is that I should use a master login/pw that no user knows for my connection. This would prevent someone from hacking away in excel, and also prevent the users to gain access to the db w/o going through the application.I think I would like to restrict access via SQL for the main reason of the security is handled by sql, and not a .net app. It seems like it would be the more secure better alternative, just longer to code properly. please let me know you're thoughts if they are different. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-15 : 09:52:47
|
| David -- of course you still have your database secure, I just meant that you only have 1 user account that the application uses, and the app itself through its UI handles security for the users.Vinnie -- the problem with that approach is that your app will be presenting the exact same options to all users -- admin options, manager only options, etc -- even though for certain users those options will not be allowed and they will just get errors saying "you can't do that" over and over, and they'll need to guess as to what they are allowed to do. That's not really a great user experience. It would be like everyone who goes to google.com to do a search also seeing all kinds of admin buttons ("change google logo", "update index", "delete site from database", etc) on the screens -- that wouldn't make much sense, would it, even if the pressing each button returns a "you can't do that!" error. That is why I suggest just handling roles in your application code and only present each user type with what they can do, which makes much more sense IMHO.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-15 : 20:48:17
|
| It is better to only use one login to connect to SQL Server and to have an administrative interface within the application to control access to the application functions.If you give every user account access to the database, it will be much more insecure.The fact that different accounting packages use the other approach is just an illustration of the poor database security design that you get with most applications. The next time I see a software package with adequate, well-designed database security will be the first.CODO ERGO SUM |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-04-15 : 22:43:13
|
| Here's a little methodology I use.. For a win forms app with web interaction.There are 5 Security Roles for the winform users.There is 1 security role for internet users.Create 5 Database Roles controlling permissions for the winforms users.Create 1 Database Role controlling permissions for the internet users.Create 1 Database Role controlling permissions for the Application.Create a Login (if a SQL Account then I give it blank password!) for the application that maps to the Application Role. Has permission to execute 1 stored procedure. If the client is not using integrated security, then 5 SQL logins are created that map to the appropriate role.Create a database table called SecurityRole which maps the 5 winform roles with a connection string. When the application starts, it uses the Application connection to validate the user and return the connection string to use for the rest of the users session.For the UI....Create User Table Create UserRole Table Create Form TableCreate FormSecurityRole Table After a successful login, the app requests the list of forms that the user has permission for then builds the menu system based on the results.HTHDavidMProduction is just another testing cycle |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-04-16 : 02:09:43
|
| Michael Valentine Jones, I will probbly go with the sugestion of using 1 login. How should I do this though? Should I setup roles in sql for each user group, or should I just have 1 role that has access to all Procedures, then restrict the access 100% at the application level based off SQL SECURITY ACCESS TABLES I will create?Can anyone please answer this.Thanks |
|
|
|
|
Controlling SQL access in .net - Revised