Oracle Security: A Definition in Plain English, Part 3
Regardless of any regulations you are required to meet you can implement a security and audit scheme that will cover you for most, if not all, eventualities. Having good security and auditability should be desirable regardless of regulations. It protects your business.
In parts 1 and 2, I covered the security side of it. So what do I mean by Audit? Well, auditing is knowing what’s going on in your environment. Future research requirements, accountability, error correction, etc are all covered by audit.
So how does Oracle implement audit? There are several ways you can do it: Standard Audit, Fine Grained Audit and Trigger Based Audit.
Standard Audit: Standard auditing is the basic audit capabilities of Oracle. Without going too far into it, Oracle will allow you to audit the actions of certain users, certain objects or certain permissions in the database. It’s highly configurable and can write to a table in the database or to a file in the operating system. This method is very performant as each statement is audited in the parse phase of execution. The downside to this efficient form of auditing is that the data you can capture is limited to predefined columns.
Fine Grained Audit (FGA): FGA is content based auditing. For example, you can audit a select statement on the EMP table where someone queries all records with a SAL > 10000. In Part 2 I described fine-grained access control (FGAC), which was defined by DBMS_RLS. FGA is much like that. It is defined by a package called DBMS_FGA. You add a policy, for select, update, insert, or delete and define which columns and conditions to monitor. In the policy, you also identify the packaged procedure to execute when the policy condition is met. The entire process looks a lot like the security policies I describe in Part 2.
Trigger Based: This is the good old boy of auditing. Why use triggers? Some DBAs will not turn on auditing because of FUD (Fear, Uncertainty and Doubt). Or, maybe you need some very specific information not easily captured otherwise. Anyway, the way to implement this is to create an audit table, usually looking just like an existing table and put an “insert and update and delete” trigger on it to always copy the existing record to the audit table. You can save some i/o by just using an “update and delete” trigger. You already know what the inserted record looks like since it’s in the master table.
So, when do you use which form of audit? That’s actually a fairly easy issue to decide. Standard audit is going to be the best performing. It can catch general data fishing expeditions (credit card info for example) as well as people trying to execute permissions beyond their grants or against objects they shouldn’t be. For regular proof of access type auditing, i.e. SOX or CMM, standard auditing should be the preferred method.
If you need to track access by content, use FGA. If you want to monitor who is looking at executive salaries, use FGA. If the requirement can be spelled out in a SQL where clause, use FGA. If standard audit can answer the requirement, do not use FGA.
And finally, in 10g, I see no reason to use trigger-based audits. Between the functionality provided by standard audit and FGA, triggers provide nothing new. They are also the least performant and the most expensive to maintain.
Best Practice #1: Include the audit team at design time. Many times, there seems to be a confrontational relationship between IT and Audit that I don’t really understand. In the end, you MUST meet your audit requirements. Deal with those requirements at design time and you will have no problems. Deal with it after implementation and suffer pain. Seems like a no brainer to me.
Best Practice #2: Plan for security at design time. Like audit, post-implementation security is painful and will usually require rework.
Best Practice #3: Only audit what needs to be audited. There IS overhead with auditing. If you need to audit, then you need to audit. Listen to your audit team, implement the auditing requirements identified and create a plan to cleanup audit trails on a regular basis.
Best Practice #4: Secure everything. Lock down the database. No one gets access to anything without a reason. Start from here and work outwards.
Best Practice #5: Protect the audit trail. If you store the audit in the database, audit the audit trail. If you store it in the OS, protect the file system. In addition, if you use triggers, do not grant to the audit table, audit select, update and delete on the audit table and put triggers on the audit tables preventing update and delete. Also, use the parameter AUDIT_SYS_OPERATIONS = TRUE to force an OS audit for all activity by the DBAs when that’s required. DBAs are normally exempt from fine-grained access and from auditing.
Best Practice #6: In an outward facing application with thousands of user, you have very unique security requirements. I’ve found that it’s best to implement security for these through stored procedures. An application user, shared by many and authenticated by proxy, would have access to a very specific set of procedures. Those procedures perform any database activity required and return result sets for viewing.
Best Practice #7: For an inward facing application, and those with a finite number of users, every user should have their own database account. I have heard all of the arguments for sharing IDs and having the application maintain the users and they are all a load of, putting this politely, manure. Put security in the database, implement it once and sleep soundly at night.
Best Practice #8: Implement the user creation and role granting/revocation process through stored procedures. Give access to these procedures to the application administrator. That will allow creating users with consistent access across an application with out having to grant these powerful permissions directly. It also allows the DBA to offload basic application security.
Best Practice #9: Never grant direct access to anyone in production. Always grant access through roles. The only user that needs direct access is the schema that needs to compile procedures and views and that user should be locked once the application is compiled.
Best Practice #10: Use secure roles. Secure roles are roles that use a specified stored procedure to be enabled. That is, when the application says set this role for this user, it will execute a pre-defined stored procedure to validate that this person really does have access to this role, in this context. Think of secure roles as Fine Grained Roles (I just made up that term so only use it in this context). It’s a procedurally implemented role.
Best Practice #11: Use fine-grained access when data needs to be secured at the column level. Use column masking to hide sensitive data.
Best Practice #12: Create and enforce a password policy. Oracle has the ability to execute a stored procedure to verify that a user’s password meets your policy requirements. Oracle also maintains a password history and you can define how soon a password can be reused.
Best Practice #13: Use account locking. Set a number of retries for failing a password and then lock the account. You can set it to automatically unlock after a predetermined amount of time or force the user to call an administrator. This is to prevent brute force access.
Best Practice #14: Encrypt when needed but only when needed. If you store credit card numbers, bank account information, social security numbers, etc, you may want to encrypt that data. Oracle provides robust encryption in the form of the DBMS_CRYPTO package.
And I think that covers most of it. I’m sure I skipped some things and glossed over others. These best practices are enough to get started. Some things that came to mind while writing this are a technical discussion of multi-tier authentication, best practices for developer access, an audit walkthrough like I did for security in Part 2 and a complete scenario using secure roles and user and grant creation through a procedure. If you would like to see any of that, let me know.
And finally, here is an article I liked and thought you might like also.
That’s my story and I’m sticking with it!
You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.