DBA Hub

📋Steps in this guide1/10

Oracle REST Data Services (ORDS) : Authentication Enhancements (ORDS_SECURITY and ORDS_SECURITY_ADMIN Packages)

This article demonstrates OAuth setup using the ORDS_SECURITY and ORDS_SECURITY_ADMIN packages.

oracle miscconfigurationintermediate
by OracleDba
18 views
1

Assumptions

This article provides an update of the OAuth configuration found in the following article. - Oracle REST Data Services (ORDS) : Authentication Rather than repeating the contents of that article, it is assumed all setup in the first article is completed before we move forward. That includes the following. - Create a Test Database User - Enable ORDS and Create a Web Service - ORDS Roles and Privileges - First-Party Authentication (Basic Authentication) - Only needed for authorization code and implicit flows. Assuming that setup is complete, we are ready to move forward.
2

ORDS_SECURITY vs ORDS_SECURITY_ADMIN

The and packages essentially do the same thing. The differences is all routines in the package include a parameter to indicate which schema the action should be performed under. So the package is for working on the current schema and the package is for an administrator to work across all schemas in a database. For the remainder of this article we will focus on the package, but this applies equally to the package, with the addition of the extra parameter.
3

OAuth

There are several methods of authorizing to the web service using OAuth. Before starting each example, make sure you clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. You do not need to delete the web service itself or the ORDS role, privilege and URL mapping. Each authorization example assumes these are already in place. OAuth revolves around registering clients, which represent a person or an application wanting to access the resource, then associating those clients to roles. Once the client is authorized, it has access to the protected resources associated with the roles.
4

OAuth : Client Credentials

The client credentials flow is a two-legged process that seems the most natural to me as I mostly deal with server-server communication, which should have no human interaction. For this flow we use the client credentials to return an access token, which is used to authorize calls to protected resources. The example steps through the individual calls, but in reality it would be automated by the application. Remember to clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. Register a client with the grant type of "client_credentials". Setting the parameter to null means a client secret is generated automatically, as it the client ID. Associate the client with the role that holds the correct privileges for the resources it needs to access. In order to access the web service, we must first retrieve an access token using the and we queried from the view. The example below retrieves the access token. Notice the user format of "CLIENT_ID:CLIENT_SECRET". It is easy to miss the ":" when you look at this for the first time. We can now use the access token to call our web service. Notice the "Authorization: Bearer {access-token}" entry in the header of the call. We successfully accessed the protected web service.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
conn testuser1/testuser1@//localhost:1521/freepdb1

set serveroutput on
declare
  l_client_cred  ords_types.t_client_credentials;
begin
  l_client_cred := ords_security.register_client(
    p_name            => 'emp_client',
    p_grant_type      => 'client_credentials',
    p_description     => 'My Company Limited',
    p_client_secret   => null,
    p_support_email   => '[email protected]',
    p_privilege_names => 'emp_priv'
  );

  commit;
  dbms_output.put_line('CLIENT_ID : ' || l_client_cred.client_key.client_id);
  dbms_output.put_line('CLIENT_SECRET : ' || l_client_cred.client_secret.secret);
END;
/
CLIENT_ID : 0d6V9_BmuIbbYSftYEqSJQ..
CLIENT_SECRET : 07_3O7qs859x_1Vpkq0QOQ..


PL/SQL procedure successfully completed.

SQL>


-- Display client details.
column name format a20
column client_secret format a20

select id, name, client_id, client_secret
from   user_ords_clients;

        ID NAME                 CLIENT_ID                        CLIENT_SECRET
---------- -------------------- -------------------------------- --------------------
     10186 emp_client           0d6V9_BmuIbbYSftYEqSJQ..         ********

SQL>


-- Display client-privilege relationship.
SELECT name, client_name
FROM   user_ords_client_privileges;

NAME                 CLIENT_NAME
-------------------- ------------------------------
emp_priv             emp_client

SQL>

begin
  ords_security.grant_client_role(
    p_client_name => 'emp_client',
    p_role_name   => 'emp_role'
  );

  commit;
end;
/


-- Display client-role relationship.
column client_name format a30
column role_name format a20

select client_name, role_name
from   user_ords_client_roles;

