There are a couple of points that I need to clarify before buying.



1 - Role-Based Security will not work with SQL Server 2000?

2 - It will work with SQL Server Authentication (user and password defined within SQL Server)?

3 - If so, does it invoke stored procs to create SQL Server users within the database and grant each user the appropriate permissions at the database level?

4 - or if it handles this some other way, how does it work?



Thanks

Daniel,



I'm just a user, but as part of my project I had to really get to know security, so I can offer some insight before you get an official response.





Yes it does. I'm using it now. However, as I understand it, when auditing happens, auditing will only be with SQL Server 2005. The official line may be that they don't support 2000, but I know it works (and works well)





Not by design. You could probably make it work, but I doubt it would be worth the effort.





It doesn't, but you're on the right track if you wanted to make it work. For the permissions to work, the user would need to be defined both within the SF security tables (so permissions would work) and on SQL Server (to handle authentication). This means you'd have to build your own user setup form (to replace the security dialog...which means also handling creating roles and assigning roles permissions to user, setting global security settings...all the things users would need to do on the form where users are managed), and when a user is added, add it to both the SF table and run a sproc (or script) to create the user on SQL. You'd also have to have a custom login form to authenticate against SQL. This would be a huge pain and if you ever wanted to move to Enterprise Server, using SQL users wouldn't work (or would be so hard as to not be feasible). If it is or could ever be a web application you'd also loose connection pooling. The application I'm converting to SF now used SQL logins, but I've switched to normal SF security (with some customization) and it is MUCH nicer. Only one SQL login to manage, app does everything else. The permission/role system rocks!



Hope this help!

Thanks for the input. My situation is that there is a big domain against which some of the users authenticate, there is a small domain in which sits the SQL Server, and there are a number of drone workstations that are essentially logged on all the time with a generic logon like nursing/nursing.



Most of the intended users of the SP apps will be sitting at those drone stations but need to be authenticated somhow. Many do not even have a domain account because they are transient. An individual department knows that the person is coming for a month and could add them as an SF user but the central IT that controls the domain may never even be aware that the individual came and went. This is a common situation in hospitals.



I've seen situations where the night nurses, who come from an agency, leave all their charting and ordering for the day shift because they have never been given permission to use the computer. This is not good for patients and having itinerant doctors using the data systems with no record of their identity is not good for HIPAA.



If this security add-on handled all of the plumbing that you described as being necessary, my guess is that it would be attractive in a lot of healthcare settings that might not otherwise be able to use it effectively.

Seems like SF security would work ideally here. The drone station is logged onto the domain via a common login, but the users of your app could still login to the app via SF and then you could know who they are. Because its app level security, only affecting the app, the power to add/deactivate users could be put into the hands of the appropriate party at the hospital/clinic. You might need a special limited version of the security dialog to handle the temp workers (like just provide there user name, password and pick form a limited number of roles). That would pretty to do.





Agreed. This is one of the reasons the security system is so important.





Not sure what you mean. If you are saying that if the SF RBS handled SQL authentication [it would be attractive], I'd have to disagree. SQL authentication for your users is a bad way to go IHMO (having done it). Maintenance is a pain, there can be performance issues, it limits options for using ES or web based apps. I think the RBS does handle almost all the critical needs (except auditing, which is in the pipeline as I understand it) and does so elegantly. There a few nice to haves (read nice to have so I don't have to build it myself ), but it seems to me to be a solid system.

Seems like SF security would work ideally here. The drone station is logged onto the domain via a common login, but the users of your app could still login to the app via SF and then you could know who they are. Because its app level security, only affecting the app, the power to add/deactivate users could be put into the hands of the appropriate party at the hospital/clinic. You might need a special limited version of the security dialog to handle the temp workers (like just provide there user name, password and pick form a limited number of roles). That would pretty to do.



Thanks for the feed back. Maybe I'm confused.



First off, the drone stations are logging onto a domain that I don't control but that the whole world has access to. I'm not even sure that I could create a databse user for that generic account since it's not in my domain. (or getting the old "authentication failed" message from not being authenticated in the server's domain. Used to be the only way around this was to use a SQL Server Login name and password.)



Secondly, in order for RBS to work, don't I have to give permission to the generic account to access my database? What's to prevent someone with an enterprise manager and the generic account/password from connecting to my database and wreaking havoc?



What is it about RBS that insures that only SF apps that have authenticated with the SF mechanism can do things in the database?

There are two levels of access control within any SF application that is using RBS:



