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?
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 –
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
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.
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.
4 Comments
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.
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.
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.