APEX Basic REST authentication

July 25, 2017 at 2:00 pm | Posted in Oracle Developement | 5 Comments

To prevent unauthorized access to your REST modules, APEX provides an easy, declarative way of adding basic authentication. This method utilizes APEX user accounts and RESTful Service Privileges. In order not to expose passwords, you should make sure to enable SSL/HTTPS!

First edit an existing APEX user account, and add the RESTful Services group to that user.  In our example we’ll edit the APEX user SCOTT. To do this, navigate to Manage Users and Groups in the workspace where your REST modules are, edit a user, and assign the group in the Group Assignments region:

group_assignment

Next create the RESTful Service Privilege by navigating to RESTful Services in the SQL Workshop. In the Tasks sidebar on the right, click the RESTful Service Privilege link.

rsvc_priv

Fill out the form and select the modules you want to protect, by shuttling them to the right in Protected Modules.

rsvc_details

That’s it! Everything under the selected module now requires the username and password of the APEX user. You can test this with the following curl command (edit URI as needed):

curl -u scott:tiger http://servername/ords/workspace/hr/empinfo/

When testing with Postman, choose the Basic Auth from the authentication select list and enter the APEX user’s username and password.

postman

 

Oracle OPC Firewall Rules

July 13, 2017 at 4:30 pm | Posted in Oracle Developement | Leave a comment

Create Firewall Rules

Managing firewall rules on Oracle OPC can be a bit confusing. In this example we want to open up ports 80 and 443 to our compute instance, in order to allow the web server there to be accessed by the public internet.

There are four elements to a firewall rule:

  1. Security List – a named container for Security Rules. A security list can have multiple rules assigned to it. Multiple security lists can be assigned to an instance.
  2. Security Application – an port range and an associated protocol.
  3. Security IP List – a comma separated list of IP address that should get access to your instance. Using 0.0.0.0/0 specifies the public internet.
  4. Security Rule – says which port range (application) is assigned to an IP list (source) and to which security list (destination) this assignment belongs to.

To create an inbound firewall rule, create a rule for a Security Application and specify the Security IP List (in the Source filed) for which addresses can get through the ports in the Security Application. Then assign that rule to a Security List, specified in the Destination field.

diagram.png.001

Let’s go through the steps. Let’s assume we already have a compute instance with our web server on it. Log into your Oracle OCP cloud account dashboard, and click the Compute region. From there click the Open Service Console button. Here you should see your existing compute instance under the Instances tab.

instances

Click the Network tab where you should see existing security rules, if any. In the left navigation pane are the various elements we need for the firewall rule.
network_tab

 

Click the Security Lists link and then the Create Security List button to create a Security List. Security lists get assigned to instances and map the various ports and IP addresses to that instance:
security_list

Click the Security Applications link and then the Create Security Application button. We’ll create an application for port 443.
security_application

Click the create button again to add another security application for port 80 to allow http access:
security_application80

Click the Security IP Lists link and then the Create Security IP List button to create a security list which determines the IP addresses that can get through. Using 0.0.0.0/0 allows all IP addresses.
security_ip_list

Now map the Port(s) to the IP list and the security list. In other words, open up port 443 to the internet for the cmr_internet security list. This is done by creating a Security Rule. This rule basically says allow port 443 to be accessed by the public web, and assign this rule to the security list cmr_public_web. Below the security list will be assigned to the instance. Note that this is an inbound policy, meaning that this is for traffic from the internet to your compute instance. If you were to reverse the source and destination values, it would become an outbound policy.

Quick note about inbound and outbound:
Inbound and outbound policies need to coincide with the policies of the Security List. A rule only works if the corresponding policy is set to Deny. In regular language this would mean: “Deny everything except what’s in the security rules.” If the policy is set to Permit, it means “Let everything through.”

Click the Security Rules link and then the Create Security Rule button. Then map the Security Application to a Source and a Destination.

