Data Management

Control access with Oracle grant security

Grant security in an Oracle database is the most basic form of access control, and it should have a place in any security scheme. Under grant security, every user in the database is given access to specific data objects using various privilege types.

By Donald K. Burleson

Oracle offers many ways to control data access rules, including:
  • Grant security (e.g., system, object, and role privileges)
  • Grant execute security (e.g., definer and invoker rights)
  • Virtual private databases (VPD)
  • N-tier authentication (e.g., RADIUS and authentication servers)

Let's start with the basics by looking at grant security to examine its benefits and pitfalls. The original relational model provides a method for granting privileges to users to allow for access control. This grant model was originally described by E.F. Codd and Chris Date in the original relational model and the model is standard across most commercial relational databases.

Oracle grant security takes several forms: object grants, system privilege grants, and role-based grants. The idea is that every user in the database is granted access to specific data objects to control data access.

Oracle Security
This is the second in a multi-part series on Oracle design for access control. If you missed it, you can read the first article in the series, "Oracle design security from the ground up," here.

Object privileges
Object privileges assign the right to perform a particular operation on a specific object. Here are some examples of object privilege assignment:
grant select, insert on customer to fred, mary, joe;
grant insert on order_table to update_role;
grant all on customer to fred;
grant select on customer_view to mary;

As you can see, the direct assignment of object privileges requires specific grants for every object to every user in the Oracle database. If you have a schema with 100 tables and 1,000 users, it would require 100,000 individual grant statements to assign security.

System privileges
System privileges cover many areas of access in a broad brush, with grants such as select any table. Examples of system privilege grants include:
grantcreate any cluster to customer_role;
grant select any table to fred;
grant create any table to public;
grant create tablespace to dba_role;

Obviously, system privileges should be used only in cases where security isn't important, because a single grant statement could remove all security from the table.

Role-based security
Role security allows you to gather related grants into a collection. Since the role is a predefined collection of privileges that are grouped together, privileges are easier to assign to users, as in this example:
createrole all_customer;

grant select, update on customer to all_customer;
grant select on item_table to all_customer;

grant all_customer to fred, mary, joe;

The benefits of role-based security are obvious, because role-based security allows you to define sets of access rules and then assign them to the appropriate classes of users.

However, unlike VPD security, it isn't possible to implement sophisticated rules for data access. With grants, users either have access to the table, or they do not.

Design for grant security
If you choose to implement grant security for your Oracle database, you must do some careful up-front planning to ensure that each role is carefully designed to cover access for a specific class of users without overlapping other roles. The steps for implementing grant security are:
  1. Define roles for all known classes of users.
  2. Define access rules for each role.
  3. Define all row-level and column-level restrictions.
  4. Create views for all data access.
  5. Assign the views to the roles.
  6. Assign the roles to the users.

To alleviate the issue of overlapping roles, many Oracle designers create a hierarchy of roles, using roles within roles to exactly match the data access requirements to user groups (Figure A).

Figure A
User group access

Note the careful overlap of access privileges between the programmer and analyst roles. It's not uncommon for roles to legitimately overlap when it comes to access, and the Oracle designer must pay careful attention to the access outline.

In the real world, the design of the roles can get quite complex. To illustrate, let's look at a simple example.

Design for row-level and column-level access
In the real world, it isn't enough simply to grant access to whole tables; often you may need to restrict access to specific rows within a table. Without VPDs, the only way to do this with grants is to create separate views for each row-level restriction and then assign the views to the roles and the roles to the users. For example, assume that you must design roles based on the following business rules:
  • Only managers may view the salary column of the employee table (column restriction).
  • Other employees may view only employee names and phone number for publisher P001 (row restriction).

To implement this design within Oracle using role-based security, perform the following steps:
  • Create the base roles for managers and employees.
  • Create the appropriate views.
  • Grant the views to the roles.
  • Grant the roles to the users.

In Oracle, the views in Listing A implement this design scheme.

Now that you've assigned the appropriate grant statements, test your views and see if they work (Listing B):

Loopholes in grant security
There are several issues with grant security that can create loopholes in your design. These include:
  • Assigning grants to PUBLIC.
  • Assigning roles using the WITH ADMIN option.
  • Overlapping unplanned access roles.
  • Assigning system privileges (select any table) to roles.
  • Creating public synonyms.

For example, within Oracle you can explicitly grant to PUBLIC all tables that you wish to have general read-only access. This system privilege supercedes the role-based security and creates a giant loophole, as in this example:
Create public synonym customer for pubs.customer;
Grant select on customer to public;

Another important loophole is public synonyms for tables. Remember, the default in Oracle is that nobody except the object owner is allowed to perform any operation on the table. Also, the full table name must be specified in the SQL or Oracle will imply that the table does not exist, as in Listing C.

Now, when you connect as the user, SCOTT, he is unable to see the table rows, as in the example in Listing D.

In this case, you know that the table exists, but Oracle considers only the fully-qualified table name (see Listing E).

Designing Oracle databases for grant security can become phenomenally complex. The issues raised in this article have led to the creation of new methods of Oracle security, most notably the grant execute model and the VPD. In my next two installments, I'll examine database design for these new methods.

Editor's Picks