>Since I've only started delving into Oracle, I have no idea on the usual way that DBA's want things done. Do they want to have to set up every user with their own Oracle ID? If so, how do you know which schema your data and the users will be put in?
Each user has their own ID. The app also has its own ID. You never put a production data base under a specific user's ID. If the user leaves the company, you have a bad situation. For each app that will have its own Oracle database, create a User ID that reflects the name of the App.
Next, you create Roles in Oracle [e.g., MyApp_Edit, MyApp_ReadOnly, MyApp_Admin]. Then you grant privileges to these roles. My users are never granted specific privileges. They are granted Roles. That way, if the purpose of a Role changes, you change the Role's privs. If you did this for each user, what a pain that would be.
You can also grant Roles to other Roles. For example, you would create the ReadOnly Role first and GRANT SELECT ON TABLE1 TO MyApp_ReadOnly. You have to do this for every table in the DB. Then you GRANT MyApp_ReadOnly to MyApp_Edit. Then you would GRANT INSERT, UPDATE, DELETE ON TABLE1 TO MyApp_Edit. Again you do this for every table in the DB except the user table and other tables that only the app admins should edit. GRANT MyApp_Edit TO MyApp_Admin.
The WNL I did last week has been posted at
http://fox.wikis.com/wc.dll?Wiki~BasicOracleForVFP>I too use a oApp.Security object. I write all the user rights to properties when they login. Of course, if their rights get changed, they have to log out then back in for the change to take affect. Some have complained about this in the past, but I'm not sure how to have the change take affect while they are logged in.
>
>Some of my checks are done at the menu level instead of on the form, at least for overall form access. You mention doing some of the ReadOnly check for controls in the control init. Does that create a lot of overhead when opening complex forms?
Not really. It's only 3 lines of code per control.
if This.lCheckPrivs and goApp.UserPriv = 'ReadOnly'
This.ReadOnly = .t.
endif
I have done more complex priv where I keep field level privs in a table on a user-by-user basis. When the user logs in, I create a goApp array with all the fields they have custom privs on. I then call a goApp method from the control Inits to retrieve what kind of priv they have on individual fields. Even with this degree of granularity, performance is not harmed.
Mark McCasland
Midlothian, TX USA