• Corporate acquisitions
• Lack of corporate standards
• Departments who design systems independently

Before you can understand how to design for Oracle security, you must first identify the areas for security and understand how they are implemented in an Oracle environment.

Areas of Oracle security

Oracle has a plethora of different security and auditing methods. It's critical to the success of the Oracle design project to choose the method and to implement it during the application design phase of system development.

The main ways to implement data access security within Oracle are:
 

• Grant security
• Role-based security
• Grant execute security
• Virtual private databases (VPD)

In many cases, you must also design for Oracle auditing. The new HIPAA health care laws have placed a tremendous burden on Oracle shops that must provide complete audit trails of all areas of DDL (e.g., schema changes), DML (e.g., updates, insert, deletes), and select audits of confidential patient information.

Another important area of Oracle security is Oracle auditing, and we also have to carefully plan the auditing scheme. There are several ways to audit within Oracle:
 

• SQL audit command (for DML)
• Auditing with object triggers (i.e., DML auditing)
• Auditing with system-level triggers (i.e., DML and DDL)
• Auditing with LogMiner (i.e., DML and DDL)
• Fine-grained auditing (i.e., select auditing)

Let’s quickly look at each auditing and security method and then show why design is so important to effective Oracle security.

Grant security

Oracle grant security takes several forms: object grants, system privilege grants, and role-based grants.

Object privileges assign the right to perform a particular operation on a specific object. The following are some examples of object privilege assignment:

grant select on customer to fred;
grant insert on order_table to update_role;
grant all on customer to fred;
grant select on customer_view to mary;

System privileges grants cover many areas of access in a broad brush, with grants like select any table. Examples of system privilege grants include:

grant create any cluster to customer_role;
grant select any table to fred;
grant create tablespace to dba_role;

Role-based grants create a role, which is a predefined collection of privileges that are grouped together for easy assignment to users. These are examples of role-based grants:
create role 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;

grant execute security

Oracle provides the ability to enforce access to a table by using procedures. A stored procedure is a code snippet written in PL/SQL that performs functions and accesses the database with SQL.

The grant execute security approach is totally different from the traditional grant approach. Rather than granting a specific privilege to a specific user, the grant execute method grants execution privileges on a stored procedure or package directly to a user.

Virtual private databases VPDs are also known by several other names, including row-level security (RLS) and fine-grained access control (FGAC). Regardless of the name, VPDs provide a whole new way to control access to Oracle data. VPDs involve creation of a security policy. When users access a table (or view) that has a security policy:   Oracle calls the policy function, which returns a predicate. A predicate is a WHERE clause that qualifies a particular set of rows within the table.   Oracle dynamically rewrites the query by appending the predicate to users' SQL statements. A VPD requires a policy that is defined to control access to tables and rows, as shown in Figure A.   Figure A The policy function may generate the predicates on whatever session environment variables are available during the function call. These variables usually appear in the form of application contexts. Whenever a query is run against the target tables, Oracle invokes the policy and produces a transient view with a WHERE clause predicate pasted onto the end of the query, like so: SELECT * FROM scott.emp WHERE P1 There are many benefits to VPDs:   Dynamic security—No need to maintain complex roles and grants Multiple security—Places more than one policy on each object, as well as stacks them upon other base policies Web Apps—A single user accesses the database, hence row-level security can easily differentiate between users No back-doors—Users no longer bypass security policies embedded in applications because the security policy is attached to the data Security and design Regardless of the security and auditing method that you use, proper design is critical, and many shops create security maps, as shown in Figure B, to show data access hierarchies.     Figure B Sample design for role-based Oracle security Problems with mixing security Once you understand the areas of security and auditing, it should be clear that you must come up with a method to ensure that security methods aren't mixed in an inappropriate way. By themselves, each of these security mechanisms provides adequate access protection, but when these methods are mixed, it can often be difficult (if not impossible) to identify the access for individual users.         Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.