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:

BEGIN
    OAUTH.CREATE_CLIENT(
        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_REDIRECT_URI => NULL,
        P_SUPPORT_EMAIL => 'test@example.com',
        P_SUPPORT_URI => 'https://example.com',
        P_PRIVILEGE_NAMES => ''
    );
    OAUTH.GRANT_CLIENT_ROLE(
        P_CLIENT_NAME => 'sql_dev_client',
        P_ROLE_NAME => 'SQL Developer'
    );
    COMMIT;
END;

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;

NAME             CLIENT_ID  CLIENT_SECRET 
---------------- ---------- --------------
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' \
https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/<schema alias>/oauth/token

{
 "access_token":"9EVGMlgDLQ8N5clLKVLj0Q",
 "token_type":"bearer",
 "expires_in":3600
}

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 'https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/database/datapump/jobs/' \
  -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" : "https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/database/datapump/jobs/HR,EXP_SD_123/",
               "rel" : "self"
            }
         ],
         "operation" : "EXPORT                        ",
         "state" : "NOT RUNNING"
      }
   ],
   "limit" : 25,
   "links" : [
      {
         "href" : "https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/database/datapump/jobs/",
         "rel" : "self"
      },
      {
         "href" : "https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/database/datapump/jobs/",
         "rel" : "edit"
      },
      {
         "href" : "https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/metadata-catalog/",
         "rel" : "describedby"
      },
      {
         "href" : "https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/db-api/stable/database/datapump/jobs/",
         "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 'https://my-database.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/_/sql' \
  -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"
      }
   ]
}

Conclusion

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.