Oracle 10g+ Security and Audit – Part 1
This is a three-part definition of Oracle Security (specifically in 10g but applies to later versions also). Part 1 covers the various types of security Oracle provides. Part 2 deals with Users/Schemas, Roles, Permissions and Data Access. Part 2 will be a more technical discussion than parts 1 or 3. In part 3, I will discuss implementing an Oracle auditing scheme and how to ensure you comply with security and audit regulations.
What exactly do I mean by security? There are several aspects of security in an enterprise work place: locked doors, guards, cameras, etc. There are just as many aspects when protecting enterprise data. Oracle provides security in four ways: authentication, authorization, data access and audit.
- Authentication is a verification that you are who you say you are. It’s the equivalent of showing a guard your ID. By default, Oracle implements authentication through a user ID and password; this is called database authentication.
Another method of authentication is OS authentication. Using this method, a user logs into the OS and Oracle will accept OS that authentication as gospel. Sometimes you will hear OS authentication referred to as OPS$ (pronounced “ops dollar”) accounts. When you use OS authentication to login into sql plus, the command line looks something like “sqlplus / “. When you use a “/”, Oracle looks for an ID that matches your OS login ID and prepends an OPS$ to it. So if your OS account is lcunning, your Oracle account would look like OPS$lcunning. The OPS$ is just a default and can be changed.
OPS$ accounts provide an additional benefit. OS scripts can log into the database without hard coding user names and passwords. This enhances overall database security.
In my experience, database and OS authentication are the most common authentication methods. However, I have run into several other methods. You can tie authentication to third-party providers like Kerberos or DCE (called network authentication) or provide it from the middle tier (called multitier authentication).
Network authentication is more expensive as you must be running Oracle Enterprise server and license an additional security pack.
There are several security issues relating to multitier security due to its distributed nature. The client must authenticate to the middle tier and the middle tier must authenticate to the database. Also, because multiple users are sharing a connection, the database must be able to distinguish the application from the user and one user from another. I will write a future article dealing just with Multitier security.
- Authorization is giving access to certain objects, processes or resources. The equivalent in our enterprise analogy would be a key card allowing access to a specific room or the having the pass code to the VIP restroom (I want one of those. I bet all the seats have felt covers and it smells like roses and lavender in there). The thought here being that management has granted you access to these rooms or resources.
Authorization is implemented using GRANTS on objects to ROLES or USERS. Let’s say your ID is JohnD. Your friendly neighborhood DBA can grant you SELECT access on the EMP table owned by SCOTT. Or not. You never can tell about those DBA people. But if the DBA will grant you that access, from that point on you can select from SCOTT.EMP. Or the DBA can create a ROLE called EMP_DEPT and grant SELECT on EMP and DEPT to EMP_DEPT. If he then granted the emp_dept role to your ID, you would be able to select from both the EMP and DEPT tables.
So the short story here is that authorization is getting access to specific objects. Oracle implements authorization via users, roles and permissions. Combined with Oracle’s offerings for data access, this provides for a very secure database. This will be covered in more detail in part 2.
- Data Access is the ability to access specific data values associated with an object. Data access security can be partitioned across records and/or across columns. Keeping with my previous analogy, if the guard let you into the building and your key let you into the VIP restroom, your access will only allow you to use the unlocked stalls. Sometimes it feels like data access is security that allows you to use the stall but not the toilet paper (TP) dispenser. Ok, that really is a bad analogy but it’s the best I can think of at the moment. But, I think that gets the point across.
Stored procedures are a common method to secure data. If all access is through stored procedures, it’s easy to return only certain columns or records to the calling program. However, if you have a requirement for ad hoc access to data, this is not realistic. I do recommend that all programmatic data access be implemented using procedures. In addition to securing the data, it protects the application by hiding the structure of the data. But that’s a whole different article.
The common method to secure data in specific columns is through secure views. Instead of granting access to a table, you can create a view and grant access to that. The view can contain all or a subset of the columns in a table. You would want to use the WITH CHECK OPTION constraint if the view allows updates or deletes and you’ve restricted access to certain rows.
In older versions of Oracle, record level access was also implemented using secure views. In more recent versions though, Oracle has introduced Virtual Private Databases (VPD) and, even more recently, Fine Grained Access Control (FGAC). This is implemented using a package called DBMS_RLS (RLS = Row Level Security) and security contexts. The package basically let’s you put a where clause on any combinations of DML and SELECT so that when a user queries the database, the where clause is added behind the scenes. The context could be a department ID or day of week or any other criteria you would like to use to secure the data. The actual building of the where clause is done with PL/SQL via a secure package. I will also cover this in more detail in part 2.
- Audit is keeping track of who did what and when. Security cameras and tracking electronic key cards through a building completes my analogy. Or to really complete it, a sensor showing how much TP was used. Hey, when I find a bad analogy, I stick to it!
Obviously, an audit trail is used for after the fact research and will not protect your data. It will let you know what someone is doing with or to your data.
In older versions of Oracle (and even now for some reason, some DBAs want to use the old methods), auditing was implemented using triggers. For DML, a trigger would fire and copy data to an audit table or audit log. You can still implement audit that way but there are downsides to it. The most basic is that a trigger can easily be disabled and without correct permissions in place, audit tables can be modified. Triggers also do not fire on SELECT so you are not able to track what data is being selected by whom.
Oracle has provided built-in audit capabilities for many years and in the most recent versions this auditing is very sophisticated. The Fine Grained Auditing (FGA) that you can use now can provide audits for all DML against a database, a schema or specific objects. FGA can also audit who was selecting data and not just who updated data. Oracle can also track who changed database objects (DDL, like alter table or compile procedure), what they did and when they did it.
Trigger based auditing requires extensive coding and testing. Depending on the detail you need, Oracle Audit can be implemented with little to no coding and testing is easier because of that.
FGA is implemented using a package called DBMS_FGA. Using DBMS_FGA you define rule based events that can store off data or even email or page someone that will identify what rule was broken and who broke it. The rules can be table driven or even driven by the client, tier or OS environments. FGA combined with LogMiner can actually reproduce the exact records returned by a user’s query.
With regulatory requirements like HIPAA and Sarbanes-Oxley, security and auditing can no longer be an after-thought. In part 2 I will show how to use and implement Oracle roles, secure views and VPDs. In part 3 I will build on part 2 and cover auditing in detail and show how you can use native Oracle features to comply with regulatory security and auditing requirements. I won’t be going into detail about specific regulations; I’ll leave those details to the business people and lawyers. I will be going into detail about the database features that will help ensure that you are covered now and in the future.
And thus ends part 1. I hope it was useful.
You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.