DBA Hub

📋Steps in this guide1/4

Oracle REST Data Services (ORDS) : Database Authentication

Use database authentication to provide basic authentication for your calls to PL/SQL from Oracle REST Data Services (ORDS).

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Enable Database Authentication

Database authentication is not enabled by default. To enable it as part of the ORDS installation process add the following line to the "/u01/ords/params/ords_params.properties" file. For an existing ORDS installation, issue the following command. If it is Alternatively, manually edit the "/u01/ords/conf/ords/defaults.xml" file, or one of the pool-specific config files, adding the following entry. The PL/SQL gateway can use a validation function to enforce an allow-list of procedures that can be called from the gateway. If you've configured the gateway to front APEX, you will have the APEX validation function in the "/u01/ords/conf/ords/defaults.xml" file. You either have to use a modified validation function, which is a good idea, or remove this entry. You can reset this parameter value using the following command. For the this example we will assign a blank value, which is functionally the same as removing it from the file. You will need to restart ORDS for this to take effect. The method will vary depending on if you are running ORDS under Tomcat, WebLogic or in standalone mode. For a Tomcat installation you might do this.

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
jdbc.auth.enabled=true

cd /u01/ords

# Default for all pools.
$JAVA_HOME/bin/java -jar ords.war set-property jdbc.auth.enabled true

# Specific pool.
$JAVA_HOME/bin/java -jar ords.war set-property
--conf pdb1
jdbc.auth.enabled true

<entry key="jdbc.auth.enabled">true</entry>

<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>

$JAVA_HOME/bin/java -jar ords.war set-property security.requestValidationFunction ""

$CATALINA_HOME/bin/shutdown.sh
$CATALINA_HOME/bin/startup.sh
2

Application Setup

Create a user that owns an API we want to expose. Create users for the database authentication, which have execute permissions on the API. We've also created a synonym to the API, so we don't have to include the user in the URL.

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
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

CREATE USER api_owner IDENTIFIED BY api_owner ACCOUNT LOCK;


CREATE OR REPLACE PACKAGE api_owner.user_api AS
  PROCEDURE display_user;
END user_api;
/


CREATE OR REPLACE PACKAGE BODY api_owner.user_api AS

  PROCEDURE display_user AS
   l_remote_user  VARCHAR2(256);
  BEGIN
    l_remote_user := OWA_UTIL.get_cgi_env('REMOTE_USER');
  
    OWA_UTIL.mime_header('application/json');
    HTP.prn('{"remote-user":"' || l_remote_user || '"}' || CHR(10));
  END display_user;

END user_api;
/

CREATE USER my_api_login_1 IDENTIFIED BY my_password1;
GRANT CREATE SESSION TO my_api_login_1;
GRANT EXECUTE ON api_owner.user_api TO my_api_login_1;

CREATE SYNONYM my_api_login_1.user_api FOR api_owner.user_api;


CREATE USER my_api_login_2 IDENTIFIED BY my_password2;
GRANT CREATE SESSION TO my_api_login_2;
GRANT EXECUTE ON api_owner.user_api TO my_api_login_2;

CREATE SYNONYM my_api_login_2.user_api FOR api_owner.user_api;
3

Test It

The following two commands call the stored procedure using HTTP and HTTPS respectively. The "-u" or "--user" option allows you to pass a the database credentials. Although it's possible to use HTTP, you should never pass plain text passwords over HTTP. We are using a self-signed certificate for the HTTPS examples, so the "-k" options prevent curl from complaining about a weak certificate.

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
$ curl -s
-u "my_api_login_1:my_password1"
http://localhost:8080/ords/user_api.display_user
{"remote-user":"MY_API_LOGIN_1"}
$ curl -s
-u "my_api_login_2:my_password2"
http://localhost:8080/ords/user_api.display_user
{"remote-user":"MY_API_LOGIN_2"}
$

$ curl -ks
-u "my_api_login_1:my_password1"
https://localhost:8443/ords/user_api.display_user
{"remote-user":"MY_API_LOGIN_1"}
$ curl -ks
-u "my_api_login_2:my_password2"
https://localhost:8443/ords/user_api.display_user
{"remote-user":"MY_API_LOGIN_2"}
$
4

Accessing Services Using the Schema Alias

If you want to access services using a schema alias, you can only use the schema alias associated with login user credentials. Connect to each login user and REST enable the schema, setting a unique schema alias for each. We can now access the services using the schema alias that matches the login details. The following will fail, as the schema alias doesn't match the login credentials. For more information see: - Oracle REST Data Services Database Authentication - Oracle REST Data Services (ORDS) : Database Authentication - Oracle REST Data Services (ORDS) : All Articles - Oracle REST Data Services (ORDS) : Authentication - Oracle REST Data Services (ORDS) : Presenting PL/SQL Web Toolkit Applications - Oracle REST Data Services (ORDS) : File Upload Download (mod_plsql Style) - Database Authentication and REST Data Services/a> 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
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
CONN my_api_login_1/my_password1@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'MY_API_LOGIN_1',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'customer1',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

CONN my_api_login_2/my_password2@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'MY_API_LOGIN_2',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'customer2',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

$ curl -ks -u "
my_api_login_1:my_password1
" https://localhost:8443/ords/
customer1
/user_api.display_user
{"remote-user":"MY_API_LOGIN_1"}
$ curl -ks -u "
my_api_login_2:my_password2
" https://localhost:8443/ords/
customer2
/user_api.display_user
{"remote-user":"MY_API_LOGIN_2"}
$

$ curl -iks -u "
my_api_login_1
:my_password1" https://localhost:8443/ords/
customer2
/user_api.display_user | grep HTTP
HTTP/1.1 404
$ curl -iks -u "
my_api_login_2
:my_password2" https://localhost:8443/ords/
customer1
/user_api.display_user | grep HTTP
HTTP/1.1 404
$

Comments (0)

Please to add comments

No comments yet. Be the first to comment!