>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.
>

Ok, so I would create the apps ID and use it's schema, creating all of the other users under it, correct?


>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.
>

I've read though many of the Oracle .pdfs and understand the use of roles. I compare it to creating security groups in NT/2000, assigning users to groups, etc.


>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.
>

You do this via a SQL or control file script, correct?


>The WNL I did last week has been posted at http://fox.wikis.com/wc.dll?Wiki~BasicOracleForVFP
>

Good, I've been waiting for that to get posted.


>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.

That's good to know that it can be done at that level, but hopefully it'll never get to the point of needing field level validation.


- Brian