Oracle Security: A Definition in Plain English, Part 2

This is the second entry of a three part series. In part one of this series, I defined what security is and what the different aspects of security in Oracle entailed. In this entry, I am going to show the technical details of implementing security in Oracle. I will create a user, give that user access and then give an example implementation of row and column level security. In part three, I will add auditing to this mix and describe some security best practices, both to protect your data and to meet regulatory requirements.


I’m going into this level of detail because I think some people are not familiar with it and it seems mysterious. It’s not. Like everything else in the database, it pays off in the long run to analyze and refine your requirements before you implement but in the end, it’s just some commands.

I’m not going to talk about regulatory compliance in the entry. I’ll save that for the next post when I’m covering the best practices for security and auditing.

If you’re not particularly interested in the technical details, you really can skip this entry and wait for part three. This one is fairly long and gets very technical.

Are you ready? Here we go!

We need to create an account for Jane Doe. Ok, following our naming standards (and we do have naming standards, right?), you will be JDoe in the application.


CREATE USER jdoe
IDENTIFIED BY “Strong1!Password”;

GRANT connect TO jdoe;

If we wanted to create this user to use OS authentication, we would do:


CREATE USER jdoe IDENTIFIED EXTERNALLY;

GRANT connect TO jdoe;

“CREATE USER”, oddly enough, creates a user. By default, Oracle usernames and passwords are not case sensitive. I’ll talk more about fixing that in part three.

“IDENTIFIED” describes the password. Using the keyword “BY” tells oracle that you will define the users password, “Strong1!Password”. “EXTERNALLY” lets oracle know to create the user with out a local password and to accept the validity of the user if the OS authenticates the person.

What is a grant? A grant allows something. “GRANT CONNECT” is a special permission that allows the user to connect to the database. At this point, jdoe can do nothing but login. If we said “GRANT SELECT ON emp”, assuming that a table or view named emp existed, jdoe would now be able to select records from that object. REVOKE is the opposite of GRANT. “REVOKE connect FROM jdoe” is an important command that we will talk more about next entry.

So now we have a USER named jdoe. What’s the difference between a user and a schema? I have always thought that it was kind of confusing that people seem to use user and schema interchangeably. A user is not the same as a schema. A user is an ID that allows access to a database and a schema is all of the objects created using that ID.

What is a role? A role is similar to a user. The biggest differences are that a role cannot login to the database and a role cannot own objects in the database. A role is just an object that can contain permissions. You can GRANT the role access to a table or a view or give the role the ability to execute a stored procedure. Nothing can happen with that role until you grant the role to a user. Once you grant the role to a user, that user inherits all of the permissions assigned to the role.

So how do we secure our data? Let’s assume that we have a table, EMP, and that table contains sensitive information. Here is the definition of EMP:


SQL> desc EMP
Name Null? Type
——————– ———— ————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Jdoe should not be able to see the SAL or COMM columns. This is easily done.

In case we have already granted anything on EMP to jdoe, we’ll first revoke access to it.


REVOKE all ON EMP FROM jdoe;

jdoe no longer has access to EMP.

Now let’s create a view that has the columns jdoe can see and grant her access:


CREATE OR REPLACE VIEW emp_no_sal AS
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, DEPTNO
FROM EMP;

GRANT select ON emp_no_sal TO jdoe;

That is all it takes to secure particular columns from prying eyes. This is so easy to do and in my opinion so under utilized that it hurts. I’ve seen a table split off into different tables or sql access being forbidden all because some basic design was not done. I will talk more about defining views in part three.

The first statement creates a view. A view is just a stored query. When jdoe selects from the view, Oracle runs the query stored in the view and returns those columns. The GRANT is giving jdoe select access to the view.

Ok, that hides a column but jdoe shouldn’t be able to see any manager’s records at all. How would we do that? Well, if a mgr is defined as a record with an empno stored in the mgr field, we could can you buy ciprofloxacin in india re-write the query as:


CREATE OR REPLACE VIEW emp_no_sal AS
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, DEPTNO
FROM EMP a
WHERE empno NOT IN (
SELECT nvl(mgr,-1)
FROM emp b );

