ROLES
-
I have a question about ROLES. I have been using UserID to manage connection to SQL for some time without problem. I am getting ready to deploy a Large project in which we are using both MSDE2000( for 10 or less workstations ) and SQL2000 for large networks. I have been advised that using a program ROLE is the most acceptable method to manage preconfiguration of the databases ( attached at install time.) we DONT want Customers to have access to our data structure as this is one of the copyrighted components of the Application. The Thoery is if an Application Role is defined in the database then it will carry over with the attach.
A. Is this true?
B. If so how do i connect to SQL in a Program Role? ( i have tried adding ";role=programName" and get no response ( other than the login dialog asking for userID and password)
Can someone help clarify this? I have bought several nice books trying to get a handle on this but none seem to invest more than a couple of paragraphs on the subject. and none cover how to accually use this Role.
Robert (ZEUSS)
Robert Dunlap
Robert Dunlap
http://www.codesonly.com
http://www.vbonly.com -
Lot of ground to cover there. First, roles are the preferred method of assigning permissions. Create the role, assign permissions, then add users to the roles. Same way you manage security using NT groups.
Application roles are a bit different, they exist so that once you connect you can execute "sp_setapprole" and that user now has ONLY the permissions associated with that role. Normally a user can have permissions by being members of many different roles.
Using an app role does nothing to protect your schema. Anyone with SA access can still see everything, change anything, etc. My opinion on this is that few schemas are worth protecting - if they are, use a proprietary data format. One of the reasons SQL is so successful is that its very open, allows the DBA to modify stored procs, tune indexes, even add cols if needed to support whatever uses may come up. Keep in mind that your average user isn't going to see the schema, just whatever you present via the application.
Finally, to use a standard role you still connect using whichever authentication method you prefer. Since a user can be a member of many roles there is no need to specify, SQL looks at the user and checks all roles to see which permissions they have. As I mentioned earlier app roles are a bit different, you have to first connect to the db using a standard connection, then do "connection.execute sp_setapprole" to enable the role.
Andy
-
2nd vote for Andy's thoughts. Is ROLE an application of some sort or do you mean application roles?
Steve Jones
-
Hmmmm,
in order for the experts to help me I need to better define the problem ( meaning my deficit of knowledge )
. I come from a foundation of COBOL and Fortran so Proprietary Data format is where I have been for 22+ years. I am using sql due to its Very Powerful feature set, and ability to be easily maintained in a large network. The Data Structure is part of the copywrited material in this contract. meaning, as a rule we dont want customer to be tinkering in our data structure or our indexes or Stored Proc's. We want to do these ourself. this App is Medical in nature so we must take some required steps to ensure privacy of the data also. our target will range from small ( 10 or less stations in a Doctors office ) to the very large ( 200 plus workstations and wireless devices in a Medical Center ) I make good use of Stored Proc's to handle data interactions.So now that the basic layout is there.
Problems
When we install MSDE it automatically configures in Windows Security mode ( excepting rare circumstance ) I have a SQL DMO applet that connects to this and changes the security to SQL so i can connect to the instance from Non Integrated Network members. The wireless devices are going to be personally owned by personel and used both in the Office and in the medical center. so we cant use Windows Integrated security. I have done some reading ( either just enough to make me dangerous or to much to do me any good without some foundational work ) In development and Alpha testing I have used sa and its associated pass to get data connection, But this in not an acceptable option for the deployed app for obvious reasons. My reading keeps taking me to Roles, and my understanding keeps pointing me to Application Roles. Any Input into this will be Treasured ( even if its only to say to give up and hire an SQL Professional as an advisor to finish and finalize the Model ), I am the Project Designer and there is no deep SQL member of the team so it falls to me to do these investigations and decisions.
Thoughts?
Robert ( ZEUSS )
Robert Dunlap
Robert Dunlap
http://www.codesonly.com
http://www.vbonly.com -
There is just no way to secure the schema. Even if you set your own SA password there is nothing to stop someone from stopping the service and just moving the mdf to a different server and attaching it. It's not wrong to secure it as much as you can, but I'd focus more on data security. If the customer changes the schema it'll either work and you'll never know or care, or it will break things which will lead to some nice additional revenue as you fix the problem.
Andy
-
Well based on your posting of last night I looked at that portion of the problem and you are right. One of the best parts about the SQL feature set is the ability to dynamically adjust the data schema to changes in need. So I am going to just include disclaimers to the End user that making changes there will violate the license aggrement and null any warranty's on that portion of the System. ( like you say nice revenue builder if we get a nosey DBA
) So this leaves me with some narrowed scope in my questions if you will permit. ( wish i could change the name of the thread now to Newbie 101 ) in the case of installing SQL we can name our needed parameters to the install personell to handle those unknowns. in the case of MSDE i have found a Big failure ( submitted to MS to no avail yet ) I read ( hehe dangerous again ) all about how to use the ini file to name some parameters but MSDE ignores any parameter after the first one. EX: i name security mode to be mixed or sql mode and no other entrys are processed so, i am unable to set the sa password. or anything. I am now trying to find a way to always know1. what is the security mode of the new MSDE instance?
2. what data do i need to change sa password to a known value for use in making the configurations
3. do I add a user to access the database with and count on workstation ID to make unique entrys in logs and in profiler?
4. if no to above do i use unique usernames for each operator? ( not a good solution as we anticipate several hundred in some instances ) or ( back to the original question ) do i make an Application Role and assign rights to it?
like I said Newbie questions for you guy's but I will never know if I dont ask. 🙂
I have used this forum once before and you both helped me and to good effect. I now recomend this site to anyone needing to know about the mysterys of SQL.
Robert (ZEUSS)
Robert Dunlap
Robert Dunlap
http://www.codesonly.com
http://www.vbonly.com -
Hopefully we have MSDE users that can help you with the details. For security you have these options (with lots of sub options!):
-NT Authentication, just create a group called YourAppAuthorizedUsers, tell their admin to put all the users in it. Easy to maintain.
-SQL authentication using single login, use the workstation name as you suggest to help pinpoint users. Easy to maintain.
-SQL authentication, every user gets their own login. Harder to maintain.
I've got an article coming up Mon that will talk about this a bit. Deciding which is right is tough. I personally app opting in most cases for the single SQL login and then securing the app separately.
Maybe that helps?
Andy
-
I'd use normal database roles and apply the security you anticipate based on roles. Then simple SQL Auth for the users and assign ALL users to a role.
If you need more granular security, and I'm guessing in a medical app that you do, I'd build a matrix which uses a table for each user name and then a table for groups. Drop a connecting table in between these to allow a many:many relationship. Then for the data, I'd do the same thing, but you'd have to have some criteria for making these joins. It's hard to describe, but if you have further questions let me know. For the basic security setup, I'd go with SQL Auth.
Even with App roles, you still will need the user to connect and the app then invoke the role. While you could share the user login, I would not recommend it. You will have to do user management somewhere, so might as well be done in SQL to ensure the proper auth.
Steve Jones
-
there will be three distinct roles for users in the Database.
1. Demographics personel who NEVER may see Medical data ( Mandated standard for medical applications )
2. the Nurse role who see's ( beleive it or not ) All Patients and All Data except Diagnostics Data used by physicians to Document proper Diagnosis and Proceedures.
3. Physician role who only see's his patients data ( again A Mandated Standard )
I had hoped to use Program to decide upon which of these three were needed based on Login to the application. and then activate a Defined Role to manage this access. the more I ask the more i am understanding that the only reasonable solution is to Contract a SQL professional for Advice and assistance in making these decisions and implementations. I am making these decisions without Near enough Operational experiance in SQL so I am Blind to any Limitations I will be making to my Client base with a choice made poorly.
Robert Dunlap
Robert Dunlap
http://www.codesonly.com
http://www.vbonly.com -
I tend to agree with your decision. This will be complicated and I'm not sure we can answer your questions thoroughly or quickly enough.
Basically though you will need to implement some more granular security for Doctors to only see their patients' data. For the others a role might suffice.
Keep in mind that whether normal or app roles, the role assigns permissions by table, not by row in the table.
Steve Jones
-
I am only in the last phases learning of the potential problems in Locking. it seems that Row level locking must be implemented using my own fields as SQL will only lock the row optimistically or lock the entire table. Is this common among all the major dataserver software? in my background we locked a row of data until the client app was done with it to assure we had complete control of data integrety, this seems sloppy to only lock the entire table or use the "Race" method. ( meaning the first to call UpdateBatch gets to keep his or her work. all others get an error and get to start over. Its been my observation in the other languages that its not appropriate in any situation to open a dataset for update and leave it available to be opened again by another user. There are no winners in that situation.
But now I may be once again comming up against my Lack of knowledge in this environment and Once again be misssing what would be obvious to a knowledgable DBA.
I dont even know how extensive the contract would be for a pro as I dont understand all of what is needed to integrate the Database interface to the needed apps. you guys have been lots of help. and thank you very much.
Robert Dunlap
Robert Dunlap
http://www.codesonly.com
http://www.vbonly.com -
Locking is tricky. The reason the db is so "open" is to allow max scalability - ie concurrent users. While it takes getting used to, you need locking to maintain consistency, not necessarily to keep someone else from changing the data. Take the case where Dr. X is editing the case history of a patient - what are the odds someone else will edit at the same time? Even if they do, with optimistic locking you have choices - last update wins, first update wins, or last update does conflict resolution which shows user field by field what changed so they can decide what to let go through. In addition, a LOT of db work is inserts. New prescriptions, new visits, etc.
I agree with Steve that implementing a security scheme of your own makes sense, not too much harder to do both and implement your own locking. Of course the danger with any type of application held lock is that it can orphaned - user goes to lunch, vacation, etc, leaving data locked, then someone has to clean up.
Andy
. I come from a foundation of COBOL and Fortran so Proprietary Data format is where I have been for 22+ years. I am using sql due to its Very Powerful feature set, and ability to be easily maintained in a large network. The Data Structure is part of the copywrited material in this contract. meaning, as a rule we dont want customer to be tinkering in our data structure or our indexes or Stored Proc's. We want to do these ourself. 
this App is Medical in nature so we must take some required steps to ensure privacy of the data also. our target will range from small ( 10 or less stations in a Doctors office ) to the very large ( 200 plus workstations and wireless devices in a Medical Center ) I make good use of Stored Proc's to handle data interactions.
) So this leaves me with some narrowed scope in my questions if you will permit. ( wish i could change the name of the thread now to Newbie 101 
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply