Home » RDBMS Server » Security » Multi-tier auditing in Oracle from Business Objects
| Multi-tier auditing in Oracle from Business Objects [message #246091] |
Tue, 19 June 2007 21:38  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
We are in the early phases of design for an Operational Data Store using Oracle 10g and Business Objects. Australia has quite strict privacy laws, and we have a requirement to log all accesses to personal identifying information (eg. a name and a phone number together).
Oracle Auditing looks like the right solution, using Proxy Auditing in order to get the identity of the Business Objects user on the audit trail rather than just the common web server account.
- Is this right?
- Will it really log the User Id authenticated by Business Objects across the Web Portal even though Oracle has no knowlege of any such user?
- Does anything extra need to be done in Business Objects (other than normal bog-standard authentication)? Or is it all set up completely within Oracle?
Normally I would just hand this on to the DBA, but we wont get one until later in the project. The doco in the Concepts manual and the Security manual are a bit sketchy on Multi-Tier implementation, and I didn't get any joy in 'Proxy' and 'Audit' searches.
All I really need to know is: Is this easy-peasy and the DBA will easily handle it when he comes on board? Or is it a problem that I need to escalate right now?
Ross Leishman
|
|
|
|
| Re: Multi-tier auditing in Oracle from Business Objects [message #246391 is a reply to message #246091] |
Wed, 20 June 2007 15:14  |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I don't know if this will help but here's some ideas.
As I don't have BO I will use SQL*Plus.
In the first example, we use the proxy authentication.
In this case, the AS knows and connects to a generic user whose only granted privilege is CREATE SESSION.
The end user is known in Oracle but cannot connect directly: he must connect through the application which will connect for him.
First setting the environment. We will enable auditing to see what's Oracle get.
SYSTEM> -- Generic application user with only CREATE SESSION privilege
SYSTEM> create user app_generic identified by app_generic;
User created.
SYSTEM> grant create session to app_generic;
Grant succeeded.
SYSTEM> -- Application role
SYSTEM> -- (contains all privileges required for a user to execute application actions)
SYSTEM> create role app_role;
Role created.
SYSTEM> -- Application end user - cannot connect directly
SYSTEM> create user app_user1 identified by values 'INVALID PASSWORD';
User created.
SYSTEM> grant create session to app_user1;
Grant succeeded.
SYSTEM> -- Grant him application role
SYSTEM> grant app_role to app_user1;
Grant succeeded.
SYSTEM> -- Say he can connect through generic user
SYSTEM> alter user app_user1 grant connect through app_generic with role app_role;
User altered.
SYSTEM> select user_id, username from dba_users where username like 'APP_%';
USER_ID USERNAME
---------- ------------------------------
70 APP_USER1
68 APP_GENERIC
2 rows selected.
SYSTEM> -- activate auditing session
SYSTEM> audit session;
Audit succeeded.
Now we connect as the end user through the application generic user
SYSTEM> connect app_generic[app_user1]/app_generic
Connected.
APP_USER1> -- See actual user
APP_USER1> show user
USER is "APP_USER1"
APP_USER1> -- See session roles
APP_USER1> select * from session_roles;
ROLE
------------------------------
APP_ROLE
1 row selected.
We see that we connect with the generic user but the actual user is the end user with application role enabled.
Let's now see what's Oracle recorded:
SYSTEM> set recsep wrap
SYSTEM> set recsepchar '-'
SYSTEM> select username, action_name, priv_used, timestamp,
2 logoff_time, client_id, comment_text
3 from dba_audit_trail
4 where username like 'APP%'
5 /
USERNAME ACTION_NAME PRIV_USED TIMESTAMP
------------------------------ ---------------------------- ---------------------------------------- -------------------
LOGOFF_TIME CLIENT_ID
------------------- ----------------------------------------------------------------
COMMENT_TEXT
------------------------------------------------------------------------------------------------------------------------
APP_GENERIC PROXY AUTHENTICATION ONLY 20/06/2007 21:46:09
Authenticated by: DATABASE
------------------------------------------------------------------------------------------------------------------------
APP_USER1 LOGOFF CREATE SESSION 20/06/2007 21:46:09
20/06/2007 21:47:11
Authenticated by: PROXY;EXTERNAL NAME: <remove for confidentiality>
------------------------------------------------------------------------------------------------------------------------
2 rows selected.
SYSTEM> set recsep off
We see Oracle recorded 2 connections:
- APP_GENERIC which action is just to serve as proxy authentication but is authenticate by the database
- APP_USER1 which is the actual user authenticated by the proxy
As the end user password is invalid, he cannot connect directly.
As the generic user has no privilege, he cannot do anything on the database.
I don't know what are the possibilities of BO but proxy authentication can be done in Java and OCI.
However if you can't then there is another possibility: using client identifier.
First, clean the environment, remove the end user and grant app_role to generic user.
SYSTEM> delete sys.aud$;
4 rows deleted.
SYSTEM> commit;
Commit complete.
SYSTEM> drop user app_user1;
User dropped.
SYSTEM> grant app_role to app_generic;
Grant succeeded.
SYSTEM> select user_id, username from dba_users where username like 'APP_%';
USER_ID USERNAME
---------- ------------------------------
68 APP_GENERIC
1 row selected.
Now the first thing that will do the application after connection is to set the client identifier with the end user name.
Unfortunatly, as we will see, Oracle does not record the client identifier in the logon/logoff record, only in the other audited actions, because at the time of the connection the client identifier is not already set.
To verify this, we will insert a row in one of my table which is audited.
SYSTEM> grant insert on michel.t to app_generic;
Grant succeeded.
SYSTEM> audit insert on michel.t by access;
Audit succeeded.
SYSTEM> connect app_generic/app_generic
Connected.
APP_GENERIC> exec dbms_session.set_identifier ('APP_USER1');
PL/SQL procedure successfully completed.
APP_GENERIC> select sys_context ('userenv','client_identifier') from dual;
SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
------------------------------------------------------------------------------------
APP_USER1
1 row selected.
APP_GENERIC> insert into michel.t values(2);
1 row created.
APP_GENERIC> commit;
Commit complete.
Let's see what's in Oracle audit trail.
APP_GENERIC> connect system/michel
Connected.
SYSTEM> set recsep wrap
SYSTEM> set recsepchar '-'
SYSTEM> select username, action_name, priv_used, timestamp,
2 logoff_time, client_id, comment_text
3 from dba_audit_trail
4 where username like 'APP%'
5 /
USERNAME ACTION_NAME PRIV_USED TIMESTAMP
------------------------------ ---------------------------- ---------------------------------------- -------------------
LOGOFF_TIME CLIENT_ID
------------------- ----------------------------------------------------------------
COMMENT_TEXT
------------------------------------------------------------------------------------------------------------------------
APP_GENERIC LOGOFF CREATE SESSION 20/06/2007 21:59:57
20/06/2007 22:01:20
Authenticated by: DATABASE
------------------------------------------------------------------------------------------------------------------------
APP_GENERIC INSERT 20/06/2007 22:00:05
APP_USER1
------------------------------------------------------------------------------------------------------------------------
2 rows selected.
SYSTEM> set recsep off
We see the generic user authenticated by the database (normal connection) but client_id is null when it is set for the insert statement.
Hope this will help you.
More can be done.
For instance, end user may also be authenticate with a password through generic user.
Application role may not be enabled by default but activated by the application through secure application role mechanism.
Regards
Michel
|
|
|
|
| Re: Multi-tier auditing in Oracle from Business Objects [message #246426 is a reply to message #246391] |
Wed, 20 June 2007 21:32 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Wow, you put a lot into that, thanks!
So, with the first example (proxy), the individual users must be known to Oracle (via CREATE USER). This is not for us, because we only want to identify new users in one place. We must register new users in BO to control its security layer (what Universes are available, and what security filters are applied). It would be an undesirable overhead to also create the same users on the database just for auditing.
The second one is more interesting. It really hinges on whether we can get BO to issue the dbms_session.set_identifier call as part of its connection protocol. We might get lucky there, but I won't hold my breath. I wonder if there's something in the Oracle*Net setup on the BO server that handles this?
If we can't get BO to issue the dbms_session.set_identifier call, then we might be in trouble. I guess I'll have to fling this to a BO expert now.
That was a big help, thanks a lot.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Wed Dec 11 20:08:40 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