CLIENT_NAME                    ROLE_NAME
------------------------------ --------------------
emp_client                     emp_role

SQL>

CLIENT_ID     : 0d6V9_BmuIbbYSftYEqSJQ..
CLIENT_SECRET : 07_3O7qs859x_1Vpkq0QOQ..
OAUTH URL     : https://localhost:8443/ords/hr/oauth/token

$
curl -i -k --user 0d6V9_BmuIbbYSftYEqSJQ..:07_3O7qs859x_1Vpkq0QOQ.. --data "grant_type=client_credentials" https://localhost:8443/ords/hr/oauth/token
HTTP/1.1 200
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Frame-Options: SAMEORIGIN
X-Content-Type-Options: nosniff
Cache-Control: no-cache, no-store, max-age=0
X-Frame-Options: SAMEORIGIN
vary: accept-encoding
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 20 Jun 2025 15:28:40 GMT

{"access_token":"KrIxK1IOnNH4QDqeGTzChw","token_type":"bearer","expires_in":3600}
$

$
curl -i -k -H"Authorization: Bearer KrIxK1IOnNH4QDqeGTzChw" https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Frame-Options: SAMEORIGIN
X-Content-Type-Options: nosniff
ETag: "bK1/oAjORtTe2J2PZwghyQT5Czyzr/eE8brkgPwsGIVlPc63QI+dqzTA7wGVOc3/Sw26RJYRbRtltQRHOQs1CQ=="
Cache-Control: max-age=0
Expires: Fri, 20 Jun 2025 15:29:33 GMT
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 20 Jun 2025 15:29:33 GMT

{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-19T00:00:00Z","sal":3000,
"comm":null,"deptno":20,"links":[{"rel":"self","href":"https://localhost:8443/ords/hr/employees/7788"},
{"rel":"edit","href":"https://localhost:8443/ords/hr/employees/7788"},
{"rel":"describedby","href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"},
{"rel":"collection","href":"https://localhost:8443/ords/hr/employees/"}]}
$
5

OAuth : Authorization Code

The authorization code flow is a three-legged process. The user accesses a URL in a browser, which prompts for credentials. Once authorized, the browser is redirected to a specified page with an authorization code as one of the parameters in the URL. That authorization code is used in a call to generate an access token, which is used to authorize calls to protected resources. With the exception of the user confirmation, all the other steps in the flow should be handled by the application. All the steps will be presented separately in the example that follows. This flow sounds complicated, but the important point to remember is the calling application never sees the user credentials. ORDS handles the user login and sends an authorization code back to the application, so it can continue with the authorization process. Remember to clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. The first-party authentication must be working for this flow to work. Create a client using the grant type of "authorization_code". Notice we have set a client secret manually, rather than generating one this time. The redirect and support URLs are not real, but we will be able to follow the example through anyway. We then attempt to request an authorization code. Notice we are using the from the view along with a unique string that will represent the state. Access the following URL from a browser. You are presented with a 401 message, which includes a "sign in" link. Click the link, sign in with the ORDS credentials you created earlier (emp_user) and you will be directed to an approval page. Click the "Approve" button, which will take you to the redirect page you specified for the client. The redirect page we specified for the client doesn't really exist, but we can get the authorization code and state from the URL. The application should check the state string matches the one used in the initial call. We use the authorization code to retrieve the access token. The following call retrieves the access token. We can now access the protected resource using the access token. As mentioned before, this looks complicated, but it allows a calling application to authenticate to a web service without seeing the user credentials. The application just has to know the and that were registered for it, and go through the user approval process to get the authorisation code.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
conn testuser1/testuser1@//localhost:1521/freepdb1

set serveroutput on
declare
  l_client_cred  ords_types.t_client_credentials;
begin
  l_client_cred := ords_security.register_client(
    p_name            => 'emp_client',
    p_grant_type      => 'authorization_code',
    p_description     => 'My Company Limited',
    p_client_secret   => ords_types.oauth_client_secret(p_secret => '07_3O7qs859x_1Vpkq0QOQ..'),
    p_redirect_uri    => 'https://localhost:8443/ords/hr/redirect',
    p_support_email   => '[email protected]',
    p_support_uri     => 'https://localhost:8443/ords/hr/support',
    p_privilege_names => 'emp_priv'
  );

  commit;

  dbms_output.put_line('CLIENT_ID : ' || l_client_cred.client_key.client_id);
  dbms_output.put_line('CLIENT_SECRET : ' || l_client_cred.client_secret.secret);