security_rule

Create another rule for the port 80 application and assign it to the same security list:
security_rule80

Now we can see that two rules are assigned to a single security list. One for HTTP and one for HTTPS. Both can be accessed via the public internet:
two_rules

Finally we assign this rule to our compute instance to allow our web server there to serve pages to the public internet via HTTP and HTTPS.

Switch back to the Instances tab, click the instance you want to assign the Security List to, and click the Add to Security List button:
add_list_to_instance

Repeat these steps to create a rule for SSH access on port 22, so that you can access the instance through a terminal. The final result should look like this:
security_lists_assigned

At this point the firewall settings are ready to go. The compute instance can now be accessed via the internet on ports 80 and 443. You can now add or remove other security lists, or add/remove rules from a particular security list to fit your needs.

 

APEX Interactive Grid: JavaScript Basics Cheat Sheet

July 3, 2017 at 1:52 pm | Posted in Oracle Developement | Leave a comment

Nice cheat sheet for APEX Interactive Grid JavaScript

TH TECHNOLOGY

APEX Interactive Grid can be customized by JavaScript in the Advanced –> JavaScript Code attribute of the Grid or a Grid column.  Yup – JavaScript.

JavaScript may be out of the comfort zone for PL/SQL developers, even those who implemented extensive tabular form customization working with PL/SQL collections.  Time to say Goodbye to those collections … Welcome JavaScript!

The following is a collection of simple JavaScript lines most likely to be needed by a developer wanting to customize an Interactive Grid, or access data elements in the Grid.

This is not a comprehensive list or a complete function – just a simple collection of lines to give you an idea of the process and examples of  – a reference for – the syntax.

JSRefLines

Taken line by line:

var $te = $(this.triggeringElement);

This line gets the triggering element – the element that caused the dynamic action (DA) to fire. Usually we…

View original post 537 more words

ORDS REST error after new installation

July 1, 2017 at 4:50 pm | Posted in Oracle Developement | Leave a comment

After installing and configuring ORDS 3.0.10, I noticed that APEX ran fine, but when testing the sample HR REST module I received the following error:

URI: http://myserver:8080/ords/cmr/hr/empinfo/

Error:
The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured

resterror

The reason was that the table ords_metadata.apex_pool_config was missing the column pool_name. To fix this, I simply had to run the ORDS validation:

java -jar ords.war validate

This added the missing column, and the error went away.

I found the answer to this on OTN.

ORDS REST: Getting Bind Variables Right

June 20, 2017 at 3:38 pm | Posted in Oracle Developement | Leave a comment

It seems every time I create a REST GET module I have to re-learn how to handle bind variables. So I decided to create a little cheat sheet.

When creating a REST module with a GET handler, you can pass variables wither through the URI or through header variables:

URI Variables:

URI

The URI template needs to have the bind variables in curly brackets (comma separated if multiple binds):

testuri/{id},{ename}

Create a query:

select * 
  from emp
 where empno = :id
   and ename = :ename

Create a parameter for each bind variable:
parameters

I don’t think whether if the Source Type matters. HTTP Header has worked for me. I typically set the parameter name and bind variable name to upper case to avoid any problems.

Test:

Click the Set Bind Variables button to test the handler. Make sure to add a value to all parameters. If you leave a parameter empty, you’ll get an error.

 

Header Variables:

Create the URI without a trailing forward slash:

header

Create the query and parameters as before.

Test:

You’ll have to test this with a REST client like POSTman or CURL:

$ curl -H "id:7788" -H "ename:SCOTT" http://myserver:8080/ords/cmr/hr/testheader

Note when using header variables, you can leave some out without getting an error.

Happy RESTing!

 

 

 

APEX Interactive Grid Processing with Dynamic Actions

May 18, 2017 at 6:39 pm | Posted in Oracle Developement | Leave a comment

