DBA Hub

📋Steps in this guide1/2

Oracle REST Data Services (ORDS) : Pre-Authenticated Requests

This article demonstrates pre-authenticated requests using the ORDS_PAR package.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

Setup

In order to run the examples in this article we need to do the following setup. We create a new test user. We create an object in the test user. We enable ORDS for our test user and create a service that queries the test table. We can access the service without authentication as demonstrated below. We protect the end point using a role and privilege. We can no longer access the service without authentication.

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
117
118
119
120
121
122
123
124
125
126
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

--drop user testuser1 cascade;

create user testuser1 identified by testuser1
  default tablespace users quota unlimited on users;
  
grant create session, create table, create type to testuser1;

conn testuser1/testuser1@//localhost:1521/freepdb1

create table emp (
  empno    number(4,0), 
  ename    varchar2(10 byte), 
  job      varchar2(9 byte), 
  mgr      number(4,0), 
  hiredate date, 
  sal      number(7,2), 
  comm     number(7,2), 
  deptno   number(2,0), 
  constraint pk_emp primary key (empno)
);
  
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

begin
  ords.enable_schema(
    p_enabled             => true,
    p_schema              => 'TESTUSER1',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'hr',
    p_auto_rest_auth      => false
  );
    
  commit;
end;
/

begin
  ords.define_service(
    p_module_name    => 'rest-v1',
    p_base_path      => 'rest-v1/',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ords.source_type_collection_feed,
    p_source         => 'select * from emp',
    p_items_per_page => 0);

  commit;
end;
/