END;
/
CLIENT_ID : YJVRN1E6OZd_13zKg6-wnw..
CLIENT_SECRET : 07_3O7qs859x_1Vpkq0QOQ..


PL/SQL procedure successfully completed.

SQL>


-- Display client details.
column name format a20

select id, name, client_id, client_secret
from   user_ords_clients;

        ID NAME                 CLIENT_ID                        CLIENT_SECRET
---------- -------------------- -------------------------------- --------------------
     10204 emp_client           YJVRN1E6OZd_13zKg6-wnw..         ********

SQL>

CLIENT_ID     : YJVRN1E6OZd_13zKg6-wnw..
State         : 3668D7A713E93372E0406A38A8C02171
URL           : https://localhost:8443/ords/hr/oauth/auth?response_type=code&client_id={client_id}&state={state}

https://localhost:8443/ords/hr/oauth/auth?response_type=code&client_id=
YJVRN1E6OZd_13zKg6-wnw..
&state=
3668D7A713E93372E0406A38A8C02171

https://localhost:8443/ords/hr/redirect?code=
5oxUYSRh321gYD6ruevoNQ
&state=
3668D7A713E93372E0406A38A8C02171

CLIENT_ID     : YJVRN1E6OZd_13zKg6-wnw..
CLIENT_SECRET : 07_3O7qs859x_1Vpkq0QOQ..
User          : CLIENT_ID:CLIENT_SECRET
Data          : grant_type=authorization_code&code={authorization-code}
URL           : https://localhost:8443/ords/hr/oauth/token

$
curl -i -k --user YJVRN1E6OZd_13zKg6-wnw..:07_3O7qs859x_1Vpkq0QOQ.. --data "grant_type=authorization_code&code=5oxUYSRh321gYD6ruevoNQ" https://localhost:8443/ords/hr/oauth/token
HTTP/1.1 200
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Frame-Options: SAMEORIGIN
X-Content-Type-Options: nosniff
Cache-Control: no-cache, no-store, max-age=0
X-Frame-Options: SAMEORIGIN
vary: accept-encoding
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 20 Jun 2025 16:31:26 GMT

{"access_token":"n93iAmMuOVALoYuM29cvJQ","token_type":"bearer","expires_in":3600,"refresh_token":"vO07ZsWoFRmN540ZNAbSKA"}
$

$
curl -i -k -H"Authorization: Bearer n93iAmMuOVALoYuM29cvJQ" https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Frame-Options: SAMEORIGIN
X-Content-Type-Options: nosniff
ETag: "bK1/oAjORtTe2J2PZwghyQT5Czyzr/eE8brkgPwsGIVlPc63QI+dqzTA7wGVOc3/Sw26RJYRbRtltQRHOQs1CQ=="
Cache-Control: max-age=0
Expires: Fri, 20 Jun 2025 16:33:58 GMT
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 20 Jun 2025 16:33:58 GMT

{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-19T00:00:00Z","sal":3000,
"comm":null,"deptno":20,"links":[{"rel":"self","href":"https://localhost:8443/ords/hr/employees/7788"},
{"rel":"edit","href":"https://localhost:8443/ords/hr/employees/7788"},
{"rel":"describedby","href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"},
{"rel":"collection","href":"https://localhost:8443/ords/hr/employees/"}]}
$
6

OAuth : Implicit

The implicit flow is a two-legged process that requires user interaction. The user accesses a URL in a browser, which prompts for credentials. Once authorized, the browser is redirected to a specified page with an access token as one of the parameters in the URL. That access token is used to authorize calls to protected resources. The example steps through the individual calls, but in reality everything but the user interaction would be automated by the application. Remember to clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. The first-party authentication must be working for this flow to work. Create a client using the grant type of "implicit". The redirect and support URLs are not real, but we will be able to follow the example through anyway. We then attempt to request an access token. Notice we are using the from the view along with a unique string that will represent the state. Access the following URL from a browser. You are presented with a 401 message, which includes a "sign in" link. Click the link, sign in with the ORDS credentials you created earlier (emp_user) and you will be directed to an approval page. Click the "Approve" button, which will take you to the redirect page you specified for the client. The redirect page we specified for the client doesn't really exist, but we can get the access token from the URL. The application should check the state string matches the one used in the initial call. We can now access the protected resource using the access token.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
conn testuser1/testuser1@//localhost:1521/freepdb1

