Today I'd like to write something about two interesting SQL functions in the Oracle database: XMLDIFF
and XMLPATCH. I'll start with XMLDIFF : As its name indicates - it compares XML documents. I'll illustrate this
with an example ...

The functions' output indicates that the documents are equal - from the XML point of view. And this
makes sense since the XML standard allows different markup for the same content. Empty tags, for instance,
can be expressed as a single tag with a trailing slash (<tag/>) as well as with an opening and a closing
tag (<tag></tag>). Character entities like &quot; have the same meaning as the character itself (").
And there are more examples beyond these two. So comparing XML documents means more than just comparing
the text contents - the XML standard must be kept in mind, and XMLDIFF does exactly this. In the next example
we'll compare two documents which are different; also from the "XML point of view".

We can see, that XMLDIFF returns more than just the information that these two documents are
different. As it's UNIX pendant diff it returns a "delta" - instructions how to modify
the first document in order to get the second. This output can be consumed by XMLPATCH; as we'll see
in the next example: We'll apply the "patch instruction" to another XML document ...

Modify the text contents of the second tag tag within document to Text 2

Append another tag tag containing the text Text 3 and the attrbute option="a" within document.

... are now being applied to another XML document which is (in this case) even different
from the very first one (which we used to compare initially). The output of the XMLPATCH operation
is as follows ...

XMLDIFF and XMLPATCH are used internally by Oracle for the XML DB functionality of In Place XML Schema
Evolution. But one can (as we have seen) use them also for own purposes - the functions are interesting when
it's about comparing XML documents and computing deltas between them.
einem XML-Dokument zu erkennen und diese ggfs. auf andere Dokumente zu übertragen ...

Yes - it is possible: You can connect to the Oracle database as say: User "A" with
the password of User "B" - and that is not a security vulnerability. In this
blog posting I'd like to elaborate a bit on proxy authentication - this feature
is particular useful in three-tier-applications. Database connections are typically
part of the static application server configuration ("data-sources.xml").
And when such a java environments want to use the user accounts in the database
there is a problem: We cannot add each individual user to the datasource configuration.
The first arguments is, that there might be just too many users - the second one is that
changes to data-sources.xml are often not possible without downtime.
So we need a connection using one single technical user's credentials and the ability
to set the desired username at runtime.
And that is all what proxy authentication is about. We'll start with creating
the technical user (TECHUSER) and two "real" user accounts (REALUSER1 and REALUSER2).

TECHUSER used his own credentials to log in. But the user as which the
connection should be established was provided within the brackets. In SQL*Plus it
is that easy: We logged in as TECHUSER/techuser and we now areREALUSER1.

So far - so good. But for real world environments this is virtually useless - no end
user connects with SQL*Plus. And we cannot add the brackets to the static datasource definition
in the application server: we need to set the client user dynamically with some code - and
the following example will show how to do this. First I have a little testing program in
java language. It first connects to the database with hardcodedTECHUSER credentials,
then it looks up "as who" it is connected and finally it shows the contents of the EMP table.
Again: The username and password arguments in the call to DriverManager.getConnection are
hard coded and this will not change!

Only by giving the username on the command line we connected as REALUSER1 or REALUSER2 - the
password always was TECHUSER's one. With this approach a java program can obtain a database
connection from its application server, set the client username and connect to the
database as another "real" user. And this proxy connection can be changed while the
main "physical" connection remains open. The following code shows this - for clarity
I have removed the actual database actions and replaced them with the
pseudo method doDatabaseActions().

publicstaticvoid main(String args[]) throws Exception {
// open the physical connection as TECHUSER
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@sccloud030:1521/orcl","techuser","techuser");
// open the proxy connection for REALUSER1, do something and close
Properties props = new Properties();
props.put("PROXY_USER_NAME", "realuser1");
((OracleConnection)con).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);
doDatabaseActions(con);
((OracleConnection)con).close(OracleConnection.PROXY_SESSION);
// the physical connection is still open ...// now open the proxy connection for REALUSER2, do something and close
props.put("PROXY_USER_NAME", "realuser2");
((OracleConnection)con).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);
doDatabaseActions(con);
((OracleConnection)con).close(OracleConnection.PROXY_SESSION);
// finally close the physical connection
con.close();
}

When client/server applications are moving to a web environment,
this technology gets highly interesting - because the application can keep its existing
user, role and security model and move on to the web technology where all database connections
are done with one technical user.

REALUSER1 or REALUSER2 still need their CREATE SESSION privilege - the accounts also
must exist in the database and must not be locked. But (if needed) you actually can "forbid"
direct connections by setting the password to an "impossible" value ...

SQL> alter user realuser1 identified by values 'Hello'

The REALUSER1 account is still open and connects are possible in theory. But since the
IDENTIFIED BY VALUES command directly wrote "Hello" into the password table, one would
have to find out one of the password - which evaluate to "Hello" during the hashing process - I'll
say it that way: at least very difficult. Connections as REALUSER1 now are only possible as
connections through TECHUSER. But it is also possible to have both direct and proxy connections
in parallel.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.