Ultimately, I want the tables of a database to be accessible by only one specific user, to simply prevent anyone but this "super user" from accessing the data outside of the database application.
Based upon the "Managing application user IDs in n-tier applications" section in the 10.1B Programming Interfaces manual, I'm thinking about implementing a security system in which one CLIENT-PRINCIPAL object is used to identify the user (for logging, auditing, task security, etc.) for the duration of a session, and another short-lived CLIENT-PRINCIPAL object is used on AppServer activation to set the database user to the aforementioned "super user" so that the AppServer session has access to the data...
In the AppServer Activate procedure:
Fetch "session" data from context store
If "session" data is available then
Create "session" CLIENT-PRINCIPAL object.
Load CLIENT-PRINCIPAL object from context store.
Create "database" CLIENT-PRINCIPAL object.
Set database user ID using "database" CLIENT-PRINCIPAL object.
Destroy "database" CLIENT-PRINCIPAL object.
Set "session" ID using "session" CLIENT PRINCIPAL object.
End if.
In the AppServer Deactivate procedure:
If "session" CLIENT-PRINCIPAL is valid then
Set database user ID using "session" CLIENT-PRINCIPAL object.
Store "session" CLIENT-PRINCIPAL in context store.
Destroy "session" CLIENT-PRINCIPAL object.
End if.
The above is slightly simplified and assumes appropriate login and logout processing. Even so, with my stated goal in mind, is this a reasonable approach or am I making things too complicated?
I'm curious to know why you want to make the database only accessible to a single "super user". Doing so would mean that the auditing logs attribute all changes to this single user. Most internal and external auditors that I've worked with require exactly the opposite - no super user and all changes attributes to the real person.
The idea is to grant DB access only to the AppServer-hosted application (by using a named "super user"), to prevent any DB access outside of the application, and to avoid having to update the Can-* metaschema fields with each change in users and/or groups.
Suppose that I will not use the built-in Audting of OE, but instead use the aformentioned "user session" client-principal to create audit logs, etc. Is my approach vaild, or is there a better way?
BTW, does the OE Auditing system always use the DB user ID, or can one specify the client-principal object to use?
OpenEdge auditing can use the DB user ID or, if you're using client pricipals, it can use them. Take a look in the data dictionary under Admin -> Database Options : User Application UserId for Auditing. With this enabled, it is possible to enable auditing without needing to define any database users. Since you only plan to allow access to the database through an AppServer, this can be a very secure configuration. With this approach, you can avoid the need to updated the can-* tables, but still have audited events attributed to a real user.
Using OpenEdge Auditing it the only way to make sure that all changes to the database are correctly and securely audited. Any other auditing approach can be easily (and secretly) switched off by the DBA. OpenEdge Auditing will not prevent switching off auditing, but it will audit that event too.