📋Steps in this guide1/16

Oracle REST Data Services (ORDS) : Authentication

This article gives a quick run through the authentication options available with Oracle REST Data Services (ORDS).

oracle miscconfigurationintermediate

by OracleDba

39 views

Assumptions and Comments

This article assumes the following. The subject of OAuth authentication can be quite confusing if it is your first time looking at it. It will make a lot more sense if you read a general introduction to the subject, this the one here .

HTTP or HTTPS

By default, the OAuth2 protocol requires all calls to be performed using HTTPS. You can read how to configure Apache Tomcat to enable HTTPS here . For ORDS 22.1 you need to set the option as follows. For ORDS versions 3.0 to 21.4, edit the "/u01/ords/conf/ords/defaults.xml" file, adding the following entry. Regardless of the method you use to set the parameter, restart Tomcat for the change to take effect. We need a new database user for our testing. Create and populate a copy of the EMP table. Enable REST web services for the test schema. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name. In this example we use the schema alias "hr". Define a basic web service we can use for the tests. In this case we'll use the AutoREST functionality to generate RESTful web services for the EMP table, using the object alias of "employees". A number of web services will now be available, but we will test using following URLs without authentication. For the rest of the article I will assume HTTPS has been enabled. If you've enabled HTTP access to OAuth, then simply change the test URLs accordingly for the tests. The "curl" command below uses the "-k" option to prevent the call from verifying the self-signed certificate used on this server. We can see the expected JSON data is returned. Now let's start setting up authentication. To protect the web service, we need to create a role with an associated privilege, then map the privilege to the web service. Normally, we would expect a role to be a collection of privileges, and of course a single privilege can be part of multiple roles, but in this case we will keep it simple. The following code creates a new role called "emp_role". We create a new privilege called "emp_priv", which is associated with roles and the the patterns it protects. This code allows us to associated the privilege with several roles and patterns in one step, by extending the arrays. The previous privilege creation and mapping could also have been achieved with the following code. Once this mapping is in place, we can no longer access the web service without authentication. At this point we've not defined how we should authenticate, only that authentication is needed to access this resource. For services based on modules, rather than AutoREST, an alternative to using a mapping pattern is to associate the authentication directly against a module. The following example performs the mapping for a module called "my_module". The mapping details are visible using the view. It's possible to support first party cookie-based authentication, or basic authentication, using a number of mechanisms. Using the ORDS file-based repository is not a supported method, so it should only be used for demos and testing. Create a new ORDS user called "emp_user" with access to the "emp_role" role. Access the web service from a browser using the following URL. You are presented with a 401 message, which includes a "sign in" link. Click the link, sign in with the ORDS credentials you just created and you will be directed to web service output. Alternatively, specify the credentials in a "curl" command. Create a Tomcat role and user by adding the following lines above the "</tomcat-users>" tag in the "$CATALINA_BASE/conf/tomcat-users.xml" file. Restart tomcat. It's possible to use Tomcat basic or digest authentication to control authentication to ORDS based on the contents of database tables using the JDBCRealm. - Oracle REST Data Services (ORDS) : Basic and Digest Authentication on Tomcat using JDBCRealm One of the new features of ORDS 18.1 is you can now use database authentication to provide basic authentication for your calls to PL/SQL. You can read more about this functionality here. - Oracle REST Data Services (ORDS) : Database Authentication If you are using ORDS 24.3 or higher, configure OAuth using the method described in this article. - Oracle REST Data Services (ORDS) : Authentication Enhancements (ORDS_SECURITY and ORDS_SECURITY_ADMIN Packages) If you are using a version of ORDS prior to version 24.3, continue to use this article for the OAuth configuration. There are several methods of authorizing to the web service using OAuth. Before starting each example, make sure you clean up the OAuth metadata, as described in the Deleting OAUTH Metadata section. You do not need to delete the web service itself or the ORDS role, privilege and URL mapping. Each authorization example assumes these are already in place. OAuth revolves around registering clients, which represent a person or an application wanting to access the resource, then associating those clients to roles. Once the client is authorized, it has access to the protected resources associated with the roles. Many of the parameters in calls to the package are optional, but cause problems down the line if they are omitted. The package calls in the following examples are the simplest I could make them without causing failures. When calling the procedure, the parameter is mandatory, but it will accept dummy text if you don't want to associate a privilege directly to the client. The client credentials flow is a two-legged process that seems the most natural to me as I mostly deal with server-server communication, which should have no human interaction. For this flow we use the client credentials to return an access token, which is used to authorize calls to protected resources. The example steps through the individual calls, but in reality it would be automated by the application. Remember to clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. Create a client with the grant type of "client_credentials". Associate the client with the role that holds the correct privileges for the resources it needs to access. In order to access the web service, we must first retrieve an access token using the and we queried from the view. The example below retrieves the access token. Notice the user format of "CLIENT_ID:CLIENT_SECRET". It is easy to miss the ":" when you look at this for the first time. We can now use the access token to call our web service. Notice the "Authorization: Bearer {access-token}" entry in the header of the call. We successfully accessed the protected web service. The authorization code flow is a three-legged process. The user accesses a URL in a browser, which prompts for credentials. Once authorized, the browser is redirected to a specified page with an authhorization code as one of the parameters in the URL. That authorization code is used in a call to generate an access token, which is used to authorize calls to protected resources. With the exception of the user confirmation, all the other steps in the flow should be handled by the application. All the steps will be presented separately in the example that follows. This flow sounds complicated, but the important point to remember is the calling application never sees the user credentials. ORDS handles the user login and sends an authorization code back to the application, so it can continue with the authorization process. Remember to clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. The first-party authentication must be working for this flow to work. Create a client using the grant type of "authorization_code". The redirect and support URLs are not real, but we will be able to follow the example through anyway. We then attempt to request an authorization code. Notice we are using the from the view along with a unique string that will represent the state. Access the following URL from a browser. You are presented with a 401 message, which includes a "sign in" link. Click the link, sign in with the ORDS credentials you created earlier (emp_user) and you will be directed to an approval page. Click the "Approve" button, which will take you to the redirect page you specified for the client. The redirect page we specified for the client doesn't really exist, but we can get the authorization code and state from the URL. The application should check the state string matches the one used in the initial call. We use the authorization code to retrieve the access token. The following call retrieves the access token. We can now access the protected resource using the access token. As mentioned before, this looks complicated, but it allows a calling application to authenticate to a web service without seeing the user credentials. The application just has to know the and that were registered for it, and go through the user approval process to get the authorisation code. The implicit flow is a two-legged process that requires user interaction. The user accesses a URL in a browser, which prompts for credentials. Once authorized, the browser is redirected to a specified page with an access token as one of the parameters in the URL. That access token is used to authorize calls to protected resources. The example steps through the individual calls, but in reality everything but the user interaction would be automated by the application. Remember to clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. Create a client using the grant type of "implicit". The redirect and support URLs are not real, but we will be able to follow the example through anyway. We then attempt to request an access token. Notice we are using the from the view along with a unique string that will represent the state. Access the following URL from a browser. You are presented with a 401 message, which includes a "sign in" link. Click the link, sign in with the ORDS credentials you created earlier (emp_user) and you will be directed to an approval page. Click the "Approve" button, which will take you to the redirect page you specified for the client. The redirect page we specified for the client doesn't really exist, but we can get the access token from the URL. The application should check the state string matches the one used in the initial call. We can now access the protected resource using the access token. We remove the OAuth metadata for the client role mapping and the client using the following code. They could be combined as a single block, but I've left them separate so they can be run individually. The ORDS URL mapping, privilege and roles are deleted using the following code. Once this is removed, the web service will be publicly available again. For more information see: - An Introduction to OAuth 2 - Tutorial: Protecting and Accessing Resources - ORDS PL/SQL Package Reference - OAUTH PL/SQL Package Reference - Using OAuth2 in Non-HTTPS Environments - Oracle REST Data Services (ORDS) : All Articles - Oracle REST Data Services (ORDS) : Authentication Enhancements (ORDS_SECURITY and ORDS_SECURITY_ADMIN Packages) - Oracle REST Data Services (ORDS) : Pre-Authenticated Requests - Oracle REST Data Services (ORDS) : Database Authentication - Oracle REST Data Services (ORDS) : Custom Authentication Schemes - Oracle REST Data Services (ORDS) : Installation on Tomcat - Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL - Oracle REST Data Services (ORDS) : AutoREST - Oracle REST Data Services (ORDS) : Configure Multiple Databases - Oracle REST Data Services (ORDS) : First Party (Basic) Authentication on Tomcat - Oracle REST Data Services (ORDS) : OAuth Client Credentials Authorization - Oracle REST Data Services (ORDS) : OAuth Authorization Code - Oracle REST Data Services (ORDS) : OAuth Implicit Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

