Be careful with OAuth2 client roles

There’s a performance section in the ORDS Best Practice document that encourages the use of OAuth2 clients rather than basic authentication for REST Services. It is excellent advice. The overhead of verifying a database username and password can add hundreds of milliseconds to the total response time. That can be avoided by using an OAuth2 client with ORDS.

A client can be granted ORDS roles: standard roles and custom roles. One of the standard ORDS roles that can be granted is SQL Developer role. This is a very useful, and powerful, role which when it is granted, should be granted with caution. Having said that, any grant of any role should be carefully considered and the implications evaluated.

Here’s an example of creating a Client Credentials type OAuth2 client and granting it SQL Developer role:

        P_NAME => 'sql_dev_client',
        P_GRANT_TYPE => 'client_credentials',
        P_OWNER => 'HR',
        P_DESCRIPTION => 'OAuth Client With SQL Developer role',
        P_ORIGINS_ALLOWED => '',
        P_SUPPORT_EMAIL => '',
        P_SUPPORT_URI => '',
        P_PRIVILEGE_NAMES => ''
        P_CLIENT_NAME => 'sql_dev_client',
        P_ROLE_NAME => 'SQL Developer'

With that client created one can get the client_id and secret for obtaining an access token:

SELECT name, client_id, client_secret FROM user_ords_clients;

---------------- ---------- --------------
sql_dev_client   3WjIAi..   myb-nW..

Using curl one can request an access token. This is what is referred to as a Two-Legged process where the client_id and client_secret is used to get an access token and that token is then used for subsequent service calls. Note that <schema alias> is the alias of the REST Enabled user that has created the client:

curl \
--user 3WjIAi.:myb-nWh.. \
--data 'grant_type=client_credentials' \<schema alias>/oauth/token


That access token is time based. It will only be valid for an hour. After which the above oauth/token request would have to be submitted again. Now that we have an access token, let’s use it to invoke a service which ships with ORDS but requires the SQL Developer role. In this example we’ll get a list of Data Pump jobs and our schema alias is hr:

curl '' \
  -H 'Authorization: bearer 9EVGMlgDLQ8N5clLKVLj0Q'

