Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle security design tips

 Oracle Tips by Burleson Consulting

June 9, 2014

The terms "open systems" and "security" can seem impossible to reconcile. Maintaining security for a centralized Oracle system is difficult enough, and when faced with networked databases over the Web, maintaining access and update security is a formidable challenge.

In the real world, security is an afterthought, and many companies don't make security a priority during their rush to the market. This is especially true for distributed Oracle databases. Some reasons that security is inconsistent (or lacking) for distributed Oracle databases include:
  • 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!

Oracle training



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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster