|
Audit Trail
|
In this issue, I will continue to talk about three-tier security.
Last time, I used JDBC thin driver to create a connection pooling and checked audit record.
UNKNOWN appeared in TERMINAL column in JDBC thin driver and I was unable to identify
the middle tier (i.e. AP server.)
Also, if I attempt to use a single connection from the middle tier,
it might cause a critical security hole.
I actually try following application:
1. Web-based internal system
2. Create DB user for each position
e.g. employee, chief, manager
3. Grant a role to every position and ensure security.
4. Allow the user to refer to table MANAGER.HYOUKA from the middle tier (AP server) but do not allow
the user to update this table.
How do you design your application under such environment?
Method 1: Ask the user to enter DB user/password on the middle tier.
In this case, the user needs to always remember the DB user.
Besides, if there are too many DB users, this method does not work well with
the connection pooling.
Method 2: Create a super user (APPSRV) and let APPSRV handle all accesses from the users.
This method works well with the connection pooling, but it is not possible to set role-based
Oracle security.
Thus, this method is not capable of preventing intentional or inattentive data collapse by APPSRV.
APPSRV is only audited, which means that it is not possible to audit activities of every DB user.
This is a critical security hole because even accountability is not an audit target.
In addition to Method 1 and 2, Oracle9i supports proxy authorization.
Proxy authentication method allows the user to connect the middle tier with minimum privilege
by proxy and and allows the user to connect by using the proxy authentication. This proxy
authentication enables connecting the database via connection pooling with the privilege of
the authorized user when SQL statement is processed.
Now, I will actually use the proxy authentication.
Before I do so, I configure the environment.
*******************************************************************************
(1) Create PROXY, EMPLOYEE, CHIEF, and MANAGER users.
(2) Grant privilege of CREATE SESSION to PROXY user. Grant privileges of CREATE SESSION
and RESOURCE to other users.
(3) Create ROLE1 with SELECT privilege to table MANAGER.HYOUKA.
(4) Create ROLE2 with INSERT privilege to table MANAGER.HYOUKA.
(5) Grant ROLE1 and ROLE2 to EMPLOYEE user.
(6) Allow EMPLOYEE user to connect by proxy authentication. (ROLE1 and ROLE2 are available.)
SQL> alter user employee grant connect through proxy with role role1,role2 ; <--(a)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
(7) Audit EMPLOYEE user if connecting by proxy authentication.
SQL> audit session by proxy on behalf of employee ; <--(b)
^^^^^^^^^^^^^^^^^^^^^
*******************************************************************************
|
(a) above allows proxy authentication. ROLE1 and ROLE2 are available.
Following SQL statement allows all roles owned by user EMPLOYEE.
SQL> alter user employee grant connect through proxy ;
(b) above audits activities of user EMPLOYEE by proxy.
Following SQL statement audits all users connecting by proxy.
SQL> audit session by proxy on behalf of any ;
I check how audit is performed.
*******************************************************************************
SQL> select * from dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
--------- ---------- -------------- --------- ---------
EMPLOYEE PROXY CREATE SESSION BY ACCESS BY ACCESS <--(c)
^^^^^
*******************************************************************************
|
Take a look at (c). It indicates that audit process is made to activities of
user EMPLOYEE connecting by proxy.
Now, I actually log in as user EMPLOYEE with proxy authentication and
issue SELECT and INSERT statements.
*******************************************************************************
Connecting from Web...
-- select from the proxy connection --
1 <--(d)
-- insert with the proxy connection --
Exception : ORA-01031:Insufficient privileges <--(e)
*******************************************************************************
|
As ROLE 1 with SELECT privilege is allocated, SELECT statement (d) succeeded.
However, ORA-01031 occurs after INSERT statement (e) is executed because
ROLE 2 with INSERT privilege is not allocated.
I check the audit record.
*******************************************************************************
SQL> select os_username
2 ,username
3 ,terminal
4 ,to_char(timestamp,'hh24:mi:ss') timestamp
5 ,action_name
6 ,to_char(logoff_time,'hh24:mi:ss') logoff_time
7 ,returncode
8 from dba_audit_session ;
OS_USERNAM USERNAME TERMINAL TIMESTAMP ACTION_NAM LOGOFF_TIME RETURNCODE
---------- --------- --------- ----------- ---------- ------------- ----------
SYSTEM EMPLOYEE KSHINKUB 09:50:41 LOGOFF 09:50:42 0
SYSTEM EMPLOYEE KSHINKUB 10:05:27 LOGOFF 10:05:27 0
SYSTEM EMPLOYEE KSHINKUB 10:13:33 LOGOFF 10:13:33 0 <--(f)
^^^^^^^^ ^^^^^^^^
*******************************************************************************
|
Audit record for user EMPLOYEE is created properly.
Last time, UNKNOWN appeared in TERMINAL column. This time,
KSHINKUB appears in TERMINAL column.
End users are not audited yet.
Proxy authentication has lots of advantages in security.
1. Use a role and ensure security for each user.
2. Allow the user to connect from the middle tier with minimum privilege.
3. Ensure accurate accountability to DB user.
4. In addition to DB to role management, security can be ensured in both
DB and AP servers.
Reference
Following is a sample source of using a proxy authentication.
/*+----------------------------------------------------------------------------+
*| ProxyConnSample |
*+----------------------------------------------------------------------------+*/
import java.sql.*;
import javax.sql.*;
import java.util.Properties;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.oci.*;
class ProxyConnSample
{
public static void main (String args [])
throws SQLException
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
String url = "jdbc:oracle:oci8:@nas";
OracleOCIConnectionPool cp = new OracleOCIConnectionPool("proxy"
,"proxy"
, url
, null);
Properties prop = new Properties();
String[] roles = {"role1"};
prop.put(OracleOCIConnectionPool.PROXY_USER_NAME,"employee" );
prop.put(OracleOCIConnectionPool.PROXY_ROLES, roles);
// Get the proxy connection
OracleOCIConnection conn = (OracleOCIConnection)
cp.getProxyConnection(OracleOCIConnectionPool.PROXYTYPE_USER_NAME
, prop);
Statement stmt = conn.createStatement ();
ResultSet rs = stmt.executeQuery ("select * from manager.hyouka");
System.out.println ("-- select from the proxy connection --");
while (rs.next ())
System.out.println (rs.getString (1));
rs.close();
rs = null;
System.out.println ("-- insert with the proxy connection --");
try {
stmt.execute("insert into manager.hyouka values (2)");
} catch (SQLException e) {
System.out.println ("Exception : " + e.getMessage());
} finally {
if (stmt != null)
stmt.close();
}
conn.close();
conn = null;
cp.close();
cp = null;
}
}
|
Koji Shinkubo
|
|