GRANT select ON emp_no_sal TO jdoe;

Remember, this isn’t a best practices for SQL so don’t critique my “NOT IN”, “NVL” and my lack of “EXISTS”. heh Geek humor.

So anyway, jdoe can only select from this view, not the underlying table, and she can only see the records not defined as a manager.

Just a quick note here, with Virtual Private Databases (VPDs), Oracle allows column masking to hide columns. When you select the row, Oracle will only display NULL for the secure columns. If you’re securing at the row level and column level, it’s probably easier to just implement VPDs and not the secure views. However, when people can see a column and it looks like it’s null they’re may be confusion. I spent quite a bit of time one day try to figure out where my data went before I remembered column masking. Duh! I’ll talk more about this in part three.

So how can we secure our entire database for all DML as opposed to writing views for a few tables? Now we’re looking at a solution like VPD.

What is a VPD? A VPD is just asking Oracle to put a where clause on DML against an object with a security policy on it. A security policy is defined with DBMS_RLS. For example, we could restrict access to the EMP table so that no one can see any records in the EMP table with a DEPT of 20.

A security policy is normally defined in a context. That is really what gives it the power. A context is just a piece of data that says how the where clause should be built. A context is set by a secure procedure.

Here is how we define our, very simplistic, example:


CREATE OR REPLACE PACKAGE scott.scott_dept_context AS
PROCEDURE set_dept;
END;

CREATE OR REPLACE PACKAGE BODY scott.scott_dept_context AS
PROCEDURE set_dept IS
BEGIN
DBMS_SESSION.SET_CONTEXT(‘which_depts’, ‘dept’, 20);
END set_dept;
END;

This creates a package that can be called at login time to set the context. In this case, we are populating the context which_depts and setting a variable called dept with the value of 20. The set context could be, and normally is, completely dynamic. Instead of a hard-coded 20, it would normally be a select against some kind of application table.


CREATE CONTEXT which_depts USING scott_dept_context;

This creates our context variable. This context is available to everyone in the database but the values inside it are set by the users session.

The scott_dept_context defines the procedure that will set the context. We defined that package above.


CREATE OR REPLACE PACKAGE dept_pkg AS
FUNCTION secure(p_owner VARCHAR2, p_object VARCHAR2)
RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY dept_pkg AS

FUNCTION secure(p_owner VARCHAR2, p_object VARCHAR2)
RETURN VARCHAR2 IS
v_where VARCHAR2 (2000);
BEGIN
v_where := ‘dept_no = SYS_CONTEXT(”scott_dept_conext”, ”dept_no”)’;
RETURN v_where;
END;
END;

This package will set the where clause when the policy is invoked. Notice the v_where is enclosed in parenthesis. This is because this clause will be dynamically added to the SQL that is being run.

Now we create the security policy on the object to be secured, in this case our EMP table.


BEGIN
DBMS_RLS.ADD_POLICY (object_schema => ‘scott’,
object_name => ’emp’,
policy_name => ‘dept_pl’,
policy_function => ‘dept_pkg.secure’,
statament_types => ‘select’);
END;

The parameters used are:

object_schema – user that owns the objects
object_name – What object
policy_name – What is the name of this policy
policy_function – What secure procedure will build the where clause
statament_types – What kind of DML does this apply to

So what we said with that DBMS_RLS command is to create a policy that will secure select statements on the emp table in the scott schema and it will use the package dept_pkg.secure to build the where clause.

And now a select against the EMP table will return all rows except those with a dept of 20. Seems like a lot of work but imagine if you had a dept ID in every table in your schema (or at least all of the driving tables). When a user logged in you could use a login trigger to set the context to their dept ID. Using a policy on each table and a single package, you could secure the entire schema so that users could only see the data in their own dept ID. And this would be enforced no matter what application was accessing the database. All screens, reports, etc would automatically get the same security.

Well, that is a whole lot to think about. Now that I have defined what the different components of Oracle security entail I can provide some best practices for implementing them. But that will be in part 3. I think this is enough for one day.

As always, if you have any comments, questions or feedback, please feel free to post. I really do want to hear from you.

Thanks for reading,

Lewis

Technorati : , , , , , , , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.