Leverage the Power of Oracle Database From the Browser with ORDS (Part 2)

Now that the web services have been created and integrated into the client side in Part 1 of this series, we can spend some time on the database tier using database features to modify which rows get returned. There will be no more code changes in the client-side portion of the application for the rest of this article; all updates will simply be enabling different database features or slightly modifying the SQL query used in the web service.

Analytic Functions

Analytic functions are a powerful no-cost feature of Oracle Database that allow developers to use sophisticated analytics in their SQL queries and PL/SQL code. Because these calculations occur on the database server, they are highly optimized and can slice through large volumes of data with ease.

Several different analytic functions are supported; a comprehensive list can be found here.

We can easily incorporate analytic functions into the SQL statements that our web service uses. This can be done in such a way that only minor, if any, changes to the user interface need to be made. The heavy lifting, so to speak, will be done at the database tier.

Our current SQL query returns all employees and orders them by salary from least to greatest. This was done using a simple inline query with an ORDER BY clause. If we run this query, we can see that SMITH has the smallest salary while KING enjoys the largest, as shown in Figure 1.

Figure 1: Results of SQL sorting on salary.

But what if we want to find the same information but segment it by department? We could simply run the SQL query for each department, adding a WHERE clause to only look at a specific department, and then use UNION to put all of those queries together. With a small dataset, this approach might work. But for a table with many departments and hundreds, if not thousands of employees, this approach is very impractical.

A better approach is to seek the help of analytic functions and use either the RANK or DENSE_RANK function to produce our results. Both functions will rank a dataset based on some criteria; the difference is that DENSE_RANK will assign consecutive values in the event of two of more values being the same, whereas RANK will not.

Table 1 illustrates the difference in how RANK and DENSE_RANK assign values to a simple dataset:

We'll apply RANK to our SQL query, and use deptno as the partitioned column. Thus, our new query now looks like Listing 1:

SELECT 
  e.empno,
  d.dname,
  e.ename,
  e.job,
  e.sal,
  0 sal_diff,
  NVL(e.comm, 0) comm,
  RANK() OVER (PARTITION BY e.deptno ORDER BY sal DESC) AS rank
FROM 
  emp e,
  dept d
WHERE
  d.deptno = e.deptno

Listing 1: Modified query using the RANK() analytic function.

To incorporate the updated SQL query into the existing web service, do the following:

1. In Oracle SQL Developer, expand the REST Data Services node in the tree. From there, expand Modules, demo, and emp to reveal a leaf node called GET.
2. Right-click GET and select Open.
3. You should now see a Worksheet tab, where the SQL query from the creation of the web service is displayed.
4. Replace the SQL query that is there with the SQL query shown in Listing 1 and save your changes by clicking the single floppy disk icon.

Figure 2: Updating the SQL query of the web service.

Now, the RANK column will contain the rank of an employee's salary within a specific department, not overall across all departments, as shown in Figure 3.

Figure 3: Results of SQL query with RANK analytic function.

The results of the web service call and web page will also reflect the updated results.

We can also apply multiple analytic functions to a single SQL statement. Let's say we wanted to easily see the difference in salary between the current employee and the previous employee in the same department. We can turn to the LAG and LEAD analytic functions to help us with this. LAG will "look back" any number of rows and return that value, whereas LEAD will "look forward" any number of rows and return that value. These two analytic functions eliminate the need to create a complex self-join, and they can be easily implemented in any SQL query.

We can modify our query slightly to include the difference between a current employee's salary and the next-highest paid employee's salary in the same the department by using the SQL query in Listing 2.

SELECT 
  e.empno,
  d.dname,
  e.ename,
  e.job,
  e.sal,
  e.sal - LAG(e.sal, 1, 0) OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS sal_diff,
  NVL(e.comm, 0) comm,
  RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS rank
FROM 
  emp e,
  dept d
WHERE
  d.deptno = e.deptno

Listing 2: SQL query that makes use of two analytical functions: LAG and RANK.

To modify the SQL query used in the web service, do the following:

1. In Oracle SQL Developer, expand the REST Data Services node in the tree. From there, expand Modules, demo, and emp to reveal a leaf node called GET.
2. Right-click GET and select Open.
3. You should now see a Worksheet tab, where the SQL query from the creation of the web service is displayed.
4. Replace the SQL query that is there with the SQL query shown in Listing 2 and save your changes by clicking the single floppy disk icon.

When the SQL query in Listing 2 is run, the SAL_DIFF column is computed using the LAG function. This column represents the difference in salary between the current and previous row. In the case of the highest paid employee per department, the value is the same as that employee's salary, because there is no previous row to compare to. The results of the new query can be seen in Figure 4.

Figure 4: The results of the LAG function, illustrating the differences in salary.

RANK, DENSE_RANK, LAG, and LEAD are just a few of the many analytic functions that you can easily incorporate into your SQL statements—regardless of whether they are called as part of a web service. Read more about analytic functions in the Oracle documentation or on the Oracle-Base website.

Another powerful database feature that can be easily implemented in Data Redaction, which is feature of the Oracle Advanced Security option for Oracle Database. Data Redaction can conditionally redact sensitive data from being displayed to the user. Data can be completely or partially redacted or replaced with random data.