1. Control of access to the database(s) via control of the connection strings, including any user names/passwords. You can connect to the database via a domain login or via SQL authentication. This part has nothing to do with RBS and is common to all SF applications. This controls access to the database, but has nothing to do with control to elements within the application (like forms, BO CRUD access or fields in a BO...that is RBS in #2). This is setup in AppMain in the SetDataSources () method. You can either use the ConnectionStringManager to build the connection string if the model for your app is that the users know how to connect to the database or you can manage the connection string manually, using the DataSources.Add() method. Typically you'd use this when the users don't know how to access the database. There is more information about both of these in the help files.



2. Control of access to forms, to data via BOs and to fields within BOs using the RBS. There are several steps to using RBS (all outlined in the help files). First you'd use the Security Dialog to setup permissions. These are simply named entities that can be assigned to users that can be programmed against to determines a user's access to some element of the application or to data via the application. E.g. you might have a PatientRecord permission that controls access to a patientrecord in the application. Once the permission is defined within the RBS system, you can use it to control access. They can be used to control if a form can be opened, if a user can add, update or delete data in a BO or all the way down to the field level in a BO. You can also program against the permissions to control things like menus, toolbars, hiding/showing elements on forms based on permissions. The application users who are responsible for controlling access to their app are then given access to the security dialog. They can then add/edit/remove users, define password and other security policies, create roles (sets of permissions) and assign roles and permissions to users. The final piece that bit a lot of us when starting to use the RBS is that when you develop security using RBS, you typically are storing the data in your StrataFrame database. You then have to deploy that data to the sql server you'll use in test/production. The DDT makes this very easy, but is not required.



So, give these two control mechanisms and your questions, here are a couple of scenarios (there are many more, as this is quite a flexible system).



The first scenario is kind of inline with what I'm guessing you were thinking when you asked about SQL Server security integration (kind of). You could allow access to SQL Server within your app via individual SQL Server user names and passwords and/or domain user account quite easily using the ConnectionStringManager. The users would have to know where the SQL Server is, and have a login on the SQL Server. You wouldn't use RBS at all (more on this in a bit), rather it just controls who is getting to SQL server, with no control else where in the app at all (unless you programed it yourself...more on that too).



There are sooooooooooo many issues with this approach, if you want to secure both your app and your data. First, the ConnectionStringManager caches the connection info in an encrypted file in Documents and Settings. It only prompts the user for the connection information if it doesn't find it in this file. Which means that on your shared workstations, you have to do some coding to make sure every user has to enter the connection info, like deleting this file right after it is created or something. Second, this is not actually a login at all, thus you don't have a user within the app, you have a connection string to connect to the database. You could pull the user name out, but then you come to the next problem: no application level security. While you've controlled access to the database, and with some code you can get the user name from the connection string, there is no mechanism to use that user name to determine if the user has access to any part of your application...i.e. you'd have to build your own security system, not a trivial exercise at all. Your data is also not as secure and/or your app is hard to manage because you have to manage users on SQL Server, which means you have to either open it up for the users to add logins (sysadmin privileges I believe) or you have to burden some dba to manage your users. Also, the server has to be know to all the users, which means they now know where to go to attack it. I'm probably forgetting a bunch of issues....this is just not a good idea unless the app is such that the user actuall is in control of the database, such as a single user or small group app were security really isn't a concern (they just lock the door at night, so people the don't know can't get in )



The second scenario is what I'm talking about as solving many of the issues you mentioned. In this case you control access to the database by having only a single SQL Server account that can access your database. You use the DataSources.Add() method within SetDataSources() in AppMain.vb. This is setup when the app is installed and controlled by whoever controls the db. There are a zillion ways to handle this (which I'm not going to go into now). Then you use RBS to secure the app. This provides authentication, a user object to work with, and hooks to determine a users permission. You can authenticate against a domain if you want (though you still have to maintain the users in your apps security tables, so RBS knows what permissions have been assigned). As the developer you define the permissions needed to access any object in the application and to access the data by configuring the BOs with permissions. The users with the proper permissions (defined by you) can manage users, including adding, editing and deactivating them as well as assigning them roles and permissions.



I hope this clears up some of your confusion. RBS is a big subject. I had lots of pain getting my head wrapped around it, but that is actually more a function of the nature of security than of any security system in general. If you haven't already, I'd suggest you do a test project to try this stuff out.

Thank you so much for going into this level of detail. I have a much clearer idea now about the various approaches and how to manage them. Your explanation is extremely helpful.



Dan

Glad to be of help!