ords --config /u01/config/ords config set security.verifySSL false

<entry key="security.verifySSL">false</entry>

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

conn / as sysdba
alter session set container=pdb1;

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

conn testuser1/testuser1@pdb1

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;

conn testuser1/testuser1@pdb1

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;
/

conn testuser1/testuser1@pdb1

begin
  ords.enable_object (
    p_enabled      => true, -- Default  { true | false }
    p_schema       => 'TESTUSER1',
    p_object       => 'EMP',
    p_object_type  => 'TABLE', -- Default  { table | view }
    p_object_alias => 'employees'
  );
    
  commit;
end;
/

http://localhost:8080/ords/hr/employees/7788
https://localhost:8443/ords/hr/employees/7788

$
curl -i -k https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
ETag: "jtC17IXyetESUjSkxB2ani/a1TnFh28yfor+fLmxxUzGr6G9IFxQ77+/Gd71W4Qzz0rSxf90Qqbl+ICwezTayQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 08:35:50 GMT

{"items":[{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3003,
"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self",
"href":"https://localhost:8443/ords/hr/employees/7788"},{"rel":"describedby",
"href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"}]}
$

conn testuser1/testuser1@pdb1

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


-- Display the role.
column name format a20

select id, name
from   user_ords_roles
where  name = 'emp_role';

        ID NAME
---------- --------------------
     10312 emp_role

SQL>

conn testuser1/testuser1@pdb1

declare
  l_roles_arr    owa.vc_arr;
  l_patterns_arr owa.vc_arr;
begin
  l_roles_arr(1)    := 'emp_role';
  l_patterns_arr(1) := '/employees/*';
  
  ords.define_privilege (
    p_privilege_name => 'emp_priv',
    p_roles          => l_roles_arr,
    p_patterns       => l_patterns_arr,
    p_label          => 'EMP Data',
    p_description    => 'Allow access to the EMP data.'
  );
   
  commit;
