This question came up twice in 3 days, so of course it meant it was time to write a blog post.

What is ‘:current_user’ when it comes to ORDS?

ORDS Docs & Implicit Binds

Who are you?

So in a database query or anonymous block backing an ORDS API Handler (GET, PUT, POST…) you can refer to :current_user and get the Authenticated USER on the session.

You might have several things you want to do with this information….I’ll leave that up to your imagination, but one general idea is you would have an additional layer of security. Yes, you have the ORDS Role, BUT…

Now, our developer/architect Colm has talked about :current_user before, but in his example, he showed it with BASIC Auth.

The question has been…

…but what about OAuth2 Clients?

What I’ve been telling people is that you should expect to get the Client ID, or –

In this case, this is the ‘who’ that ORDS associates with your session/visit.

Ok, so how do I test this?

Much like Colm did, I’ll have GET handler that prints the user’s name, via

select :current_user from dual
In the latest update to ORDS, we added :binds to your Code Editor for easier selection.

Ok, now I have created a priv, role, and said Client with required role. And the ‘who’ API is being protected by the same priv.

Let’s run it.

Ta-da! It returns just what I would expect.

Or, if I want to make this a little bit more user-friendly, I’ll just “Alias” the :current_user column in my SELECT handler.

P.S. I love The Who.
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

4 Comments

  1. Andreas Markus Reply

    Is it possible to get a hold of the client_id / user when I have auto rest enabled a package and oauth client is active? There is no module in this case so no :current_user known in the package procedures/functions. I have tried to hack in some owa_util.get_cgi_env calls, but that breaks the call all together:

    {
    “code”: “UserDefinedResourceError”,
    “title”: “User Defined Resource Error”,
    “message”: “The request could not be processed due to an error in a user defined resource”,
    “o:errorCode”: “ORDS-25001”,
    “cause”: “An error occurred when evaluating the SQL statement associated with this resource. SQL Error Code 6502, Error Message: ORA-06502: PL/SQL: numeric or value error\nORA-06512: at \”SYS.OWA_UTIL\”, line 359\nORA-06512: at \”INF.KLANTPORTAAL_WSE\”, line 24\nORA-06512: at line 2\n”,
    “action”: “Ask the user defined resource author to check the SQL statement is correctly formed and executes without error”,
    “type”: “tag:oracle.com,2020:error/UserDefinedResourceError”,
    “instance”: “tag:oracle.com,2020:ecid/tqQJT2Tx3H6PJkjv46Tcvw”
    }

    When I use USER, I get the current schema / owner INF

    • You can’t access the runtime environment from your plsql when using AUTOREST

      If you need to do that, then you need to write your own REST Module.

      Now, if you’re wanting to build your own custom security and have it applied to autorest objects, then you can use the prehook feature to do this.

    • Andreas Markus

      Thank you for answering that fast! I didn’t expect that, so I’ve been trying a bit further and found that SYS_CONTEXT(‘USERENV’,’CLIENT_IDENTIFIER’) will give me the USER_ORDS_CLIENTS.CLIENT_ID. Not sure if this holds over different versions of ORDS. At my current site it is at 20.4 and they will be upgrading to current in the foreseeable future. Implementing the prehook feature is too big a step for now. I have tried to test the client_identifier on apex.oracle .com and I’m able to create the service and the oauth client. But when I try to get an oauth token via curl, it always times out.

  2. Rodolfo Cartas Reply

    Though it’s true you get the client-id when using Client Credentials, the Authorization Code or the Implicit flows will actually return the authenticated user name and not the client id.

Write A Comment