Databases Reference
In-Depth Information
You've practically defined that if the current user had the username greg, then it would return
something (here you simply return 1 but it could also be abc if you desire, as long as a single record was
returned). Hence the authorization scheme you created in this recipe can be interpreted as: if the
current user has the username greg, he should be granted access to the element.
Based on this simple concept, you can apply very complex access rights control to your application.
For instance, you can create an Employee report that shows all columns to a manager, but hides the
Current Salary column when a normal clerk views it.
Tip
The hardcoding of data (such as username greg) in the authorization scheme is for purpose of
demonstration and is certainly not encouraged. You would usually do something more meaningful in the
authorization scheme, such as checking if a user is a manager or a Head of Department (against another database
table and so on).
As a side note, the concept of authorization schemes also promotes reusability and ease of
maintenance. It is reusable because you can reuse the same logic for multiple elements in your
application without rewriting the same logic many times. More importantly, this makes it easier for you
to maintain your application. For instance, if the logic changes one day, such that you need to include an
additional check in your PL/SQL, you can just change it at one location, and it will instantly be applied to
all elements that use the said authorization scheme.
8-3. Preventing SQL Injection Attacks
Problem
You have a dynamic report showing the list of customers in the system. By default, your application
requires the end user to specify the customer name before it retrieves the matching customer from the
database. An APEX hacker has managed to retrieve the full list of all customers in the database via an
SQL injection attack. You want to protect your application against similar attacks in the future.
Solution
First, you need to set up the sample tables and forms needed to duplicate the scenario for the attack. To
do this, please follow these steps:
1.
Create the table shown in Listing 8-5 if it doesn't yet exist in your database.
Listing 8-5.
Sample Customers Table
CREATE TABLE "CUSTOMERS"
(
"ID" NVARCHAR2(255) NOT NULL ENABLE,
"NAME" NVARCHAR2(255),
"ADDRESS" NVARCHAR2(2000),
"ZIP_CODE" NVARCHAR2(6),
"COUNTRY" NVARCHAR2(255),
