Getting access to data, and logic, in your Oracle database is made so very easy with Oracle REST Data Services (ORDS). With a few clicks you can REST-enable a table so that clients can query, insert, update and delete records in it. That’s all via REST requests and all without writing any custom SQL. Moreover, these REST services can be secured so that only users with the required role has access.
What if you wanted to provide open access to a subset of data without the overhead of authentication and role authorisation configuration? Consider an internal employee directory for example where employees, or other systems, in the organisation’s intranet can search for employees. Some Oracle customers have achieved this by creating a read only view and ORDS enabling that. There is another approach that does not involve ORDS enabling the schema where the data resides or creating views. Let’s explore that…
Context
In the scenarios outlined below there is database called ORCLPDB1 with a HR schema. ORDS is running on localhost:8080 and the URL for accessing the database is http://localhost:8080/ords/orclpdb1/
The example command line requests shown below uses HTTPie. You may be more familiar with curl, but HTTPie is worth exploring if you script REST calls because there’s less typing involved!
The HR application uses HR schema for managing employee details, contact information and sensitive information including salaries and bonuses. The EMPLOYEES table in the HR schema is the ultimate source of truth about the people employed in the organisation. It is only employee_id, first_name, last_name, email, phone_number, and manager_id that should be made available through a REST interface.
Which schema?
An ORDS Enabled schema is required because it will be with that user that queries on the HR.EMPLOYEES table will be performed in the database. One can ORDS Enable the HR schema or create a new schema which only exists to provide a REST interface and ORDS enable that.
In these examples we’re going to create a new schema called HR_REST and only give it the minimum of privileges required. You can call the schema whatever suits the nomenclature in your organisation. Some may consider HR_READ_ONLY or the shorter HR_RO names in this context.
Create schema
With a DBA or some other suitable privileged user, connect to the database and run the following:
CREATE USER HR_REST IDENTIFIED BY <password>;
GRANT CONNECT TO HR_REST ;
GRANT SELECT ON HR.EMPLOYEES TO HR_REST;
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(p_schema => 'HR_REST');
END;
You will notice that the HR_REST can only CONNECT and SELECT so it is restricted at the database level as to what it can do.
Define Service
This is the point where we define a REST endpoint to retrieve just the columns required by taking advantage of the DEFINE_SERVICE procedure. Connect to the database as HR_REST user and run the following:
BEGIN
ORDS.DEFINE_SERVICE(
p_module_name => 'hr.read_only.employees',
p_base_path => '/employees/',
p_pattern => '.',
p_source => 'select employee_id, first_name, last_name, email, phone_number, manager_id from hr.employees'
);
COMMIT;
END;
The employee list can now be accessed at http://localhost:8080/ords/orclpdb1/hr_rest/employees/ using GET.
http http://localhost:8080/ords/orclpdb1/hr_rest/employees/
HTTP/1.1 200 OK
Content-Type: application/json
Date: Fri, 22 Mar 2019 18:14:04 GMT
ETag: "CfvOiafv+Tw6EyXoXwWPS8ZT8w0wkv0gHAy297hSqILBnV6OdzJ8MlTgngdhPuPv9p9MfsLCqSkyqmXiHedNlA=="
Transfer-Encoding: chunked
{
"count": 25,
"hasMore": true,
"items": [
{
"email": "DOCONNEL",
"employee_id": 198,
"first_name": "Donald",
"last_name": "OConnell",
"manager_id": 124,
"phone_number": "650.507.9833"
},
...items removed for brevity...
{
"email": "DGRANT",
"employee_id": 199,
"first_name": "Douglas",
"last_name": "Grant",
"manager_id": 124,
"phone_number": "650.507.9844"
},
{
"email": "JWHALEN",
"employee_id": 200,
"first_name": "Jennifer",
"last_name": "Whalen",
"manager_id": 101,
"phone_number": "515.123.4444"
},
{
"email": "MHARTSTE",
"employee_id": 201,
"first_name": "Michael",
"last_name": "Hartstein",
"manager_id": 100,
"phone_number": "515.123.5555"
},
{
"email": "PFAY",
"employee_id": 202,
"first_name": "Pat",
"last_name": "Fay",
"manager_id": 201,
"phone_number": "603.123.6666"
}
],
"limit": 25,
"links": [
{
"href": "http://localhost:8080/ords/orclpdb1/hr_rest/employees/",
"rel": "self"
},
{
"href": "http://localhost:8080/ords/orclpdb1/hr_rest/metadata-catalog/employees/",
"rel": "describedby"
},
{
"href": "http://localhost:8080/ords/orclpdb1/hr_rest/employees/",
"rel": "first"
},
{
"href": "http://localhost:8080/ords/orclpdb1/hr_rest/employees/?offset=25",
"rel": "next"
}
],
"offset": 0
}
Note the link to the metadata-catalog for the service. The one downside of this approach is that there’s no metadata about the structure of the response. If one had ORDS enabled a view, the metadata-catalog would at least describe the properties for each column.
However, the downside of ORDS enabling a view is that anything other than a GET will result in a HTTP 500 error response which is not ideal. The advantage of this DEFINE_SERVICE approach is that other HTTP methods will result in a very clear HTTP 405 Method Not Allowed response. For example:
http -v POST http://localhost:8080/ords/orclpdb1/hr_rest/employees/ first_name=Peter
HTTP/1.1 405 Method Not Allowed
Allow: GET
Content-Length: 15798
Content-Type: text/html
Similar to when a view is ORDS enabled, the ORDS pagination and query filter object query parameters can be used to find specific employees.
http http://localhost:8080/ords/orclpdb1/hr_rest/employees/ q=='{"first_name":"Alana"}'
HTTP/1.1 200 OK
Content-Type: application/json
ETag: "YNh9iZn3p5zFLO1zjlpaPtXBcdAGR3QMAatiPatERoTt5gievnW7Bz7qYFLNVP19H83pfE7rRRZiy0kRc5wXfg=="
Transfer-Encoding: chunked
{
"count": 1,
"hasMore": false,
"items": [
{
"email": "AWALSH",
"employee_id": 196,
"first_name": "Alana",
"last_name": "Walsh",
"manager_id": 124,
"phone_number": "650.507.9811"
}
],
"limit": 25,
"links": [
{
"href": "http://localhost:8080/ords/orclpdb1/hr_rest/employees/?q=%7B%22first_name%22:%22Alana%22%7D",
"rel": "self"
},
{
"href": "http://localhost:8080/ords/orclpdb1/hr_rest/metadata-catalog/employees/",
"rel": "describedby"
},
{
"href": "http://localhost:8080/ords/orclpdb1/hr_rest/employees/?q=%7B%22first_name%22:%22Alana%22%7D",
"rel": "first"
}
],
"offset": 0
}
Conclusion
By introducing another database schema, with restricted access just to the table required the level of exposure through the REST interface is reduced. While this could be achieved with a read only view, the response to clients that attempt to POST, PUT or DELETE is a HTTP 500 so it is much cleaner, and more RESTful one could argue, to use DEFINE_SERVICE in this case.
Peter's Random Topic Library 