Developing Oracle RESTful Services
This tutorial will guide you through the process of creating a simple RESTful Service to GET some data from a database. Then we will protect the created resource using ORDS roles.
More detailed description of developing and protecting Oracle RESTful Services can be found here:
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-create-basic-rest-web-services-using-plsql
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-autorest
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication
Defining RESTful Services using AutoREST
First let's create a simple table and insert some data for our test:
CREATE TABLE USERS (
USERID NUMBER(4,0),
NAME VARCHAR2(10 BYTE),
SURNAME VARCHAR2(10 BYTE),
CONSTRAINT PK_EMP PRIMARY KEY (USERID)
);
insert into USERS (USERID,NAME,SURNAME) values (1,'Smith','Liam');
insert into USERS (USERID,NAME,SURNAME) values (2,'Clerk','John');
insert into USERS (USERID,NAME,SURNAME) values (3,'Boguski','Marek');
insert into USERS (USERID,NAME,SURNAME) values (4,'Vardy','Michael');
commit;
First step is to enable REST web services for the schema. We can specify any url mapping pattern for the schema
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => 'MYSCHEMA',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'myschema',
p_auto_rest_auth => FALSE);
In the example above we map our schema to a certain url. Assuming that we run our test on the devdb11 database, the URL will be:
https://oraweb.cern.ch/ords/devdb11/myschema/
https://oraweb.cern.ch/ords/ - this part of the URL comes from the internal configuration of ORDS that is managed by IT-DB.
/devdb11 - depends on the database used.
The next step is to define a service for one of our tables (using
AutoREST) or map a specific URL to our statement (select, procedure...). In this example we use the
AutoREST feature to expose the whole table for READ, UPDATE, INSERT and DELETE.
ORDS.ENABLE_OBJECT(p_enabled => TRUE,
p_schema => 'myschema',
p_object => 'USERS',
p_object_type => 'TABLE',
p_object_alias => 'users',
p_auto_rest_auth => FALSE);
p_object_alias maps to the last part of the URL under which we can access our resource:
https://oraweb.cern.ch/ords/devdb11/myschema/users
Let's test our REST endpoint:
curl --basic https://oraweb.cern.ch/ords/devdb11/myschema/users/
{"items":[{"userid":1,"name":"Smith","surname":"Liam","links":[{"rel":"self","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/1"}]},{"userid":2,"name":"Clerk","surname":"John","links":[{"rel":"self","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/2"}]},{"userid":3,"name":"Boguski","surname":"Marek","links":[{"rel":"self","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/3"}]},{"userid":4,"name":"Vardy","surname":"Michael","links":[{"rel":"self","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/4"}]}],"hasMore":false,"limit":25,"offset":0,"count":4,"links":[{"rel":"self","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/"},{"rel":"edit","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/"},{"rel":"describedby","href":"https://oraweb.cern.ch/ords/devdb11/myschema/metadata-catalog/users/"},{"rel":"first","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/"}]}
UPDATE, INSERT and DELETE operations using
AutoREST feature are described here:
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-autorest
Defining RESTful Services using PL/SQL
In this example we will define our own PL/SQL statement to get the date from the USERS table. We already have our schema rest-enabled (look at ORDS.ENABLE_SCHEMA above).
We define a simple service:
BEGIN
ORDS.define_service(
p_module_name => 'testmodule',
p_base_path => 'users/',
p_pattern => 'name/',
p_method => 'GET',
p_source_type => ORDS.source_type_query,
p_source => 'SELECT USERID, NAME FROM USERS',
p_items_per_page => 0);
COMMIT;
END;
p_base_path and p_pattern will become parts of our URL:
https://oraweb.cern.ch/ords/devdb11/myschema/users/name/
Let's try our service:
curl --basic https://oraweb.cern.ch/ords/devdb11/myschema/users/name/
{"items":[{"userid":1,"name":"Smith"},{"userid":2,"name":"Clerk"},{"userid":3,"name":"Boguski"},{"userid":4,"name":"Vardy"}]}
UPDATE, INSERT and DELETE operations are described here:
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-create-basic-rest-web-services-using-plsql
Protecting RESTful Services
Basic Authentication
To protect our service we have to define a role, a privilege and a mapping between the privilege and the URL we want to protect.
ORDS roles map to e-groups but e-group name for protecting ORDS RESTful Services must follow the pattern
ords-rest-access-*
In this example we created and are members of ords-rest-access-ims e-group at CERN.
First we create a role with the same name:
BEGIN
ORDS.create_role(
p_role_name => 'ords-rest-access-ims'
);
COMMIT;
END;
p_role_name directly maps to the CERN e-group name.
Now we can create a privilege associated with that role:
DECLARE
l_arr OWA.vc_arr;
BEGIN
l_arr(1) := 'ords-rest-access-ims';
ORDS.define_privilege (
p_privilege_name => 'rest-access-test',
p_roles => l_arr,
p_label => 'Users data',
p_description => 'Allow access to Users data.'
);
COMMIT;
END;
p_roles param has to contain the role we defined above (can contain more than one role). We also specified the privilege name (p_privilege_name) and a description.
To protect our service we associate the privilege with our url mapping:
BEGIN
ORDS.create_privilege_mapping(
p_privilege_name => 'rest-access-test',
p_pattern => '/users/*'
);
COMMIT;
END;
after creating the mapping we can try to access our service:
curl --basic https://oraweb.cern.ch/ords/devdb11/myschema/users/
We should receive error 401 Unauthorized.
Let's try passing our credentials. In this example
dmoskali
user belongs to e-group ords-rest-access-ims
curl --basic --user dmoskali https://oraweb.cern.ch/ords/devdb11/myschema/users/
Enter host password for user 'dmoskali':
{"items":[{"userid":1,"name":"Smith","surname":"Liam","links":[{"rel":"self","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/1"}]},{"userid":2,"name":"Clerk","surname":"John","links":[{"rel":"self","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/2"}]},{"userid":3,"name":"Boguski","surname":"Marek","links":[{"rel":"self","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/3"}]},{"userid":4,"name":"Vardy","surname":"Michael","links":[{"rel":"self","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/4"}]}],"hasMore":false,"limit":25,"offset":0,"count":4,"links":[{"rel":"self","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/"},{"rel":"edit","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/"},{"rel":"describedby","href":"https://oraweb.cern.ch/ords/devdb11/myschema/metadata-catalog/users/"},{"rel":"first","href":"https://oraweb.cern.ch/ords/devdb11/myschema/users/"}]}
To protect your services you can create any e-group that starts with the prefix
ords-rest-access-
ORDS & OAuth 2 : Client Credentials flow
The client credentials flow is a two-legged process. For this flow we use the client credentials to return an
access token, which is used to authenticate calls to protected resources.
First step is to
register your application as a OAUTH
client:
BEGIN
OAUTH.create_client(
p_name => 'pcitdes12',
p_grant_type => 'client_credentials',
p_owner => 'lurodrig',
p_description => 'Luis client for demo OAUTH2 on ORDS. It will CRUD operations on one table (site) of a specific schema (cerndb_mwctl_a_01_dev)',
p_support_email => 'lurodrig@cern.ch',
p_privilege_names => 'crud-operations-on-site-table'
);
COMMIT;
END;
- p_name: name of your application/client. It must be unique. Here I am using the name of my terminal.
- p_grant_type: for this flow we want to use client_credentials grant. The client will need to present his credentials, client_id & client_secret to access the access token.
- p_owner: here I will set my NICE username for clarity.
- p_description: something meaningful, if possible...
- p_support_email: self-explained, I hope...
- p_privilege_names: this field will tell ORDS what actions this client wants to do.
You can find the full specification for the OAUTH PL/SQL package
here
Like in the basic authentication scenario we have to
create a role:
BEGIN
ORDS.create_role(
p_role_name => 'cerndb-mwctl-a-01-dev-site-api'
);
COMMIT;
END;
We have to
link that
role with the
privileges:
DECLARE
l_priv_roles owa.vc_arr;
BEGIN
l_priv_roles(1) := ''ords-rest-access-dev-site-api';
ords.define_privilege(
p_privilege_name => 'crud-operations-on-site-table',
p_roles => l_priv_roles,
p_label => 'Sites CRUD',
p_description => 'Provides the ability to create, ' ||
'update and delete sites '
);
END;
COMMIT;
The last step will be
grant our
client with that
role:
BEGIN
OAUTH.grant_client_role(
p_client_name => 'pcitdes12',
p_role_name => 'ords-rest-access-dev-site-api'
);
COMMIT;
END;
And we are done! Now we have to retrieve the
client_id and
client_secret:
SELECT id, name, client_id, client_secret
FROM user_ords_clients;
With the
CLIENT_ID and
CLIENT_SECRET you can request the access_token to ORDS:
curl --basic --user CLIENT_ID:CLIENT_SECRET --data "grant_type=client_credentials" https://oraweb.cern.ch/ords/devdb11/cerndb_mwctl_a_01_dev/oauth/token
The output will look like this:
{"access_token":"cXGjuDYwwmn49nVAOoistA..","token_type":"bearer","expires_in":3600}
Finally with the
access_token you can access your protected resources:
curl -H "Authorization: Bearer cXGjuDYwwmn49nVAOoistA.." https://oraweb.cern.ch/ords/devdb11/cerndb_mwctl_a_01_dev/site/
ORDS & OAuth 2 : Implicit
The OAuth2 Implicit grant flow is suitable for client-side web applications (for instance a
JavaScript-client), since we don't need to store the client_id and client_secret.
It requires user interaction instead of storing the credentials. The user accesses an URL in a browser and the browser prompts for credentials.
After the authentication, the browser is redirected to a page which include an access token as a parameter in the URL.
Than we can use the
access_token to access protected resources.
Prerequisite: We start by creating an ORDS-role (p_role_name) as explained in the ‘Basic authentication’-part of this twiki. Later you will need to associate your client with this ORDS-role.
Secondly we must
remember to clean up the OAUTH metadata. This is done in the following way:
BEGIN
OAUTH.revoke_client_role(
p_client_name => 'pcitdb35',
p_role_name => 'ords-rest-access-cerndb-rota-users-api'
);
COMMIT;
END;
/
BEGIN
OAUTH.delete_client(
p_name => 'pcitdb35'
);
COMMIT;
END;
/
Now we define the OAuth2 Implicit grant flow. The first step is to
create the client with the grant type ‘implicit’:
BEGIN
OAUTH.create_client(
p_name => 'pcitdb35',
p_grant_type => 'implicit',
p_owner => 'ralvsvaa',
p_description => 'A client for DB Rota tool',
p_redirect_uri => 'https://oraweb.cern.ch/ords/devdb11/db-rota/users/redirect',
p_support_email => 'ralvsvaa@cern.ch',
p_support_uri => 'https://oraweb.cern.ch/ords/devdb11/db-rota/users/support',
p_privilege_names => 'ords-rest-access-crud-operations-on-users-table'
);
COMMIT;
END;
/
Secondly display the client details and
retrieve the client_id:
COLUMN name FORMAT A20
SELECT id, name, client_id, client_secret
FROM user_ords_clients;
We will get a table showing the client details. The
client_id will be used in the later steps.
Associate the created
client with the
ORDS-role from the ‘Basic Authentication’-part. This role holds the correct privileges for the resources it needs to access:
BEGIN
OAUTH.grant_client_role(
p_client_name => 'pcitdb35',
p_role_name => 'ords-rest-access-cerndb-rota-users-api'
);
COMMIT;
END;
/
Display the client-role relationship:
COLUMN client_name FORMAT A30
COLUMN role_name FORMAT A20
SELECT client_name, role_name
FROM user_ords_client_roles;
Now we need to request an
access_token. This is done by accessing the following URL from a browser:
https://oraweb.cern.ch/ords/devdb11/db-rota/oauth/auth?response_type=token&client_id={client_id}&state={random-string}
It's important to set the response_type to
token, and insert your specific schema (p_url_mapping_pattern, e.g. 'db-rota' ), the
client_id and a random string in the URL.
We should get an Error 401 Unauthorized. From the 'Sign in'-link on this page, we can sign in with the credentials connected to the
e-group (p_role_name).
The e-group was defined in the 'Basic Authentication'.
Next we will be directed to an approval page. Click the "Approve" button, which will take us to the redirect page we specified for the client.
We should get an Error 500 since the redirect URL is not an existing page. In the URL we will find the
access_token as one of the parameters.
And we are done! Now we have the
access_token and can access the protected resource:
curl -H "Authorization: Bearer {access_token}" https://oraweb.cern.ch/ords/devdb11/db-rota/users/
--
RebekkaAlvsvaag - 2017-07-06
How to see defined enpoints, privileges, roles...
Once you REST-enabled your schema you can get the metacatalog that describes all modules/restpoints defined:
https://devapex-sso.cern.ch/ords/devdb11/myschema/metadata-catalog/
Or you can query directly user_ords_modules, user_ords_templates, user_ords_handlers tables.
To see defined roles:
SELECT id, name
FROM user_ords_roles;
Display privileges defined:
SELECT id, name
FROM user_ords_privileges;
Display privilege-role relation:
SELECT privilege_id, privilege_name, role_id, role_name
FROM user_ords_privilege_roles;
Display privilege mappings:
SELECT privilege_id, name, pattern
FROM user_ords_privilege_mappings;
How to get the HTTP status of the operation from PL/SQL
To decide the HTTP status of the operation from PL/SQL, you have to do a couple of things.
- Declare an (INTEGER) OUT parameter in your procedure, so that you can pass back the desired status. For instance
PROCEDURE deleteExternalVisitors(p_data IN BLOB,p_status OUT INTEGER)
. Write your own pl/sql code and decide what HTTP status you want to pass back in p_status (a list of HTTP status codes can be found at:
http://www.restapitutorial.com/httpstatuscodes.html)
- Finally define a parameter for your handler, as in
https://docs.oracle.com/cd/E56351_01/doc.30/e56293/ORDS-reference.htm#AELIG90210
In order to set the HTTP status, use “X-APEX-STATUS-CODE” as indicated in
https://docs.oracle.com/cd/E21611_01/doc.11/e21058/rest_api.htm#AELIG7137
BEGIN
ORDS.DEFINE_PARAMETER(
p_module_name => 'module_ws',
p_pattern => 'bookings/',
p_method => 'DELETE',
p_name => 'X-APEX-STATUS-CODE',
p_bind_variable_name => 'status',
p_source_type => 'HEADER',
p_access_method => 'OUT'
);
COMMIT;
END;
That’s it. ORDS will send the HTTP status that you have determined in your code (received as p_status) as it maps to your parameter “status” (in the handler definition) that maps to X-APEX-STATUS-CODE.
You have to define a parameter for each combination of pattern and method.
How to deal with BLOBs
ORDS does not provide anymore the
file upload and download parameters. Nevertheless if you need to download and upload binaries to the database you can always develop your own little API for this purpose.
Download/Upload API example
Imagine that you have a very simple table like this one
CREATE TABLE "IMAGE"
( "ID" NUMBER NOT NULL ENABLE,
"BIN" BLOB
);
ALTER TABLE "IMAGE" ADD CONSTRAINT "IMAGE_PK" PRIMARY KEY ("ID") ;
COMMIT;
The first things that you have to do is to enable ORDS in your schema, define a module and a template:
BEGIN
ORDS.DEFINE_MODULE(
p_module_name => 'module1',
p_base_path => '/app/images/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
COMMIT;
END;
BEGIN
ORDS.DEFINE_TEMPLATE(
p_module_name => 'module1',
p_pattern => 'image/:id',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
COMMIT;
END;
Download
In order to simplify the code we are using the
ORDS Media Source Type. With this type you just need to specify in your query the returning type (image/png) and the binary value:
BEGIN
ORDS.DEFINE_HANDLER(
p_module_name => 'module1',
p_pattern => 'image/:id',
p_method => 'GET',
p_source_type => ORDS.source_type_media,
p_items_per_page => 0,
p_mimes_allowed => '',
p_comments => NULL,
p_source => 'SELECT ''image/png'', BIN FROM IMAGE where id = :id'
);
COMMIT;
END;
Try it!
https://oraweb.cern.ch/ords/devdb11/app/lurodrig/images/image/1
Upload
For uploading you can write a little PL/SQL block like the below one. The
:body
parameter refers to the HTTP request body
BEGIN
ORDS.DEFINE_HANDLER(
p_module_name => 'module1',
p_pattern => 'image/:id',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_items_per_page => 0,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
' DECLARE
P_BIN BLOB := :body;
P_ID INTEGER := :id;
BEGIN
UPDATE IMAGE SET BIN = P_BIN WHERE ID = P_ID;
END;');
COMMIT;
END;
Try it!
curl -v -F "file=@image.jpg" https://devoraweb.cern.ch/ords/devdb11/lurodrig/app/images/image/2
References
- This article from thatjeffsmith.com is very complete.
- And of course Tim Hall always has very good stuff, check out this article
--
DamianRadoslawMoskalik - 2016-09-22