Vyoms OneStopTesting.com - Testing EBooks, Tutorials, Articles, Jobs, Training Institutes etc.
OneStopGate.com - Gate EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopMBA.com - MBA EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopIAS.com - IAS EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopSAP.com - SAP EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopGRE.com - of GRE EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
Bookmark and Share Rss Feeds

Control Access with Oracle Grant Security | Articles | Recent Articles | News Article | Interesting Articles | Technology Articles | Articles On Education | Articles On Corporate | Company Articles | College Articles | Articles on Recession
Sponsored Ads
Hot Jobs
Fresher Jobs
Experienced Jobs
Government Jobs
Walkin Jobs
Placement Section
Company Profiles
Interview Questions
Placement Papers
Resources @ VYOMS
Companies In India
Consultants In India
Colleges In India
Exams In India
Latest Results
Notifications In India
Call Centers In India
Training Institutes In India
Job Communities In India
Courses In India
Jobs by Keyskills
Jobs by Functional Areas
Learn @ VYOMS
GATE Preparation
GRE Preparation
GMAT Preparation
IAS Preparation
SAP Preparation
Testing Preparation
MBA Preparation
News @ VYOMS
Freshers News
Job Articles
Latest News
India News Network
Interview Ebook
Get 30,000+ Interview Questions & Answers in an eBook.
Interview Success Kit - Get Success in Job Interviews
  • 30,000+ Interview Questions
  • Most Questions Answered
  • 5 FREE Bonuses
  • Free Upgrades


Wipro Technologies
Tata Consultancy Services
Cognizant Technologies

Home » Articles » Control Access with Oracle Grant Security

Control Access with Oracle Grant Security

Article Posted On Date : Friday, January 15, 2010

Control Access with Oracle Grant Security

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 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.

Oracle 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:

grant create 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.

Oracle 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:

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;

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 Oracle 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.

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.

SQL> create role emp_role;

 Role created.

 SQL> create role mgr_role;

 Role created.

 SQL> create view
2 emp_view
3 as
4 select
5 emp_first_name, emp_last_name
6 from
7 emp
8 where
9 pub_key = 'P001';

 View created.

 SQL> create view
2 mgr_view
  3 as
4 select
5 emp_first_name, emp_last_name, emp_salary
6 from
7 emp;

 View created.

 SQL> -- Grant the privileges to the role
 SQL> grant create session, connect, resource to emp_role, mgr_role;

 Grant succeeded.

 SQL> grant select on emp_view to emp_role;

 Grant succeeded.

 SQL> grant select on mgr_view to mgr_role;

 Grant succeeded.

 SQL> create user fred_emp identified by fred;

 User created.

 SQL> grant emp_role to fred_emp;

 Grant succeeded.

 SQL> create user mary_mgr identified by mary;

 User created.

 SQL> grant mgr_role to mary_mgr;

 Grant succeeded.

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

SQL> connect fred_emp/fred;
SQL> select * from pubs.emp_view;

EMP_FIRST_NAME EMP_LAST_NAME                                             
-------------------- ------------------------------                            
sam king                                                      
mary korn                                                      

SQL> connect mary_mgr/mary;

SQL> select * from pubs.mgr_view;

EMP_FIRST_NAME       EMP_LAST_NAME          EMP_SALARY                 
-------------------- ---------------------- ----------             
sam                  king                   95000                 
bill                 jackson                35000                 
mary                 korn                   28000                 
fred                 linus                  45000                 
janet                tokheim                63000                
john                 lavender               14000              

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:

connect pubs/pubs

create table
select object_name, object_type from dba_objects;

 Table created.

select count(*) from test_table;


 1 row selected.

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

connect scott/tiger

 select count(*) from test_table
 ERROR at line 1:
 ORA-00942: table or view does not exist

In this case, you know that the table exists, but Oracle considers only the fully-qualified table name:

Connect pubs/pubs

 create public synonym test1_table for pubs.test1_table;

 Synonym created.

 Connect scott/tiger

 select count(*) from test_table;


 1 row selected.


Complexity of Oracle Security

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 book "Oracle Privacy Security Auditing", I'll examine database design for these new methods.

Sponsored Ads

Interview Questions
HR Interview Questions
Testing Interview Questions
SAP Interview Questions
Business Intelligence Interview Questions
Call Center Interview Questions