Note: In order to manage redaction policies, your schema will need EXECUTE privileges on DBMS_REDACT. Your DBA might have to assist in granting these privileges.

We can create a simple redaction policy that will apply to our query—all without having to change a single line of code on the client or the server. This policy will completely redact any values in the COMM or commission column. With the policy enabled, any time the EMP table is queried, the value of the COMM column will simply show 0.

To create a redaction policy, do the following:

1. From Oracle SQL Developer, select the SQL Worksheet that corresponds to your schema. You can use the one that was used to create the EMP and DEPT tables in Part 1. If you don't see a SQL Worksheet tab, create a new one by clicking the icon circled in Figure 5.
    

   Figure 5. Icon for opening a new SQL worksheet

2. Enter the following SQL query in the SQL Worksheet tab:
    
   Copy

   
   BEGIN
   DBMS_REDACT.add_policy
     (
     object_schema => 'SCOTT',
     object_name   => 'EMP',
     policy_name   => 'REDACT_COMM',
     expression    => '1=1',
     column_name   => 'COMM',
     function_type => DBMS_REDACT.FULL
     );
   END;
   /

3. Execute the SQL query by clicking the Run Statement icon.
    

   Figure 6.

If the query executes successfully, you will see a message that reads "PL/SQL procedure successfully completed." If you re-query the data from either Oracle SQL Developer or the web service, all values for COMM should read 0, as shown in Figure 7.

Figure 7: The results of the same query after a redaction policy was applied.

Let's keep the redaction policy in force as we enable additional database functionality.

Security is always on the mind of developers today. With incidents of breaches on the rise and no end in sight, it is critical that you ensure that access to your data is restricted to only authorized users. While this article is not intended to serve as a checklist for securing Oracle Database, it would be remiss to not include a portion on security, specifically one that can be easily applied to a query used with a web service.

While Oracle Database has many ways to restrict access to data, one of the more common methods is called Oracle Virtual Private Database—a no-cost feature of Oracle Database, Enterprise Edition that can be easily enabled. This feature aims to hide rows of data that don't match specific criteria. For example, we'll set up a rule that restricts any query on EMP to return only employees of a specific department.

Oracle Virtual Private Database can work in concert with other Oracle Database security features, such as Data Redaction. Once we have our rule in place, the same query that we have been using will return only employees that are in department 30. The redaction rules that we set up—where commission is fully redacted—will still be applied.

Note: To manage Oracle Virtual Private Database policies, your schema will need EXECUTE privileges on DBMS_RLS. Your DBA might have to assist in granting these privileges.

Before an Oracle Virtual Private Database policy can be created, a policy function needs to exist. A policy function is a standard Oracle Database function that will return the WHERE clause that Oracle Virtual Private Database will automatically apply to the query against a specified object. So in our case, all we need to do is return deptno = 30 to limit the query to return only employees in department 30.

Best practices dictate that the policy function be created in a different schema than where your data lives. This can ensure that developers will not be able to modify the function and circumvent its logic. For this example, we'll simply create it in the same schema to keep things simple.

To create an Oracle Virtual Private Database policy function and enable it for the EMP table, do the following:

1. From Oracle SQL Developer, select the SQL Worksheet that corresponds to your schema. If you don't see a SQL Worksheet tab, create a new one by clicking the icon circled in Figure 8.
    

   Figure 8. Icon for opening a new SQL worksheet.

2. Enter the following SQL query in the SQL Worksheet tab:
    
   Copy

   
   CREATE OR REPLACE FUNCTION restrict_by_dept
     (
     owner     IN VARCHAR2, 
     objname   IN VARCHAR2
     ) 
   RETURN VARCHAR2 AS
   BEGIN
   RETURN 'DEPTNO = 30';
   END;
   /

3. Execute the SQL query by clicking the Run Statement icon shown in Figure 9.
    

   Figure 9. Icon for creating a executing the query.

4. Enter the following SQL query in the SQL Worksheet tab, replacing the previous content:
    
   Copy

   
   BEGIN
   DBMS_RLS.ADD_POLICY
     (  
     object_schema   => 'SCOTT',
     object_name     => 'EMP',
     policy_name     => 'RESTRICT_BY_DEPT',
     policy_function => 'RESTRICT_BY_DEPT',
     statement_types => 'SELECT'
     );
   END;
   /

5. Execute the SQL query by clicking the Run Statement icon.

If you simply reload the web page now, only employees in department 30 will be returned in both the report and the chart, as illustrated in Figure 10.

Figure 10: The report and chart after a rule restricting data to the sales department was applied.

Note that the commission values are still being redacted, and the employees are still sorted from most paid to least paid, per the RANK analytic function. Differences in salaries are still being displayed, per the LAG analytic function as well. Not a single line in the HTML file or even the JavaScript file was changed.

Also, Oracle Virtual Private Database applies to anytime the database queries the EMP table. This means that data will be restricted when we view both the form and the report. If you edit any of the employees in the SALES department, notice the URL in your browser. It should look something like this:

The last parameter is the EMPNO of the user that we're editing; in this case, BLAKE. If we want to tamper with the URL and try to see KING's record, all we need to do is change that last parameter to 7839 and reload the page, right? Wrong.

As shown in Figure 11, if we try to view any employee who is not in the accounting department, we'll simply get a blank form. Oracle Virtual Private Database is doing its job by protecting the data, regardless how it is accessed.

Figure 11: The results of trying to alter the URL and view an employee not in the accounting department.

Auditing

As a final step, we can enable auditing to record each time the query used in the web service was accessed. This will allow us to monitor its usage and ensure that it is being run by expected clients for authorized purposes. In fact, auditing can be configured for almost any transaction in Oracle Database; both data definition language (DDL), for creating and modifying objects, and data manipulation language (DML), for querying and modifying data, are supported.

Note: In order to manage audit policies, your schema will need EXECUTE privileges on DBMS_FGA. Your DBA might have to assist in granting these privileges.

In our example, we can use something called fine-grained auditing, which allows us to specify a condition that will be triggered when an object is audited. We might only want to audit the EMP table if an employee of a specific department is included in the results or if the value of a salary exceeds a specific value.

To create an audit policy on EMP that will audit any transaction where the salary of an employee is updated to be greater than 4000, do the following:

1. From Oracle SQL Developer, select the SQL Worksheet that corresponds to your schema.
2. Enter the following SQL query in the SQL Worksheet tab:
    
   Copy

   
   BEGIN
   DBMS_FGA.add_policy
     (
     object_schema   => 'SCOTT',
     object_name     => 'EMP',
     policy_name     => 'WEBSERVICE',
           statement_types => 'INSERT,UPDATE',
     audit_condition => 'SAL > 4000',
     audit_column    => 'SAL'
     );
   END;
   /

3. Execute the SQL query by clicking the Run Statement icon.

Now, edit any employee and set their salary to a value less than 4000 and save the changes. Because the new salary is less than 4000—which is the threshold defined in the rule—nothing was written to the audit logs. Now, edit any employee and set the salary to 4001 or greater. Because the threshold of 4000 was exceeded, an entry was written to the audit log.

If you're using Oracle Database 12c, the audit logs are written to the new unified audit table, UNIFIED_AUDIT_TRAIL. This new view consolidates all the older audit views into a single place. If you're still on Oracle Database 11g, the audit logs will be written to the standard DBA_FGA_* tables.

To view the audit logs in Oracle Database 12c, as the SYS or SYSTEM/PDB_ADMIN user, issue the following query:

SELECT * from unified_audit_trail WHERE fga_policy_name = 'WEBSERVICE' 
  ORDER BY event_timestamp desc

To view the audit logs in Oracle Database 11g, as the SYS or SYSTEM user, issue the following query:

SELECT * FROM  dba_fga_audit_trail WHERE policy_name = 'WEBSERVICE' 
  ORDER BY timestamp desc

In either case, many columns will be returned. If you look closely, you should be able to spot the SQL query that was executed, as well as the schema and table that were impacted. 

Disabling Functionality

At the conclusion of this article, you might want to reset your schema to how it was before different database features were enabled. To do that, simply run the corresponding SQL commands in Oracle SQL Developer.

Redaction
To disable the redaction rule, execute the following:

BEGIN
DBMS_REDACT.drop_policy(
  object_schema => 'SCOTT',
  object_name   => 'EMP',
  policy_name   => 'REDACT_COMM');
END;
/

Oracle Virtual Private Database
To disable Oracle Virtual Private Database, execute the following:

BEGIN
DBMS_RLS.DROP_POLICY(
  object_schema   => 'SCOTT',
  object_name     => 'EMP',
  policy_name     => 'RESTRICT_BY_DEPT');
END;
/

Fine-Grained Auditing
To disable fine-grained auditing, execute the following:

BEGIN
DBMS_FGA.drop_policy(
  object_schema   => 'SCOTT',
  object_name     => 'EMP',
  policy_name     => 'WEBSERVICE');
END;
/

Summary

No matter what front end you prefer, using Oracle Database as a back end provides a wealth of robust, mature, and easy-to-use functionality that can be tailored to suit your individual needs. No longer do you need to learn and incorporate complex libraries to help with analyzing and securing your data; all of this can happen by using native functionality of Oracle Database, regardless of the front end.

About the Author

Scott Spendolini is president and founder of Sumner Technologies, a world-class Oracle services, education, and solutions firm. Throughout his career, he has assisted clients with their Oracle Application Express development and training needs. Spendolini is a long-time, regular presenter at many Oracle-related conferences, including Oracle OpenWorld, Kscope, and Rocky Mountain Oracle Users Group (RMOUG). He is an Oracle Ace Director, the author of Expert Oracle Application Express Security, and a coauthor of Pro Oracle Application Express. Spendolini is also an Oracle Certified Oracle Application Express developer. Spendolini started his career at Oracle Corporation, where he worked with Oracle E-Business Suite for almost seven years and was a senior product manager for Oracle Application Express for over three years. He holds a dual bachelor's degree from Syracuse University in management information systems and telecommunications management.