Here is a cool post on triggering dynamic actions from an interactive grid: http://lschilde.blogspot.com/2017/05/apex-51-interactive-grid-row-processing.html

Validating JSON in the database

April 24, 2017 at 6:04 pm | Posted in Oracle Developement | 1 Comment

Creating REST services and consuming JSON inside the Oracle database has become quite easy. The APEX_JSON package provides a number of useful procedures and functions to work with JSON. However, one thing I haven’t found is a way to validate JSON before parsing it.

To my knowledge the is not yet a PL/SQL way to check whether a JSON string is valid, i.e. to make sure that all the brackets, commas, colons, etc. are there. There is however, a column check constraint you can use in 12c that will only allow valid JSON to be inserted into a table. So here is a quick workaround for validating JSON using the check constraint: I simply take the JSON and attempt to insert it into the table. If the check constraint is violated, I’ll get an error.

I wrapped to whole thing in a function that will then simply return a boolean value for the JSON passed into it.

create table json_validator ( 
	 id   number generated always as identity primary key
	,json clob
	,constraint validate_json check (json is json format json)
);

create or replace function validate_json (l_json in clob)
return boolean
is
  l_id pls_integer;
  e_invalid_json exception;
  pragma exception_init (e_invalid_json,-2290);
  pragma autonomous_transaction;
begin
	insert into json_validator (json) values (l_json);
	rollback;
	return true;
exception
	when e_invalid_json then
	  return false;
end;
/

set serveroutput on
declare
  l_json  clob;
  l_return boolean;
begin
  l_json := '
				{"widget": {
					"debug": "on",
					"window": {
						"title": "sample konfabulator widget",
						"name": "main_window",
						"width": 500,
						"height": 500
					},
					"image": { 
						"src": "images/sun.png",
						"name": "sun1",
						"hoffset": 250,
						"voffset": 250,
						"alignment": "center"
					},
					"text": {
						"data": "click here",
						"size": 36,
						"style": "bold",
						"name": "text1",
						"hoffset": 250,
						"voffset": 100,
						"alignment": "center",
						"onmouseup": "sun1.opacity = (sun1.opacity / 100) * 90;"
					}
				}}    
  ';

  l_return := validate_json(l_json => l_json );

  if l_return then 
	dbms_output.put_line('JSON is valid');
  else
	dbms_output.put_line('JSON is NOT valid');
  end if;

end;
/

How to use Font Awesome Animation Library in Applications

April 11, 2017 at 6:10 pm | Posted in Oracle Developement | Leave a comment

A very nice blog post about using Fontawesome animations by :

http://apexfusion.blogspot.com/2017/04/how-to-use-font-awesome-animation.html

 

Expose Yourself with ngrok

April 7, 2017 at 9:01 am | Posted in Oracle Developement | Leave a comment

Need to expose a web application behind your firewall to the web? No problem with ngrok. Simply download ngrok and tell it which local port to listen to, and it will provide you with a publicly accessible URL through which you can reach your application.

I’ve tested it with a simple Node.js Hello World application running on my laptop on port 3000 behind my firewall. I then started ngrok with this simple command:

$ ngrok http 3000

Ngrok responded with my new URL and also logged all requests.

ngrok

This is a great tool for development and testing, but definitely use with caution! 😉

 

Live JavaScript logging with Quokka

April 3, 2017 at 1:38 pm | Posted in Oracle Developement | Leave a comment

If you’re using Visual Studio Code for writing JavaScript, the handy Quokka plugin will allow you to preview values at design time.

Add the Quokka extension via the Extensions Marketplace in VS Code.quokka_ext

Then, from the command palette, either create a new JavaScript file through Quokka, or start Quokka for an existing file.quokka2

Now you can attach the special comment /*?*/ to a variable to preview it’s value. Make sure that the comment touches the variable. The preview values will magically show up in the editor and the output console.

quokka3

Here is a live view:

quokka

 

Next Page »


Entries and comments feeds.