$ curl -k https://localhost:8443/ords/hr/rest-v1/employees/
{"items":[
{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"1980-12-17T00:00:00Z","sal":800,"comm":null,"deptno":20},
{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00Z","sal":1600,"comm":300,"deptno":30},
{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-22T00:00:00Z","sal":1250,"comm":500,"deptno":30},
{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"1981-04-02T00:00:00Z","sal":2975,"comm":null,"deptno":20},
{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-28T00:00:00Z","sal":1250,"comm":1400,"deptno":30},
{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"1981-05-01T00:00:00Z","sal":2850,"comm":null,"deptno":30},
{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-09T00:00:00Z","sal":2450,"comm":null,"deptno":10},
{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-19T00:00:00Z","sal":3000,"comm":null,"deptno":20},
{"empno":7839,"ename":"KING","job":"PRESIDENT","mgr":null,"hiredate":"1981-11-17T00:00:00Z","sal":5000,"comm":null,"deptno":10},
{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-08T00:00:00Z","sal":1500,"comm":0,"deptno":30},
{"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"1987-05-23T00:00:00Z","sal":1100,"comm":null,"deptno":20},
{"empno":7900,"ename":"JAMES","job":"CLERK","mgr":7698,"hiredate":"1981-12-03T00:00:00Z","sal":950,"comm":null,"deptno":30},
{"empno":7902,"ename":"FORD","job":"ANALYST","mgr":7566,"hiredate":"1981-12-03T00:00:00Z","sal":3000,"comm":null,"deptno":20},
{"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-23T00:00:00Z","sal":1300,"comm":null,"deptno":10}
],"hasMore":false,"limit":0,"offset":0,"count":14,"links":[
{"rel":"self","href":"https://localhost:8443/ords/hr/rest-v1/employees/"},
{"rel":"describedby","href":"https://localhost:8443/ords/hr/metadata-catalog/rest-v1/employees/"}
]}
$

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

declare
  l_roles_arr    owa.vc_arr;
  l_patterns_arr owa.vc_arr;
  l_modules_arr  owa.vc_arr;
begin
  l_roles_arr(1)    := 'emp_role';
  l_modules_arr(1)  := 'rest-v1';
  
  ords.define_privilege (
    p_privilege_name => 'emp_priv',
    p_roles          => l_roles_arr,
    p_patterns       => l_patterns_arr,
    p_modules        => l_modules_arr,
    p_label          => 'My Module',
    p_description    => 'Allow access to My Module.'
  );
   
  commit;
end;
/

$ curl -k https://localhost:8443/ords/hr/rest-v1/employees/
{
    "code": "Unauthorized",
    "message": "Unauthorized",
    "type": "tag:oracle.com,2020:error/Unauthorized",
    "instance": "tag:oracle.com,2020:ecid/_1OhR_eerPykNBlfyxhQPg"
}
$
2

Pre-Authenticated Requests

At the time of writing, some of the functionality in the package is not yet documented. We use the function in the package to define a pre-authentication token for the request. We identify the request by specifying the module, pattern and HTTP method. We also define a duration in seconds. Notice the output includes the token along with the modified URI including the token. We call the resource using the modified URI that includes the token, and we can get the data. We use the function, added in ORDS version 25.2, to determine the remaining duration for the pre-authenticated request. The function displays information about the pre-authenticated endpoint. This is currently not documented in version 25.2 If we need to revoke access to the resource prior to the pre-authentication timing out, we can do this using the or procedures. The documentation says it may take up to 30 seconds for the revoke to be noticed. Once revoked, the resource is no longer accessible using the token. For more information see: - Oracle REST Data Services Pre-Authenticated Requests - Oracle REST Data Services (ORDS) : Authentication - Oracle REST Data Services (ORDS) : Authentication Enhancements (ORDS_SECURITY and ORDS_SECURITY_ADMIN Packages) - 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
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
conn testuser1/testuser1@//localhost:1521/freepdb1

set serveroutput on long 1000000 linesize 1000
declare
  l_uri  clob;
begin
  l_uri := ords_par.define_for_handler (
    p_module_name => 'rest-v1',
    p_pattern => 'employees/',
    p_method => 'get',
    p_duration => 3600
  );
  commit;
  dbms_output.put_line(l_uri);
end;
/
{
 "token": "MKHi3NEFZCApMC9WL1wwKoBl4zqLgaSO8sftzkgufmwVzADj0pAR9UyKqiqKLdssaUCVABlXWRvgxndw6MG0JcVXIGyx43uXKkvAybreQw6KFM9DtDr4fMzvWIxzWQ",
 "alias" : "xOkytN8cUqFO1ZB0F7LQv4Ze5XcsDFeuCBByOjtZPaxBywtPJzqLtgL4P7QWwDf",
 "uri": "hr/_/par/MKHi3NEFZCApMC9WL1wwKoBl4zqLgaSO8sftzkgufmwVzADj0pAR9UyKqiqKLdssaUCVABlXWRvgxndw6MG0JcVXIGyx43uXKkvAybreQw6KFM9DtDr4fMzvWIxzWQ/rest-v1/employees/"
}

PL/SQL procedure successfully completed.

SQL>

$ curl -k https://localhost:8443/ords/hr/_/par/MKHi3NEFZCApMC9WL1wwKoBl4zqLgaSO8sftzkgufmwVzADj0pAR9UyKqiqKLdssaUCVABlXWRvgxndw6MG0JcVXIGyx43uXKkvAybreQw6KFM9DtDr4fMzvWIxzWQ/rest-v1/employees/
{"items":[
{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"1980-12-17T00:00:00Z","sal":800,"comm":null,"deptno":20},
{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00Z","sal":1600,"comm":300,"deptno":30},
{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-22T00:00:00Z","sal":1250,"comm":500,"deptno":30},
{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"1981-04-02T00:00:00Z","sal":2975,"comm":null,"deptno":20},
{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-28T00:00:00Z","sal":1250,"comm":1400,"deptno":30},
{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"1981-05-01T00:00:00Z","sal":2850,"comm":null,"deptno":30},
{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-09T00:00:00Z","sal":2450,"comm":null,"deptno":10},
{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-19T00:00:00Z","sal":3000,"comm":null,"deptno":20},
{"empno":7839,"ename":"KING","job":"PRESIDENT","mgr":null,"hiredate":"1981-11-17T00:00:00Z","sal":5000,"comm":null,"deptno":10},
{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-08T00:00:00Z","sal":1500,"comm":0,"deptno":30},
{"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"1987-05-23T00:00:00Z","sal":1100,"comm":null,"deptno":20},
{"empno":7900,"ename":"JAMES","job":"CLERK","mgr":7698,"hiredate":"1981-12-03T00:00:00Z","sal":950,"comm":null,"deptno":30},
{"empno":7902,"ename":"FORD","job":"ANALYST","mgr":7566,"hiredate":"1981-12-03T00:00:00Z","sal":3000,"comm":null,"deptno":20},
{"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-23T00:00:00Z","sal":1300,"comm":null,"deptno":10}
],"hasMore":false,"limit":0,"offset":0,"count":14,"links":[
{"rel":"self","href":"https://localhost:8443/ords/hr/rest-v1/employees/"},
{"rel":"describedby","href":"https://localhost:8443/ords/hr/metadata-catalog/rest-v1/employees/"}
]}
$

set serveroutput on
declare
  l_duration  number;
begin
  l_duration := ords_par.par_expiration('MKHi3NEFZCApMC9WL1wwKoBl4zqLgaSO8sftzkgufmwVzADj0pAR9UyKqiqKLdssaUCVABlXWRvgxndw6MG0JcVXIGyx43uXKkvAybreQw6KFM9DtDr4fMzvWIxzWQ');

  dbms_output.put_line('l_duration : ' || l_duration);
end;
/
l_duration : 3026.999999999999999999999999999999999998

PL/SQL procedure successfully completed.

SQL>

set serveroutput on
declare
  l_state  clob;
begin
  l_state := ords_par.state_from_par('MKHi3NEFZCApMC9WL1wwKoBl4zqLgaSO8sftzkgufmwVzADj0pAR9UyKqiqKLdssaUCVABlXWRvgxndw6MG0JcVXIGyx43uXKkvAybreQw6KFM9DtDr4fMzvWIxzWQ');

  dbms_output.put_line(l_state);
end;
/
[{"alias": "xOkytN8cUqFO1ZB0F7LQv4Ze5XcsDFeuCBByOjtZPaxBywtPJzqLtgL4P7QWwDf", "type": "HANDLER", "prefix": "/rest-v1/", "pattern": "employees/", "method": "GET" }]

PL/SQL procedure successfully completed.

SQL>

begin
  ords_par.revoke_par('MKHi3NEFZCApMC9WL1wwKoBl4zqLgaSO8sftzkgufmwVzADj0pAR9UyKqiqKLdssaUCVABlXWRvgxndw6MG0JcVXIGyx43uXKkvAybreQw6KFM9DtDr4fMzvWIxzWQ');
  commit;
end;
/

begin
  ords_par.revoke_par_by_alias('xOkytN8cUqFO1ZB0F7LQv4Ze5XcsDFeuCBByOjtZPaxBywtPJzqLtgL4P7QWwDf');
  commit;
end;
/

$curl -k https://localhost:8443/ords/hr/_/par/MKHi3NEFZCApMC9WL1wwKoBl4zqLgaSO8sftzkgufmwVzADj0pAR9UyKqiqKLdssaUCVABlXWRvgxndw6MG0JcVXIGyx43uXKkvAybreQw6KFM9DtDr4fMzvWIxzWQ/rest-v1/employees/
{
    "code": "Unauthorized",
    "message": "Unauthorized",
    "type": "tag:oracle.com,2020:error/Unauthorized",
    "instance": "tag:oracle.com,2020:ecid/epoy-zqj4pgFB-qmM3G7pg"
}
$

Comments (0)

Please to add comments

No comments yet. Be the first to comment!