Updated 5 June 2023

Looking to secure your Oracle Database REST APIs served by ORDS using BASIC Auth? You can create an ‘ORDS’ user for this.

ORDS will have a set of known users and passwords that can be used to authenticate and authorize REST APIs.

When you create an ORDS user, you will be asked to provide:

  • the name of the user
  • a list of roles to grant to said user
  • a password

One of the system roles ORDS maintains is ‘SQL Developer.’ This role is granted to any Database User authenticated session. You may for…reasons want to use that same Role to protect say an ORDS AUTOREST TABLE.

If the SCHEMA is ‘SCOTT’, you could then authenticate as SCOTT/TIGER when making GET, PUT, POST, DELETE calls to ords/scott/emp/ or ords/scott/emp/10

But, what if you wanted to create a user called ‘ords_dev’ that was only known to ORDS and not to the database?

ORDS 3 – 21.4

java -jar ords.war user ords_dev "SQL Developer"
You're prompted for the password and you'll have to repeat it.
You’re prompted for the password and you’ll have to repeat it.

By the way, this is covered in the Docs, the Admin and Dev Guide to be specific.

Now we’re cooking with gas. Now I can connect and pull down and push up modules for my RESTful Services.

ORDS 22.1 and higher

ords --config c:\ords\vbox23\ config user add ords_dev "SQL Developer"
You can grant more than one role, simply do “Role1, Role2, Role3, … N”

Remember, you can use the –help for generating help on an ORDS command or subcommand.

❯ ords config user add --help

ORDS: Release 23.1 Production on Mon Jun 05 12:49:44 2023

Copyright (c) 2010, 2023, Oracle.

Configuration:
  /C:/ORDS/23.1/bin/


Usage:
  ords config user add [OPTIONS] <NAME> <ROLE>

Add or update a user, specify its password and role(s).

Options:
    -h, --help                   Show usage information for the command

        --password-stdin         To run the ords config user add command
                                 non-interactively use this option to read the
                                 password value from STDIN

Arguments:
        <name>                   The name of the user

        <role>                   One or more roles assigned to the user.

                                 Example:
                                 ords config user add user1 role1


                                 Enclose in double quotes for multiple roles
                                 separated by commas, and for role(s) that
                                 contains spaces:


                                 ords config user add user2 "role1, role2"

                                 ords config user add user3 "Example Role"




ORDS\23.1\bin took 2s
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.

19 Comments

  1. Hi Jeff – I was trying to get the SQL Developer Rest Development window connection to work in https mode without success – you say above “So I’m asking to connect over HTTP (if you picked secure/https – this won’t work)”.

    Is there any configuration I can do to enable this connection via https? All my curl commands and tests work fine from the command line using https, so it looks like a wrinkle in SQL Developer that I can’t iron out.

    Would be nice to get this to work under https, if you can provide a fix I’d appreciate it.

    Cheers, Mark

    • There’s no real reason i can think of (assuming you have access to the database) to use this feature anymore. So, is there a reason you’re wanting to use this vs the Database connection tree and the REST Services section?

  2. Martín Zabala Reply

    Hello,

    My solution for this issue in weblogic is, add


    false

    in file config.xml, restart the domain and sucess connect from SQL Developer

    Regards

  3. Hi Jeff.
    When I installed ords in Standalone Mode,
    I specified the next lines in the parameter file:

    ords.sqldev.password=desarest
    ords.sqldev.username=desarest

    I’ve created and probed successfully a REST from a table, following the instructions on
    “https://docs.oracle.com/database/ords-17/QSORD/get-started-with-oracle-rest-data-services”.
    Then, I tried to reproduce the section in “Create a RESTful Service from a SQL Query section”, but I got Authentication Failed message (Invalid resource owner credentials) when attempted to Create RESTful Services Connection, with
    Connection Name: ordstest
    Username: desarest
    X http
    Hostname: localhost
    Port: 8080
    Server Path: /ords
    Squema/Workspace: ordstest

    When executing the command line:

    java -jar ords.war user desarest “SQL Developer”

    it shows: “You need a console to read user credentials” and it doesn’t create the credentiasl file.
    It seems like I cannot create developer user.
    Thanks

  4. Looks like the docs link has gone bust. Wish Oracle wouldn’t move the docs around.

    • I have the same dream Zach.

      I’ve updated the post to fix the Docs link, sorry for the trouble.

    • Thanks, Jeff! Thanks for the article (as always), too.

  5. Were you able to fix the issue of connecting to ords services in weblogic thro’ SQL developer? I am facing the same issue. I am getting HTTP/1.1 401 Unauthorized. Thanks

  6. Omar Sawalhah Reply

    Hi Jeff,
    I need to do the same thing GlassFish, can you help, and if it is a must to create the same user in ORDS as in the standalone setup?

    Omar

  7. Hello,
    I have a question. I want to run ords in (integrated) weblogic.
    How do I give a user the SQL Developer role in weblogic?
    Does this role exist?

    Thank you

    • that’s a weblogic question, but you just give it a role called ‘sql developer’ – ORDS will know what means

    • Dimitris

      when I’m doing so I get the following error
      “Cannot connect to weblogic.
      HTTP/1.1 401 Unauthorized”

    • Adam Niwczinski

      I am facing the same problem and can’t find a solution.
      I have created the user in ORDS (using: java -jar ords.war user test_developer “SQL Developer”) and then created a user with the same name in weblogic, created a group with the name “SQL Developer” and assigned it to the test_developer user, but I am still getting the “Cannot connect to weblogic.
      HTTP/1.1 401 Unauthorized”
      Help! 🙂

    • Were you able to fix the issue? I am facing the same issue and get HTTP/1.1 401 Unauthorized

    • Balachandran

      Exactly I faced the same issue and i resolved it. Go to Edit existing RESTful Services Connection > correct the last parameter – Schema/Workspace. It should work.

    • correct the last parameter to what value? what needs to be changed?

Reply To Omar Sawalhah Cancel Reply