2 Replies Latest reply on Apr 3, 2018 5:33 AM by Adrian D

    Associate an ORDS client id with a database user id




      I have a POST request setup through ORDS which allows a supplier to send a data to the database via JSON and have it added to a sales table.  I control access to the request via OAuth2.  At present the supplier has to have a field in their data with their seller_id, but I'd like to control this through the db ideally to make it more secure.  Is it possible to associate a client key with a user id from the database, and have that field automatically populated when the supplier posts their data?  I assume I would do this by adding an extra field in the users table which includes the client_id, but I'm not sure how I'd then pass the client id into my procedure call when they post.  Can anyone advise?




        • 1. Re: Associate an ORDS client id with a database user id

          Bump.  Has anyone done this before?


          I suppose an alternative would be to change the endpoint to include the supplier name as part of the path in the handler, and then associate each client with their individual endpoint.  Does that make more sense?

          • 2. Re: Associate an ORDS client id with a database user id
            Adrian D

            Hi Ross


            I'm looking at the same type of issue trying to link the OAuth user to a database user.

            I think I have an approach that can work.

            If you do the oauth.create_client with the p_name equal to the users oracle username which is to be given access.

            Then in the PL/SQL you can reference the special bind variable of :current_user which will give you the OAuth client_id. Which gives you a value like KiTWLLitA9WCVE84Jj8kCA..

            Then you can query back against the user_ords_clients table to get back to the registered name which will be the oracle username.


            SELECT name

            FROM user_ords_clients

            WHERE client_id = 'KiTWLLitA9WCVE84Jj8kCA..'


            From my prototyping it seems to work correctly.

            Let me know what you think.