end;
/


-- Display the privilege information.
column name format a20

select id, name
from   user_ords_privileges
where  name = 'emp_priv';

        ID NAME
---------- --------------------
     10313 emp_priv

SQL>


-- Display the privilege-role relationship.
column privilege_name format a20
column role_name format a20

select privilege_id, privilege_name, role_id, role_name
from   user_ords_privilege_roles
where  role_name = 'emp_role';

PRIVILEGE_ID PRIVILEGE_NAME          ROLE_ID ROLE_NAME
------------ -------------------- ---------- --------------------
       10313 emp_priv                  10312 emp_role

SQL>


-- Display the mapping information.
column name format a20
column pattern format a20

select privilege_id, name, pattern
from   user_ords_privilege_mappings
where  name = 'emp_priv';

PRIVILEGE_ID NAME                 PATTERN
------------ -------------------- --------------------
       10246 emp_priv             /employees/*

SQL>

begin
  ords.create_privilege(
      p_name        => 'emp_priv',
      p_role_name   => 'emp_role',
      p_label       => 'EMP Data',
      p_description => 'Allow access to the EMP data.');

  ords.create_privilege_mapping(
      p_privilege_name => 'emp_priv',
      p_pattern        => '/employees/*');     

  commit;
end;
/

$
curl -i -k https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 401 Unauthorized
Server: Apache-Coyote/1.1
Content-Type: text/html
Content-Length: 11577
Date: Wed, 29 Jun 2016 08:45:32 GMT
.
. Edited out for brevity.
.
$

conn testuser1/testuser1@pdb1

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)  := 'my_modules';
  
  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;
/

$
cd /u01/ords
$
$JAVA_HOME/bin/java -jar ords.war user emp_user emp_role
Enter a password for user emp_user:
Confirm password for user emp_user:
Jun 29, 2016 11:52:42 AM oracle.dbtools.standalone.ModifyUser execute
INFO: Created user: emp_user in file: /u01/ords/conf/ords/credentials
$

https://localhost:8443/ords/hr/employees/7788

$
curl -i -k --user emp_user:Password1 https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
ETag: "jtC17IXyetESUjSkxB2ani/a1TnFh28yfor+fLmxxUzGr6G9IFxQ77+/Gd71W4Qzz0rSxf90Qqbl+ICwezTayQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 02 Jul 2016 06:19:47 GMT

{"items":[{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3003,
"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self",
"href":"https://localhost:8443/ords/hr/employees/7788"},{"rel":"describedby",
"href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"}]}
$

<role rolename="emp_role"/>
  <user username="emp_user" password="Password1" roles="emp_role"/>

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

conn testuser1/testuser1@pdb1

begin
  oauth.create_client(
    p_name            => 'emp_client',
    p_grant_type      => 'client_credentials',
    p_owner           => 'My Company Limited',
    p_description     => 'A client for Emp management',
    p_support_email   => '[email protected]',
    p_privilege_names => 'emp_priv'
  );

  commit;
end;
/

-- Display client details.
column name format a20

select id, name, client_id, client_secret
from   user_ords_clients;

        ID NAME                 CLIENT_ID                        CLIENT_SECRET
---------- -------------------- -------------------------------- --------------------------------
     10316 emp_client           3NvJRo_a0UwGKx7Q-kivtA..         F5WVwyrWxXj3ykmhSONldQ..

SQL>


-- Display client-privilege relationship.
select name, client_name
from   user_ords_client_privileges;

NAME                 CLIENT_NAME
-------------------- ------------------------------
emp_priv             emp_client

SQL>

begin
  oauth.grant_client_role(
    p_client_name => 'emp_client',
    p_role_name   => 'emp_role'
  );

  commit;
end;
/


-- Display client-role relationship.
column client_name format a30
column role_name format a20

select client_name, role_name
from   user_ords_client_roles;

CLIENT_NAME                    ROLE_NAME
------------------------------ --------------------
emp_client                     emp_role

SQL>

CLIENT_ID     : 3NvJRo_a0UwGKx7Q-kivtA..
CLIENT_SECRET : F5WVwyrWxXj3ykmhSONldQ..
OAUTH URL     : https://localhost:8443/ords/hr/oauth/token

$
curl -i -k --user 3NvJRo_a0UwGKx7Q-kivtA..:F5WVwyrWxXj3ykmhSONldQ.. --data "grant_type=client_credentials" https://localhost:8443/ords/hr/oauth/token
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 12:07:02 GMT

{"access_token":"
-zYl-sFyB2iLicAHw2TsRA..
","token_type":"bearer","expires_in":3600}
$

$
curl -i -k -H"Authorization: Bearer -zYl-sFyB2iLicAHw2TsRA.." https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
ETag: "jtC17IXyetESUjSkxB2ani/a1TnFh28yfor+fLmxxUzGr6G9IFxQ77+/Gd71W4Qzz0rSxf90Qqbl+ICwezTayQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 12:07:31 GMT

{"items":[{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3003,
"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self",
"href":"https://localhost:8443/ords/hr/employees/7788"},{"rel":"describedby",
"href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"}]}
$

Conn testuser1/testuser1@pdb1

begin
  oauth.create_client(
    p_name            => 'emp_client',
    p_grant_type      => 'authorization_code',
    p_owner           => 'My Company Limited',
    p_description     => 'A client for Emp management',
    p_redirect_uri    => 'https://localhost:8443/ords/hr/redirect',
    p_support_email   => '[email protected]',
    p_support_uri     => 'https://localhost:8443/ords/hr/support',
    p_privilege_names => 'emp_priv'
  );

  commit;
end;
/


-- Display client details.
column name format a20

select id, name, client_id, client_secret
from   user_ords_clients;

        ID NAME                 CLIENT_ID                        CLIENT_SECRET
---------- -------------------- -------------------------------- --------------------------------
     10333 emp_client           gxqNSyxPbLUJhSj1yBe8qA..         E-_mKJBlOTfTdHc_zISniA..

SQL>

CLIENT_ID     : gxqNSyxPbLUJhSj1yBe8qA..
State         : 3668D7A713E93372E0406A38A8C02171
URL           : https://localhost:8443/ords/hr/oauth/auth?response_type=code&client_id={client_id}&state={state}

https://localhost:8443/ords/hr/oauth/auth?response_type=code&client_id=
gxqNSyxPbLUJhSj1yBe8qA..
&state=
3668D7A713E93372E0406A38A8C02171

https://localhost:8443/ords/hr/redirect?code=
FF-APuIMukuBlrver1XU2A..
&state=3668D7A713E93372E0406A38A8C02171

CLIENT_ID     : gxqNSyxPbLUJhSj1yBe8qA..
CLIENT_SECRET : E-_mKJBlOTfTdHc_zISniA..
User          : CLIENT_ID:CLIENT_SECRET
Data          : grant_type=authorization_code&code={authorization-code}
URL           : https://localhost:8443/ords/hr/oauth/token

$
curl -i -k --user gxqNSyxPbLUJhSj1yBe8qA..:E-_mKJBlOTfTdHc_zISniA.. --data "grant_type=authorization_code&code=FF-APuIMukuBlrver1XU2A.." https://localhost:8443/ords/hr/oauth/token
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 12:38:52 GMT

{"access_token":"
cOYb2hFK_SyxOh8o9n6R7A..
","token_type":"bearer","expires_in":3600,"refresh_token":"RC33rvSwAfhguraOWlvgfA.."}
$

$
curl -i -k -H"Authorization: Bearer cOYb2hFK_SyxOh8o9n6R7A.." https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
ETag: "jtC17IXyetESUjSkxB2ani/a1TnFh28yfor+fLmxxUzGr6G9IFxQ77+/Gd71W4Qzz0rSxf90Qqbl+ICwezTayQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 12:40:34 GMT

{"items":[{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3003,
"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self",
"href":"https://localhost:8443/ords/hr/employees/7788"},{"rel":"describedby",
"href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"}]}
$

conn testuser1/testuser1@pdb1

begin
  oauth.create_client(
    p_name            => 'emp_client',
    p_grant_type      => 'implicit',
    p_owner           => 'My Company Limited',
    p_description     => 'A client for Emp management',
    p_redirect_uri    => 'https://localhost:8443/ords/hr/redirect',
    p_support_email   => '[email protected]',
    p_support_uri     => 'https://localhost:8443/ords/hr/support',
    p_privilege_names => 'emp_priv'
  );

  commit;
end;
/


-- Display client details.
column name format a20

select id, name, client_id, client_secret
from   user_ords_clients;

        ID NAME                 CLIENT_ID                        CLIENT_SECRET
---------- -------------------- -------------------------------- --------------------------------
     10325 emp_client           0docHbkL8__7Ic58n7GCBA..

SQL>

CLIENT_ID     : 0docHbkL8__7Ic58n7GCBA..
State         : 3668D7A713E93372E0406A38A8C02171
URL           : https://localhost:8443/ords/hr/oauth/auth?response_type=code&client_id={client_id}&state={random-string}

https://localhost:8443/ords/hr/oauth/auth?response_type=token&client_id=
0docHbkL8__7Ic58n7GCBA..
&state=
3668D7A713E93372E0406A38A8C02171

https://localhost:8443/ords/hr/redirect#token_type=bearer&access_token=
5SVR_NVP5N_OnDQt6iSxJg..
&expires_in=3600&state=3668D7A713E93372E0406A38A8C02171

$
curl -i -k -H"Authorization: Bearer 5SVR_NVP5N_OnDQt6iSxJg.." https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
ETag: "jtC17IXyetESUjSkxB2ani/a1TnFh28yfor+fLmxxUzGr6G9IFxQ77+/Gd71W4Qzz0rSxf90Qqbl+ICwezTayQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 12:15:35 GMT

{"items":[{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3003,
"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self",
"href":"https://localhost:8443/ords/hr/employees/7788"},{"rel":"describedby",
"href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"}]}
$

begin
  oauth.revoke_client_role(
    p_client_name => 'emp_client',
    p_role_name   => 'emp_role'
  );

  commit;
end;
/

begin
  oauth.delete_client(
    p_name => 'emp_client'
  );

  commit;
end;
/

begin
  ords.delete_privilege_mapping(
    p_privilege_name => 'emp_priv',
    p_pattern => '/employees/*'
  );     

  commit;
end;
/

begin
  ords.delete_privilege (
    p_name => 'emp_priv'
  );
   
  commit;
end;
/

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

Create a Test Database User

We need a new database user for our testing. Create and populate a copy of the EMP table.

Code/Command (click line numbers to comment):

conn / as sysdba
alter session set container=pdb1;

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

conn testuser1/testuser1@pdb1

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;

Enable ORDS and Create a Web Service

Enable REST web services for the test schema. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name. In this example we use the schema alias "hr". Define a basic web service we can use for the tests. In this case we'll use the AutoREST functionality to generate RESTful web services for the EMP table, using the object alias of "employees". A number of web services will now be available, but we will test using following URLs without authentication. For the rest of the article I will assume HTTPS has been enabled. If you've enabled HTTP access to OAuth, then simply change the test URLs accordingly for the tests. The "curl" command below uses the "-k" option to prevent the call from verifying the self-signed certificate used on this server. We can see the expected JSON data is returned. Now let's start setting up authentication.

Code/Command (click line numbers to comment):

conn testuser1/testuser1@pdb1

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;
/

conn testuser1/testuser1@pdb1

begin
  ords.enable_object (
    p_enabled      => true, -- Default  { true | false }
    p_schema       => 'TESTUSER1',
    p_object       => 'EMP',
    p_object_type  => 'TABLE', -- Default  { table | view }
    p_object_alias => 'employees'
  );
    
  commit;
end;
/

http://localhost:8080/ords/hr/employees/7788
https://localhost:8443/ords/hr/employees/7788

$
curl -i -k https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
ETag: "jtC17IXyetESUjSkxB2ani/a1TnFh28yfor+fLmxxUzGr6G9IFxQ77+/Gd71W4Qzz0rSxf90Qqbl+ICwezTayQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 08:35:50 GMT

{"items":[{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3003,
"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self",
"href":"https://localhost:8443/ords/hr/employees/7788"},{"rel":"describedby",
"href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"}]}
$

ORDS Roles and Privileges

To protect the web service, we need to create a role with an associated privilege, then map the privilege to the web service. Normally, we would expect a role to be a collection of privileges, and of course a single privilege can be part of multiple roles, but in this case we will keep it simple. The following code creates a new role called "emp_role". We create a new privilege called "emp_priv", which is associated with roles and the the patterns it protects. This code allows us to associated the privilege with several roles and patterns in one step, by extending the arrays. The previous privilege creation and mapping could also have been achieved with the following code. Once this mapping is in place, we can no longer access the web service without authentication. At this point we've not defined how we should authenticate, only that authentication is needed to access this resource. For services based on modules, rather than AutoREST, an alternative to using a mapping pattern is to associate the authentication directly against a module. The following example performs the mapping for a module called "my_module". The mapping details are visible using the view.

Code/Command (click line numbers to comment):

conn testuser1/testuser1@pdb1

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


-- Display the role.
column name format a20

select id, name
from   user_ords_roles
where  name = 'emp_role';

        ID NAME
---------- --------------------
     10312 emp_role

SQL>

conn testuser1/testuser1@pdb1

declare
  l_roles_arr    owa.vc_arr;
  l_patterns_arr owa.vc_arr;
begin
  l_roles_arr(1)    := 'emp_role';
  l_patterns_arr(1) := '/employees/*';
  
  ords.define_privilege (
    p_privilege_name => 'emp_priv',
    p_roles          => l_roles_arr,
    p_patterns       => l_patterns_arr,
    p_label          => 'EMP Data',
    p_description    => 'Allow access to the EMP data.'
  );
   
  commit;
end;
/


-- Display the privilege information.
column name format a20

select id, name
from   user_ords_privileges
where  name = 'emp_priv';

        ID NAME
---------- --------------------
     10313 emp_priv

SQL>


-- Display the privilege-role relationship.
column privilege_name format a20
column role_name format a20

select privilege_id, privilege_name, role_id, role_name
from   user_ords_privilege_roles
where  role_name = 'emp_role';

PRIVILEGE_ID PRIVILEGE_NAME          ROLE_ID ROLE_NAME
------------ -------------------- ---------- --------------------
       10313 emp_priv                  10312 emp_role

SQL>


-- Display the mapping information.
column name format a20
column pattern format a20

select privilege_id, name, pattern
from   user_ords_privilege_mappings
where  name = 'emp_priv';

PRIVILEGE_ID NAME                 PATTERN
------------ -------------------- --------------------
       10246 emp_priv             /employees/*

SQL>

begin
  ords.create_privilege(
      p_name        => 'emp_priv',
      p_role_name   => 'emp_role',
      p_label       => 'EMP Data',
      p_description => 'Allow access to the EMP data.');

  ords.create_privilege_mapping(
      p_privilege_name => 'emp_priv',
      p_pattern        => '/employees/*');     

  commit;
end;
/

$
curl -i -k https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 401 Unauthorized
Server: Apache-Coyote/1.1
Content-Type: text/html
Content-Length: 11577
Date: Wed, 29 Jun 2016 08:45:32 GMT
.
. Edited out for brevity.
.
$

conn testuser1/testuser1@pdb1

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)  := 'my_modules';
  
  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;
/

First-Party Authentication (Basic Authentication)

It's possible to support first party cookie-based authentication, or basic authentication, using a number of mechanisms.

File-Based Repository

Using the ORDS file-based repository is not a supported method, so it should only be used for demos and testing. Create a new ORDS user called "emp_user" with access to the "emp_role" role. Access the web service from a browser using the following URL. You are presented with a 401 message, which includes a "sign in" link. Click the link, sign in with the ORDS credentials you just created and you will be directed to web service output. Alternatively, specify the credentials in a "curl" command.

Code/Command (click line numbers to comment):

$
cd /u01/ords
$
$JAVA_HOME/bin/java -jar ords.war user emp_user emp_role
Enter a password for user emp_user:
Confirm password for user emp_user:
Jun 29, 2016 11:52:42 AM oracle.dbtools.standalone.ModifyUser execute
INFO: Created user: emp_user in file: /u01/ords/conf/ords/credentials
$

https://localhost:8443/ords/hr/employees/7788

$
curl -i -k --user emp_user:Password1 https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
ETag: "jtC17IXyetESUjSkxB2ani/a1TnFh28yfor+fLmxxUzGr6G9IFxQ77+/Gd71W4Qzz0rSxf90Qqbl+ICwezTayQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 02 Jul 2016 06:19:47 GMT

{"items":[{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3003,
"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self",
"href":"https://localhost:8443/ords/hr/employees/7788"},{"rel":"describedby",
"href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"}]}
$

Tomcat Users

Create a Tomcat role and user by adding the following lines above the "</tomcat-users>" tag in the "$CATALINA_BASE/conf/tomcat-users.xml" file. Restart tomcat.

Code/Command (click line numbers to comment):

<role rolename="emp_role"/>
  <user username="emp_user" password="Password1" roles="emp_role"/>

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

Tomcat JDBCRealm

It's possible to use Tomcat basic or digest authentication to control authentication to ORDS based on the contents of database tables using the JDBCRealm. - Oracle REST Data Services (ORDS) : Basic and Digest Authentication on Tomcat using JDBCRealm

Database Authentication

One of the new features of ORDS 18.1 is you can now use database authentication to provide basic authentication for your calls to PL/SQL. You can read more about this functionality here. - Oracle REST Data Services (ORDS) : Database Authentication

OAuth

If you are using ORDS 24.3 or higher, configure OAuth using the method described in this article. - Oracle REST Data Services (ORDS) : Authentication Enhancements (ORDS_SECURITY and ORDS_SECURITY_ADMIN Packages) If you are using a version of ORDS prior to version 24.3, continue to use this article for the OAuth configuration. There are several methods of authorizing to the web service using OAuth. Before starting each example, make sure you clean up the OAuth metadata, as described in the Deleting OAUTH Metadata section. You do not need to delete the web service itself or the ORDS role, privilege and URL mapping. Each authorization example assumes these are already in place. OAuth revolves around registering clients, which represent a person or an application wanting to access the resource, then associating those clients to roles. Once the client is authorized, it has access to the protected resources associated with the roles. Many of the parameters in calls to the package are optional, but cause problems down the line if they are omitted. The package calls in the following examples are the simplest I could make them without causing failures. When calling the procedure, the parameter is mandatory, but it will accept dummy text if you don't want to associate a privilege directly to the client.

OAuth : Client Credentials

The client credentials flow is a two-legged process that seems the most natural to me as I mostly deal with server-server communication, which should have no human interaction. For this flow we use the client credentials to return an access token, which is used to authorize calls to protected resources. The example steps through the individual calls, but in reality it would be automated by the application. Remember to clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. Create a client with the grant type of "client_credentials". Associate the client with the role that holds the correct privileges for the resources it needs to access. In order to access the web service, we must first retrieve an access token using the and we queried from the view. The example below retrieves the access token. Notice the user format of "CLIENT_ID:CLIENT_SECRET". It is easy to miss the ":" when you look at this for the first time. We can now use the access token to call our web service. Notice the "Authorization: Bearer {access-token}" entry in the header of the call. We successfully accessed the protected web service.

Code/Command (click line numbers to comment):

conn testuser1/testuser1@pdb1

begin
  oauth.create_client(
    p_name            => 'emp_client',
    p_grant_type      => 'client_credentials',
    p_owner           => 'My Company Limited',
    p_description     => 'A client for Emp management',
    p_support_email   => '[email protected]',
    p_privilege_names => 'emp_priv'
  );

  commit;
end;
/

-- Display client details.
column name format a20

select id, name, client_id, client_secret
from   user_ords_clients;

        ID NAME                 CLIENT_ID                        CLIENT_SECRET
---------- -------------------- -------------------------------- --------------------------------
     10316 emp_client           3NvJRo_a0UwGKx7Q-kivtA..         F5WVwyrWxXj3ykmhSONldQ..

SQL>


-- Display client-privilege relationship.
select name, client_name
from   user_ords_client_privileges;

NAME                 CLIENT_NAME
-------------------- ------------------------------
emp_priv             emp_client

SQL>

begin
  oauth.grant_client_role(
    p_client_name => 'emp_client',
    p_role_name   => 'emp_role'
  );

  commit;
end;
/


-- Display client-role relationship.
column client_name format a30
column role_name format a20

select client_name, role_name
from   user_ords_client_roles;

CLIENT_NAME                    ROLE_NAME
------------------------------ --------------------
emp_client                     emp_role

SQL>

CLIENT_ID     : 3NvJRo_a0UwGKx7Q-kivtA..
CLIENT_SECRET : F5WVwyrWxXj3ykmhSONldQ..
OAUTH URL     : https://localhost:8443/ords/hr/oauth/token

$
curl -i -k --user 3NvJRo_a0UwGKx7Q-kivtA..:F5WVwyrWxXj3ykmhSONldQ.. --data "grant_type=client_credentials" https://localhost:8443/ords/hr/oauth/token
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 12:07:02 GMT

{"access_token":"
-zYl-sFyB2iLicAHw2TsRA..
","token_type":"bearer","expires_in":3600}
$

$
curl -i -k -H"Authorization: Bearer -zYl-sFyB2iLicAHw2TsRA.." https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
ETag: "jtC17IXyetESUjSkxB2ani/a1TnFh28yfor+fLmxxUzGr6G9IFxQ77+/Gd71W4Qzz0rSxf90Qqbl+ICwezTayQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 12:07:31 GMT

{"items":[{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3003,
"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self",
"href":"https://localhost:8443/ords/hr/employees/7788"},{"rel":"describedby",
"href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"}]}
$

OAuth : Authorization Code

The authorization code flow is a three-legged process. The user accesses a URL in a browser, which prompts for credentials. Once authorized, the browser is redirected to a specified page with an authhorization code as one of the parameters in the URL. That authorization code is used in a call to generate an access token, which is used to authorize calls to protected resources. With the exception of the user confirmation, all the other steps in the flow should be handled by the application. All the steps will be presented separately in the example that follows. This flow sounds complicated, but the important point to remember is the calling application never sees the user credentials. ORDS handles the user login and sends an authorization code back to the application, so it can continue with the authorization process. Remember to clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. The first-party authentication must be working for this flow to work. Create a client using the grant type of "authorization_code". The redirect and support URLs are not real, but we will be able to follow the example through anyway. We then attempt to request an authorization code. Notice we are using the from the view along with a unique string that will represent the state. Access the following URL from a browser. You are presented with a 401 message, which includes a "sign in" link. Click the link, sign in with the ORDS credentials you created earlier (emp_user) and you will be directed to an approval page. Click the "Approve" button, which will take you to the redirect page you specified for the client. The redirect page we specified for the client doesn't really exist, but we can get the authorization code and state from the URL. The application should check the state string matches the one used in the initial call. We use the authorization code to retrieve the access token. The following call retrieves the access token. We can now access the protected resource using the access token. As mentioned before, this looks complicated, but it allows a calling application to authenticate to a web service without seeing the user credentials. The application just has to know the and that were registered for it, and go through the user approval process to get the authorisation code.

Code/Command (click line numbers to comment):

Conn testuser1/testuser1@pdb1

begin
  oauth.create_client(
    p_name            => 'emp_client',
    p_grant_type      => 'authorization_code',
    p_owner           => 'My Company Limited',
    p_description     => 'A client for Emp management',
    p_redirect_uri    => 'https://localhost:8443/ords/hr/redirect',
    p_support_email   => '[email protected]',
    p_support_uri     => 'https://localhost:8443/ords/hr/support',
    p_privilege_names => 'emp_priv'
  );

  commit;
end;
/


-- Display client details.
column name format a20

select id, name, client_id, client_secret
from   user_ords_clients;

        ID NAME                 CLIENT_ID                        CLIENT_SECRET
---------- -------------------- -------------------------------- --------------------------------
     10333 emp_client           gxqNSyxPbLUJhSj1yBe8qA..         E-_mKJBlOTfTdHc_zISniA..

SQL>

CLIENT_ID     : gxqNSyxPbLUJhSj1yBe8qA..
State         : 3668D7A713E93372E0406A38A8C02171
URL           : https://localhost:8443/ords/hr/oauth/auth?response_type=code&client_id={client_id}&state={state}

https://localhost:8443/ords/hr/oauth/auth?response_type=code&client_id=
gxqNSyxPbLUJhSj1yBe8qA..
&state=
3668D7A713E93372E0406A38A8C02171

https://localhost:8443/ords/hr/redirect?code=
FF-APuIMukuBlrver1XU2A..
&state=3668D7A713E93372E0406A38A8C02171

CLIENT_ID     : gxqNSyxPbLUJhSj1yBe8qA..
CLIENT_SECRET : E-_mKJBlOTfTdHc_zISniA..
User          : CLIENT_ID:CLIENT_SECRET
Data          : grant_type=authorization_code&code={authorization-code}
URL           : https://localhost:8443/ords/hr/oauth/token

$
curl -i -k --user gxqNSyxPbLUJhSj1yBe8qA..:E-_mKJBlOTfTdHc_zISniA.. --data "grant_type=authorization_code&code=FF-APuIMukuBlrver1XU2A.." https://localhost:8443/ords/hr/oauth/token
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
X-Frame-Options: SAMEORIGIN
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 12:38:52 GMT

{"access_token":"
cOYb2hFK_SyxOh8o9n6R7A..
","token_type":"bearer","expires_in":3600,"refresh_token":"RC33rvSwAfhguraOWlvgfA.."}
$

$
curl -i -k -H"Authorization: Bearer cOYb2hFK_SyxOh8o9n6R7A.." https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
ETag: "jtC17IXyetESUjSkxB2ani/a1TnFh28yfor+fLmxxUzGr6G9IFxQ77+/Gd71W4Qzz0rSxf90Qqbl+ICwezTayQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 12:40:34 GMT

{"items":[{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3003,
"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self",
"href":"https://localhost:8443/ords/hr/employees/7788"},{"rel":"describedby",
"href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"}]}
$

OAuth : Implicit

The implicit flow is a two-legged process that requires user interaction. The user accesses a URL in a browser, which prompts for credentials. Once authorized, the browser is redirected to a specified page with an access token as one of the parameters in the URL. That access token is used to authorize calls to protected resources. The example steps through the individual calls, but in reality everything but the user interaction would be automated by the application. Remember to clean up the OAuth metadata, as described in the Deleting OAuth Metadata section. Create a client using the grant type of "implicit". The redirect and support URLs are not real, but we will be able to follow the example through anyway. We then attempt to request an access token. Notice we are using the from the view along with a unique string that will represent the state. Access the following URL from a browser. You are presented with a 401 message, which includes a "sign in" link. Click the link, sign in with the ORDS credentials you created earlier (emp_user) and you will be directed to an approval page. Click the "Approve" button, which will take you to the redirect page you specified for the client. The redirect page we specified for the client doesn't really exist, but we can get the access token from the URL. The application should check the state string matches the one used in the initial call. We can now access the protected resource using the access token.

Code/Command (click line numbers to comment):

conn testuser1/testuser1@pdb1

begin
  oauth.create_client(
    p_name            => 'emp_client',
    p_grant_type      => 'implicit',
    p_owner           => 'My Company Limited',
    p_description     => 'A client for Emp management',
    p_redirect_uri    => 'https://localhost:8443/ords/hr/redirect',
    p_support_email   => '[email protected]',
    p_support_uri     => 'https://localhost:8443/ords/hr/support',
    p_privilege_names => 'emp_priv'
  );

  commit;
end;
/


-- Display client details.
column name format a20

select id, name, client_id, client_secret
from   user_ords_clients;

        ID NAME                 CLIENT_ID                        CLIENT_SECRET
---------- -------------------- -------------------------------- --------------------------------
     10325 emp_client           0docHbkL8__7Ic58n7GCBA..

SQL>

CLIENT_ID     : 0docHbkL8__7Ic58n7GCBA..
State         : 3668D7A713E93372E0406A38A8C02171
URL           : https://localhost:8443/ords/hr/oauth/auth?response_type=code&client_id={client_id}&state={random-string}

https://localhost:8443/ords/hr/oauth/auth?response_type=token&client_id=
0docHbkL8__7Ic58n7GCBA..
&state=
3668D7A713E93372E0406A38A8C02171

https://localhost:8443/ords/hr/redirect#token_type=bearer&access_token=
5SVR_NVP5N_OnDQt6iSxJg..
&expires_in=3600&state=3668D7A713E93372E0406A38A8C02171

$
curl -i -k -H"Authorization: Bearer 5SVR_NVP5N_OnDQt6iSxJg.." https://localhost:8443/ords/hr/employees/7788
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
ETag: "jtC17IXyetESUjSkxB2ani/a1TnFh28yfor+fLmxxUzGr6G9IFxQ77+/Gd71W4Qzz0rSxf90Qqbl+ICwezTayQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 29 Jun 2016 12:15:35 GMT

{"items":[{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3003,
"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self",
"href":"https://localhost:8443/ords/hr/employees/7788"},{"rel":"describedby",
"href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"}]}
$

Deleting OAuth Metadata

We remove the OAuth metadata for the client role mapping and the client using the following code. They could be combined as a single block, but I've left them separate so they can be run individually.

Code/Command (click line numbers to comment):

begin
  oauth.revoke_client_role(
    p_client_name => 'emp_client',
    p_role_name   => 'emp_role'
  );

  commit;
end;
/

begin
  oauth.delete_client(
    p_name => 'emp_client'
  );

  commit;
end;
/

Deleting Roles and Privileges Metadata

The ORDS URL mapping, privilege and roles are deleted using the following code. Once this is removed, the web service will be publicly available again. For more information see: - An Introduction to OAuth 2 - Tutorial: Protecting and Accessing Resources - ORDS PL/SQL Package Reference - OAUTH PL/SQL Package Reference - Using OAuth2 in Non-HTTPS Environments - Oracle REST Data Services (ORDS) : All Articles - Oracle REST Data Services (ORDS) : Authentication Enhancements (ORDS_SECURITY and ORDS_SECURITY_ADMIN Packages) - Oracle REST Data Services (ORDS) : Pre-Authenticated Requests - Oracle REST Data Services (ORDS) : Database Authentication - Oracle REST Data Services (ORDS) : Custom Authentication Schemes - Oracle REST Data Services (ORDS) : Installation on Tomcat - Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL - Oracle REST Data Services (ORDS) : AutoREST - Oracle REST Data Services (ORDS) : Configure Multiple Databases - Oracle REST Data Services (ORDS) : First Party (Basic) Authentication on Tomcat - Oracle REST Data Services (ORDS) : OAuth Client Credentials Authorization - Oracle REST Data Services (ORDS) : OAuth Authorization Code - Oracle REST Data Services (ORDS) : OAuth Implicit Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

begin
  ords.delete_privilege_mapping(
    p_privilege_name => 'emp_priv',
    p_pattern => '/employees/*'
  );     

  commit;
end;
/

begin
  ords.delete_privilege (
    p_name => 'emp_priv'
  );
   
  commit;
end;
/

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!