Which gives the below response:

   "count" : 1,
   "hasMore" : false,
   "items" : [
         "attached_sessions" : 0,
         "datapump_sessions" : 0,
         "degree" : 0,
         "job_mode" : "TABLE                         ",
         "job_name" : "EXP_SD_123",
         "links" : [
               "href" : ",EXP_SD_123/",
               "rel" : "self"
         "operation" : "EXPORT                        ",
         "state" : "NOT RUNNING"
   "limit" : 25,
   "links" : [
         "href" : "",
         "rel" : "self"
         "href" : "",
         "rel" : "edit"
         "href" : "",
         "rel" : "describedby"
         "href" : "",
         "rel" : "first"
   "offset" : 0

An interesting snippet of information. Of course one can do a lot more with these Database API services. Those Database API services that can be access by clients with SQL Developer role are not limited to just getting information.

Anything your database account can do

The Database API services perform a specific set of well defined operations. While access to the services require an ORDS role, such as SQL Developer or SQL Administrator, the database account used to execute the corresponding SQL is the REST Enabled schema.

There are other services provided by ORDS which require the SQL Developer role to access and one of note is the REST Enabled SQL Service. With the access token for a client with SQL Developer role one can submit any SQL script or statement.

For example, show the corresponding database account username:

curl '' \
  -H 'Authorization: bearer 9EVGMlgDLQ8N5clLKVLj0Q' \
  -H 'Content-Type: application/json' \
  --data-raw '{"statementText":"select user from dual","offset":0,"limit":256}'

Which confirms that the database user in this case is the HR schema…

   "env" : {
      "defaultTimeZone" : "UTC"
   "items" : [
         "response" : [],
         "result" : 0,
         "resultSet" : {
            "count" : 1,
            "hasMore" : false,
            "items" : [
                  "user" : "HR"
            "limit" : 256,
            "metadata" : [
                  "columnClassName" : "java.lang.String",
                  "columnName" : "USER",
                  "columnTypeName" : "VARCHAR2",
                  "isNullable" : 1,
                  "jsonColumnName" : "user",
                  "precision" : 128,
                  "scale" : 0
            "offset" : 0
         "statementId" : 1,
         "statementPos" : {
            "endLine" : 2,
            "startLine" : 1
         "statementText" : "select user from dual",
         "statementType" : "query"


For those familiar with ORDS this is not a great revelation and there are plenty of legitimate cases where an ORDS OAuth2 client would have the SQL Developer role. In fact a quick search online for ORDS OAuth2 examples will show some. My intent is to highlight that the SQL Developer role provides access to a range of powerful ORDS services and the use of the role should be carefully considered.

Should we ban anonymity on the Internet?

In an Information Security article a few months back, Bruce Schneier (author of Schneier on Security) and Marcus Ranum put some points forward for and against internet anonymity. I have to admit that I agree with Schneier and find Ranum’s argument quite weak. He appears to suggest that the main reason to enforce identity is to avoid spam. The tools aren’t great, but there are already mechanisms in place to address this. Criminals are always getting better at finding ways to exploit weaknesses in internet technologies increasingly at the heart of the way we shop, interact, work, entertain and inform ourselves. We just have to keep up with the pace in the cat and mouse game. Sacrificing anonymity, and the right to privacy, is too great a cost for just avoiding emails about Viagra ™ and Nigerian generals with a stash of cash to move out of the country.

What is the great danger of not being anonymous? Well it’s all the inferring that goes on about facts that get gathered around the things you search for, shop for, chat about, view and listen to. These are then used to categorise you for advertising, inclusion or exclusion from groups or activities. NetFlix provided a great example of this last year. Just weeks after the contest began, two University of Texas researchers showed that with the NetFlix data one could identify users and in some cases their political leanings and sexual orientation.

Getting back to Schneier’s point, trying to implement a robust identification system, which criminals can not outwit or take advantage of, is not possible…

Mandating universal identity and attribution is the wrong goal. Accept that there will always be anonymous speech on the Internet. Accept that you’ll never truly know where a packet came from. Work on the problems you can solve: software that’s secure in the face of whatever packet it receives, identification systems that are secure enough in the face of the risks. We can do far better at these things than we’re doing, and they’ll do more to improve security than trying to fix insoluble problems.

Your hosted application is a virus vector

We are all used to seeing SaaS as an acronym for Software as a Service. It also doubles as another acronym for the non-functional features of a SaaS architecture. When you’re providing a hosted service there are a number of non-functional features that need to be considered such as Scalability, Availability, Agility and Security. I’ll flesh out these concepts at a later date as they are all quite broad and encompass a lot of features. Security, for example, includes, but is not limited to ensuring the integrity of the system and the systems that connect to it.

One threat to system integrity is a computer virus. If your hosted application becomes a vector for the dissemination of viruses to your clients system it becomes a threat to your business, not just your systems. So, how do you test you hosted application to make sure infected files are handled properly?

At the beginning of this year I started working on Oracle CRM Sales Library OnDemand, which permits the uploading of documents to be shared with others in your organisation. It provides a rich set of social networking features that enables users to share, rate, review, and tag PowerPoint presentations an MS Word documents so that everyone in your organization can leverage the most effective sales materials. To share a document, or it’s contents, it must be uploaded to Sales Library and of course, the upload process involves virus scanning. In fact, virus scanning is the first step. Irrespective of the file type, before any logical validation takes place, such as supported formats, the file is interrogated by an anti-virus system. When a file fails the virus scan, the user has to be informed with meaningful message. So, while your hosted application does not have to be coded with anti-virus logic, it does need to be able to handle and report effectively, when a virus is found.

Once you have this logic in place, you have to be able to test that the behaviour of your application is correct. Now, you don’t want your development or testing environment to be using files with viruses to test with. In fact, your organisations anti-malware system would probably prevent you from storing the files anyway. Thankfully, the European Institute for Computer Antivirus Research has a solution. For testing the anti-virus error handling logic in our application we attempt to upload a file with just the EICAR test string as content.


The above is not a virus, but is a test string for anti-malware systems. In unit tests, this can be used as the only content of an InputStream. You should provide a dummy implementation of your virus scan system when unit testing too. Remember, you’re not trying to test the anti-virus software. You want to test the behaviour of your application when the anti-virus software reports a virus is found. When testing the deployed Sales Library application, the text file can be saved with a .ppt or .doc extension and then uploaded. The only content of the file should be the test string.

I have yet to find an anti-virus system that does not comply with this EICAR anti-virus test string. A simple test in your own environment will reveal if your anti-virus software does or not. I suspect that it will, and you can proceed with confidence that your hosted application is handling contaminated files properly for the protection of yours and your customers business.

Social CRM Goes Live!

Announced at last year’s OpenWorld, and previewed at last month’s Enterprise 2.0 Conference, Oracles’ Social CRM goes live today at This adds another social network, although quite a unique one, to the Oracle social grid.

So, how does Social CRM compare to the Oracle social networks and what makes it so special? Let’s look at what is in the Oracle Community already. There are blogs, forums and wikis that bring together people who are interested in Oracle products and technology. They provide the communication aspect of collobaration. And, more importantly, it is collobaration around what Oracle sells.

Social CRM introduces tools to support the doing aspect of colloboration. However, it is collobaration around the products and services you sell. The heavy weight, process driven, data entry intensive tools of which we are so familiar with, and still serves a valuable purpose in the enterprise, does not cater for every aspect of how business is done. The human element in selling, in particular, is not easily modelled. Social CRM is a small step in the right direction with tools, such as Sales Prospector, to increase sales and decrease data entry.

My own involvement in Social CRM started in January of this year, looking after the Security and User Provisioning side of the platform. Of course we hadn’t even reached code chill before we started work on the next version, so we are still very busy. Watch out for more (1, 2) later in the year. For me, the most rewarding part of the work is, coincidentally, the collaboration. You see, Social CRM is made possible through Agile software development practices. It is collaborative from the inside out.

Oracle Directory Manager and Application Development

Oracle Directory Manager is a Java-based tool for administering Oracle Internet Directory (LDAP). The Oracle Directory Manager is the main directory administration tool and it is installed with Oracle Internet Directory.

When developing applications there is often one or more central LDAP directories for developers to use. When working on a new application it is often necessary to reset entries, test scenarios, etc. However, it is unlikely that everyone’s desktop will have the entire OID installation. On my desk alone there are 3 desktop machines, and one laptop and non of them have the full Identity Management stack. In fact, the laptop is from when I was with Siebel so, although it was manufactured this century, it has little more than JDeveloper, Thunderbird and Oracle Calendar running on it.

One easy way to have Oracle Directory Manager on every developer’s machine, but not having to install anything else, is to take advantage of the fact that it is a Java application.

To achieve this, copy some jars (over 15 of them!) from the ORACLE_HOME/jlib and the ORACLE_HOME/ldap/oidadmin directories to a directory on your PC. Let’s call it oidadmin. Keep the directory structures. The entire list of jars is below. The main class is oracle.ldap.admin.client.NavigatorFrame and there are a few parameters that need to be passed to it. The entire command line is too long to type, let alone remember, so put it all in a file called oidadmin.cmd (when on windows) in the same oidadmin directory.


-classpath "./ldap/oidadmin/osdadmin.jar;

The above is formatted for readability and should be all on the one line. On windows I create shortcut on the desktop to the command file. The final touch is to use the OID Directory Manager icon for the shortcut. Any machine with Java can become a OID Directory Manager machine which I have found really useful for demonstrations and collaboration with developing new solutions.