Clipper Interview Questions
DBA Interview Questions
Firebird Interview Questions
Hierarchical Interview Questions
Informix Interview Questions
Microsoft Access Interview Questions
MS SqlServer Interview Questions
MYSQL Interview Questions
Network Interview Questions
Object Relational Interview Questions
PL/SQL Interview Questions
PostgreSQL Interview Questions
Progress Interview Questions
Relational Interview Questions
SQL Interview Questions
SQL Server Interview Questions
Stored Procedures Interview Questions
Sybase Interview Questions
Teradata Interview Questions

Microsof Technologies

.Net Database Interview Questions
.Net Deployement Interview Questions
ADO.NET Interview Questions
ADO.NET 2.0 Interview Questions
Architecture Interview Questions
ASP Interview Questions
ASP.NET Interview Questions
ASP.NET 2.0 Interview Questions
C# Interview Questions
Csharp Interview Questions
DataGrid Interview Questions
DotNet Interview Questions
Microsoft Basics Interview Questions
Microsoft.NET Interview Questions
Microsoft.NET 2.0 Interview Questions
Share Point Interview Questions
Silverlight Interview Questions
VB.NET Interview Questions
VC++ Interview Questions
Visual Basic Interview Questions

Java / J2EE

Applet Interview Questions
Core Java Interview Questions
Eclipse Interview Questions
EJB Interview Questions
Hibernate Interview Questions
J2ME Interview Questions
J2SE Interview Questions
Java Interview Questions
Java Beans Interview Questions
Java Patterns Interview Questions
Java Security Interview Questions
Java Swing Interview Questions
JBOSS Interview Questions
JDBC Interview Questions
JMS Interview Questions
JSF Interview Questions
JSP Interview Questions
RMI Interview Questions
Servlet Interview Questions
Socket Programming Interview Questions
Springs Interview Questions
Struts Interview Questions
Web Sphere Interview Questions

Programming Languages

C Interview Questions
C++ Interview Questions
CGI Interview Questions
Delphi Interview Questions
Fortran Interview Questions
ILU Interview Questions
LISP Interview Questions
Pascal Interview Questions
Perl Interview Questions
PHP Interview Questions
Ruby Interview Questions
Signature Interview Questions
UML Interview Questions
VBA Interview Questions
Windows Interview Questions
Mainframe Interview Questions

Copyright © 2001-2022 Vyoms.com. All Rights Reserved. Home | About Us | Advertise With Vyoms.com | Jobs | Contact Us | Feedback | Link to Us | Privacy Policy | Terms & Conditions
Placement Papers | Get Your Free Website | IAS Preparation | C++ Interview Questions | C Interview Questions | Report a Bug | Romantic Shayari | CAT 2022

Fresher Jobs | Experienced Jobs | Government Jobs | Walkin Jobs | Company Profiles | Interview Questions | Placement Papers | Companies In India | Consultants In India | Colleges In India | Exams In India | Latest Results | Notifications In India | Call Centers In India | Training Institutes In India | Job Communities In India | Courses In India | Jobs by Keyskills | Jobs by Functional Areas

Testing Articles | Testing Books | Testing Certifications | Testing FAQs | Testing Downloads | Testing Interview Questions | Testing Jobs | Testing Training Institutes

Gate Articles | Gate Books | Gate Colleges | Gate Downloads | Gate Faqs | Gate Jobs | Gate News | Gate Sample Papers | Gate Training Institutes

MBA Articles | MBA Books | MBA Case Studies | MBA Business Schools | MBA Current Affairs | MBA Downloads | MBA Events | MBA Notifications | MBA FAQs | MBA Jobs
MBA Job Consultants | MBA News | MBA Results | MBA Courses | MBA Sample Papers | MBA Interview Questions | MBA Training Institutes

GRE Articles | GRE Books | GRE Colleges | GRE Downloads | GRE Events | GRE FAQs | GRE News | GRE Training Institutes | GRE Sample Papers

IAS Articles | IAS Books | IAS Current Affairs | IAS Downloads | IAS Events | IAS FAQs | IAS News | IAS Notifications | IAS UPSC Jobs | IAS Previous Question Papers
IAS Results | IAS Sample Papers | IAS Interview Questions | IAS Training Institutes | IAS Toppers Interview

SAP Articles | SAP Books | SAP Certifications | SAP Companies | SAP Study Materials | SAP Events | SAP FAQs | SAP Jobs | SAP Job Consultants
SAP Links | SAP News | SAP Sample Papers | SAP Interview Questions | SAP Training Institutes |

Copyright ©2001-2022 Vyoms.com, All Rights Reserved.
Disclaimer: VYOMS.com has taken all reasonable steps to ensure that information on this site is authentic. Applicants are advised to research bonafides of advertisers independently. VYOMS.com shall not have any responsibility in this regard.