set serveroutput on
declare
  l_client_cred  ords_types.t_client_credentials;
begin
  l_client_cred := ords_security.register_client(
    p_name            => 'emp_client',
    p_grant_type      => 'implicit',
    p_description     => 'My Company Limited',
    p_redirect_uri    => 'https://localhost:8443/ords/hr/redirect',
    p_support_email   => '[email protected]',
    p_support_uri     => 'https://localhost:8443/ords/hr/support',
    p_privilege_names => 'emp_priv'
  );

  commit;
  dbms_output.put_line('CLIENT_ID : ' || l_client_cred.client_key.client_id);
  dbms_output.put_line('CLIENT_SECRET : ' || l_client_cred.client_secret.secret);
END;
/
CLIENT_ID : QAjfma85sN6ECVGtzB5u5g..
CLIENT_SECRET :


PL/SQL procedure successfully completed.

SQL>


-- Display client details.
column name format a20

select id, name, client_id, client_secret
from   user_ords_clients;

        ID NAME                 CLIENT_ID                        CLIENT_SECRET
---------- -------------------- -------------------------------- --------------------
     10223 emp_client           QAjfma85sN6ECVGtzB5u5g..

SQL>

CLIENT_ID     : QAjfma85sN6ECVGtzB5u5g..
State         : 3668D7A713E93372E0406A38A8C02171
URL           : https://localhost:8443/ords/hr/oauth/auth?response_type=code&client_id={client_id}&state={random-string}

https://localhost:8443/ords/hr/oauth/auth?response_type=token&client_id=
QAjfma85sN6ECVGtzB5u5g..
&state=
3668D7A713E93372E0406A38A8C02171

https://localhost:8443/ords/hr/redirect#token_type=bearer&access_token=
IeHZVqRWCOmFwqE6MDOMjg
&expires_in=3600&state=3668D7A713E93372E0406A38A8C02171

$
curl -i -k -H"Authorization: Bearer IeHZVqRWCOmFwqE6MDOMjg" https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Frame-Options: SAMEORIGIN
X-Content-Type-Options: nosniff
ETag: "bK1/oAjORtTe2J2PZwghyQT5Czyzr/eE8brkgPwsGIVlPc63QI+dqzTA7wGVOc3/Sw26RJYRbRtltQRHOQs1CQ=="
Cache-Control: max-age=0
Expires: Fri, 20 Jun 2025 17:03:28 GMT
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 20 Jun 2025 17:03:28 GMT

{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-19T00:00:00Z","sal":3000,
"comm":null,"deptno":20,"links":[{"rel":"self","href":"https://localhost:8443/ords/hr/employees/7788"},
{"rel":"edit","href":"https://localhost:8443/ords/hr/employees/7788"},
{"rel":"describedby","href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"},
{"rel":"collection","href":"https://localhost:8443/ords/hr/employees/"}]}
$
7

Import Client

In the previous examples we've used to create a new client. If we want to recreate a client using an existing client ID we can use instead. Rather than repeat all the previous examples we will just show a single example using client credentials. Notice both the client ID and secret are set manually. Let's remove that. We could have rotated the secret to create a new one. See below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
conn testuser1/testuser1@//localhost:1521/freepdb1

set serveroutput on
declare
  l_client_cred ords_types.t_client_credentials;
