We all need to secure sensitive information, and sometimes we just don't know where to start. So, I recommend you start at the beginning. The first thing you need to do is identify all of your sensitive information and use the tools that Oracle provides to secure for attack vectors.
I'm going to start this series by going through and protecting credit card numbers as they are used in applications. I have yet to hear of a business needing to display an entire credit card number. At best, a user may need the last four digits.
Sys_context returns information on a user's environment such as IP address, user name, and how the user authenticated to the database. You use this in conjunction with redaction, fine-grained auditing, virtual private databases, and populate custom context with login triggers.
For example: We only want users to access sensitive information if they have logged on from approved ip addresses. If a user is outside of those IP addresses, we want a virtual private database policy that will append where 1=2 to the where clause so that no data is returned. This reduces the attack surface to a subset of workstations that is more easily managed.
Code listing 1:
NOTE: You can expand this to include an IP address privilege table. We are denormalizing this to keep it simple.
Create a table in the security schema to hold IP addresses and what the user is allowed to see. For each IP address, we have flags for ccnbr, ssn, and customer access. If a user has customer access but does not have ccnbr, then those columns will be redacted. If the user does not have customer access, where 1=2 will be appended to the where clause, causing no rows to be returned.
Code listing 2:
Create a context
The customers_ctx context will have three names spaces: ccnbr, ssn and customers. For each of the name spaces there can be two values, Y or N. It's simple as that. We also need to create a package to control how the context is populated. With only one way to populate the context, again you have reduced the attack surface making it more difficult to get around your defenses.
Code listing 3:
There is only one procedure exposed in the package spec: set_context. This procedure will set all the context for customer security by calling the function has_ccnbr, which returns a boolean, so if the user has ccnbr set to 'Y' in the ip_addresses table, the user connected using SSL, and the user has the CCNBR privilege, then the user can access the credit card number. If sys_context(customers_ctx, customer) = 'Y,' then the user will be able to access the customer's table. You can easily expand this to limit the rows that get returned to a subset of the customer's table.
Code listing 4:
In order to populate the custom context, we call cust_control_pkg.set_context from a logon trigger that is in the security schema. There is an importunate note. If the logon trigger has errors, then users will not be able to connect to the database. For this reason, we always have an exception handler to trap any errors that might occur.
Code listing 5:
Now we are going to create a Virtual Private Database Policy using the dbms_rls.add_policy procedure. In this case, we are naming the policy cust_policy, which is owned by security that calls the function cust_vpd. This policy will fire every time there is a select, update, or delete against the customer's table.
The function cust_vpd is what is used to generate the where clause. In this example, if the context customers_ctx, customers is 'N,' the function will return with where 1=2, thereby returning nothing to the user. Otherwise the function will append to the where clause, a query that will limit the number of rows being returned to the what the user is allowed to see. This can easily be expanded so a superuser can see all of the rows by adding another context.
