By Scott Spendolini
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 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:Table 1. Comparison of RANK and DENSE_RANK values
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:
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:
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. Details about Data Redaction can be found here.
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
To create a redaction policy, do the following:
Figure 5. Icon for opening a new SQL worksheet
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; /
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.Oracle Virtual Private Database
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:
Figure 8. Icon for opening a new SQL worksheet.
CREATE OR REPLACE FUNCTION restrict_by_dept ( owner IN VARCHAR2, objname IN VARCHAR2 ) RETURN VARCHAR2 AS BEGIN RETURN 'DEPTNO = 30'; END; /
Figure 9. Icon for creating a executing the query.
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; /
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.
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.
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:
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; /
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.
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.
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; /
To disable fine-grained auditing, execute the following:
BEGIN DBMS_FGA.drop_policy( object_schema => 'SCOTT', object_name => 'EMP', policy_name => 'WEBSERVICE'); END; /
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.
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.