begin
  ords_security.import_client(
    p_name             => 'emp_client2',
    p_client_id        => 'YJVRN1E6OZd_13zKg6-wnw..',
    p_grant_type       => 'client_credentials',
    p_description      => 'My Company Limited',
    p_support_email    => '[email protected]',
    p_privilege_names  => 'emp_priv'
  );

  commit;

  l_client_cred.client_key.name      := 'emp_client2';
  l_client_cred.client_secret.secret := '07_3O7qs859x_1Vpkq0QOQ..';
   
  l_client_cred := ords_security.register_client_secret(
    p_client_key    => l_client_cred.client_key,
    p_client_secret => l_client_cred.client_secret
  );

  dbms_output.put_line('CLIENT_ID : ' || l_client_cred.client_key.client_id);
  dbms_output.put_line('CLIENT_SECRET : ' || l_client_cred.client_secret.secret);
END;
/
CLIENT_ID : YJVRN1E6OZd_13zKg6-wnw..
CLIENT_SECRET : 07_3O7qs859x_1Vpkq0QOQ..


PL/SQL procedure successfully completed.

SQL>

begin
  ords_security.delete_client(
    p_name => 'emp_client2'
  );

  commit;
end;
/
8

Rotate Client Secret

The function allows us to create a new client secret, and optionally revoke the previous secrets. This could be done on a regular basis as part of a security policy, or in an emergency if we believe a secret has been compromised. Remember to clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. Register a client with the grant type of "client_credentials". We rotate the secret. Notice the new secret that is generated.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
conn testuser1/testuser1@//localhost:1521/freepdb1

set serveroutput on
declare
  l_client_cred  ords_types.t_client_credentials;
begin
  l_client_cred := ords_security.register_client(
      p_name            => 'emp_client',
      p_grant_type      => 'client_credentials',
      p_description     => 'My Company Limited',
      p_client_secret   => null,
      p_support_email   => '[email protected]',
      p_privilege_names => 'emp_priv'
  );

  commit;
  dbms_output.put_line('CLIENT_ID : ' || l_client_cred.client_key.client_id);
  dbms_output.put_line('CLIENT_SECRET : ' || l_client_cred.client_secret.secret);
END;
/
CLIENT_ID : 6r41hX9MEhG_umDMT70o4A..
CLIENT_SECRET : EFqAV4zYSkGNrIe64yQ4lw..


PL/SQL procedure successfully completed.

SQL>

declare
  l_client_cred  ords_types.t_client_credentials;
begin
  l_client_cred.client_key.name := 'emp_client';
   
  l_client_cred := ords_security.rotate_client_secret(
    p_client_key      => l_client_cred.client_key,
    p_revoke_existing => true,
    p_revoke_sessions => true);

  dbms_output.put_line('SLOT:'      || l_client_cred.client_secret.slot);
  dbms_output.put_line('SECRET:'    || l_client_cred.client_secret.secret);
  dbms_output.put_line('ISSUED ON:' || l_client_cred.client_secret.issued_on);
END;
/
SLOT:2
SECRET:3PgDGUzpa9P4ackz5Ncbsg..
ISSUED ON:20-JUN-25 18.21.21.939676


PL/SQL procedure successfully completed.

SQL>
9

Deleting OAuth Metadata

We remove the OAuth metadata for the client role mapping and the client using the following code. They could be combined as a single block, but I've left them separate so they can be run individually.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
begin
  ords_security.revoke_client_role(
    p_client_name => 'emp_client',
    p_role_name   => 'emp_role'
  );

  commit;
end;
/


begin
  ords_security.delete_client(
    p_name => 'emp_client'
  );

  commit;
end;
/
10

Deleting Roles and Privileges Metadata

This is unchanged from the previous article, but it is included here to save you having to switch back if we want to remove the authentication of the service. The ORDS URL mapping, privilege and roles are deleted using the following code. Once this is removed, the web service will be publicly available again. For more information see: - ORDS_SECURITY PL/SQL Package Reference - ORDS_SECURITY_ADMIN PL/SQL Package Reference - Oracle REST Data Services (ORDS) : Authentication - Oracle REST Data Services (ORDS) : Pre-Authenticated Requests - Oracle REST Data Services (ORDS) : All Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
begin
  ords.delete_privilege_mapping(
    p_privilege_name => 'emp_priv',
    p_pattern => '/employees/*'
  );     

  commit;
end;
/

begin
  ords.delete_privilege (
    p_name => 'emp_priv'
  );
   
  commit;
end;
/

begin
  ords.delete_role(
    p_role_name => 'emp_role'
  );
  
  commit;
end;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!