An ORDS endpoint is public until something protects it. ORDS protects resources with privileges: a privilege covers URL patterns or modules, and lists the roles a caller needs. Users and OAuth clients get roles, and requests without one are refused with 401. This guide protects an employee API that returns salaries.
Before You Start
You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.
ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.
Syntax
ords.create_role(p_role_name => 'role');
ords.create_privilege(p_name => 'privilege', p_role_name => 'role',
p_label => '...', p_description => '...');
ords.create_privilege_mapping(p_privilege_name => 'privilege',
p_pattern => '/base/*'); -- or SET_MODULE_PRIVILEGEPatterns are relative to the schema alias, so /hr/* covers every URL under /ords/nimbus/hr/.
An Open Employee API
ORDS.DEFINE_SERVICE creates the hr module, its employees template, and a GET handler in one call.
Example:
begin
ords.define_service(p_module_name => 'hr',
p_base_path => '/hr/',
p_pattern => 'employees',
p_method => 'GET',
p_source_type => ords.source_type_collection_feed,
p_source => 'select employee_id, first_name, last_name, salary
from employees
order by employee_id');
commit;
end;
/Right now anyone can read names and salaries:
Example:
curl -k "https://localhost:8443/ords/nimbus/hr/employees?limit=2"
Output (start of the response):
{
"items": [
{
"employee_id": 100,
"first_name": "Layla",
"last_name": "Haddad",
"salary": 48000
},
{
"employee_id": 101,
"first_name": "Omar",
"last_name": "Al Mansoori",
"salary": 36000
}
...Protect It with a Privilege and a Role
Example:
begin
ords.create_role(p_role_name => 'hr.reader');
ords.create_privilege(p_name => 'hr.read',
p_role_name => 'hr.reader',
p_label => 'Read employee data',
p_description => 'Employee names and salaries');
ords.create_privilege_mapping(p_privilege_name => 'hr.read',
p_pattern => '/hr/*');
commit;
end;
/
select p.name as privilege, p.label, r.name as role
from user_ords_privileges p
join user_ords_privilege_roles pr on pr.privilege_id = p.id
join user_ords_roles r on r.id = pr.role_id
where p.name = 'hr.read';
select name, pattern from user_ords_privilege_mappings where name = 'hr.read';Output:
PL/SQL procedure successfully completed. PRIVILEGE LABEL ROLE ____________ _____________________ ____________ hr.read Read employee data hr.reader NAME PATTERN __________ __________ hr.read /hr/*
After a few seconds, while ORDS refreshes its cached metadata, the same request is refused:
Output (HTTP 401 Unauthorized):
{
"code": "Unauthorized",
"message": "Unauthorized",
"type": "tag:oracle.com,2020:error/Unauthorized",
"instance": "tag:oracle.com,2020:ecid/yuF-wbraXARIMRrPX1Hl6w"
}Who Can Get In
A caller passes when it is authenticated with the role hr.reader: an OAuth client that has been granted the role, or a user known to ORDS with that role. The OAuth client credentials flow is the usual choice for system-to-system calls.
Things to Know
- A privilege covers every HTTP method on its patterns; put read and write endpoints in different modules or patterns when they need different roles.
- ORDS.SET_MODULE_PRIVILEGE protects a whole module by name instead of by pattern.
- USER_ORDS_PRIVILEGES, USER_ORDS_ROLES, and USER_ORDS_PRIVILEGE_MAPPINGS show what protects what.
Related Guides
Conclusion
ORDS privileges protect URL patterns or modules and name the roles a caller needs. Create a role and a privilege, map the privilege to the URLs, and every request without that role gets 401 Unauthorized.
