Oracle security design tips

• 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.     Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

Oracle Training from Don Burleson  The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:   and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright © 1996 -  2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.