As a database administrator I have been called on to oversee many, many kinds of systems over the years. I have supported everything from prototype proof-of-concept systems with a single programmer to high availability production systems with thousands of end users; servers that support commercial off-the-shelf applications and servers that support multiple custom Java applications. It is an industry standard best-practice to have multiple levels of test and development platforms for every production system, and it is fair to say that because of this most of the systems under my watch have been used for product development purposes of one kind or another rather than end-user activities.
In my experience it is common for most commercial Java-based web applications to have a single database schema that owns all of the application-related code and database objects. End users log in to an application level account while the application uses the single database account – usually through a JDBC connection pool – behind the scenes. This makes user administration at the database level much simpler, since application users are usually maintained by a dedicated application administrator. Unfortunately this practice often gives rise to management issues of another sort in the development database environments.
The problem is that application developers have (gasp!) been known to share the username and password for the application schema owner account and use it to connect to the database from their various development tools. This practice makes it very difficult to audit developer actions accurately, or to maintain any sort of controls on just who exactly has access to that particular account. Since the credentials are shared, developers often take it upon themselves to share those credentials with new team members before they have been properly certified for network access or approved by management to have access to sensitive application code or data. One way to combat this issue of access proliferation is to use a method of user authentication that Oracle calls “proxy” authentication. It allows a user to connect to a database as one user and on connection become another user. This capability was originally deployed by Oracle as a way for applications to authenticate end users to individual database accounts through a common application account, but it works just as effectively the other way around.
In this model, each developer is given a personal account in the test or development database. A developer role, which includes system privileges required for software development across schemas like “create any table” and “create any procedure” is assigned to each developer account. Most application development can now be handled – and audited – through these personal accounts. Because the accounts are now personal there is no incentive for the developer to share their credentials with another user; it would be relatively simple to tell if a personal account was being shared, and doing so would be grounds for termination with most companies.
In the rare cases where direct access to the application schema is required, such as working with materialized views, DBMS jobs, or Oracle Scheduler, proxy authentication can be used. The application schema is altered to allow connections from individual developer accounts, like this:
alter user app_owner grant connect through dev_user;
Once this proxy privilege has been assigned, the developer can connect to the application schema using their personal credentials instead of the shared account credentials. All they need do is append the application account name in brackets to the end of their development account name, like this:
By connecting in this way, the developer can still perform needed actions but need never be aware of the application owner account’s real password. The proxy account name (the developer’s personal account) is available in the system session context, and can be automatically made visible in the v$session view through a database trigger so that the DBA can tell who is connected to shared accounts at all times.
CREATE OR REPLACE TRIGGER db_session_trig AFTER LOGON ON DATABASE v_proxy_user varchar2; BEGIN v_proxy_user := sys_context('userenv','proxy_user'); if v_proxy_user is not null then dbms_session.set_identifier(v_proxy_user); end if; END;
select username, osuser, client_identifier from v$session where username='APEX_040000'; USERNAME OSUSER CLIENT_IDENTIFIER ----------------- --------------- ---------------------- APEX_040000 oracle PETE
Using the PROXY_USERS view it is easy to determine exactly which developers have access to each application owner account as well.
PROXY CLIENT AUTHENTICATION FLAGS ---------- --------------- -------------- ----------------------------------- PETE APEX_040000 NO PROXY MAY ACTIVATE ALL CLIENT ROLES
Because even basic auditing captures the OS username of the developer, the audit trail will record the actual developer behind DDL operations executed as the application owner.
Sep 4 10:04:07 testdb Oracle Audit: SESSIONID: "12345" ENTRYID: "1" STATEMENT: "6" USERID: "APEX_040000" USERHOST: "myserver" TERMINAL: "pts/2" ACTION: "7" RETURNCODE: "0" OBJ$CREATOR: "APEX_040000" OBJ$NAME: "TEST_TABLE" SES$TID "4567" OS$USERID: "oracle"
Using individual developer user accounts with proxy account access to application schemas, it is possible to allow developers to work in shared accounts while still maintaining account credential security, visibility of connected users, and an accurate audit history.