Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Oracle Security and User Administration
Oracle Tips by Burleson
 

Even with the advent of roles in Oracle7, the DBA still has to maintain a record of the rights a role has. If a role is a template user, the template must still be configured. The configuration of the roles for a system is often done with a script, like the one shown in Listing 3.3.

Listing 3.3 A script that grants privileges to roles.

-- *****************************************************************
-- This role will be granted to accounts that process financial
-- aid applications.
--
CREATE ROLE Financial_Aid_Processor;
 
--
-- This role must be able to read the student's contact information.
--
GRANT SELECT ON STUDENTS TO Financial_Aid_Processor;
 
--
-- This role assigns all financial aid application data.
--
GRANT SELECT ON STUDENT_FINANCIAL_AID TO Financial_Aid_Processor;
GRANT INSERT ON STUDENT_FINANCIAL_AID TO Financial_Aid_Processor;
 
--
-- This role needs read access to students' grades.
--
GRANT SELECT ON ENROLLED_COURSES TO Financial_Aid_Processor;
 
--
-- This role is granted to management accounts in the financial aid
-- office.
--
CREATE ROLE Financial_Aid_Manager;
 
-- ******************************************************************
-- The manager will have all the privileges of a processor.
--
GRANT Financial_Aid_Processor TO Financial_Aid_Manager;
--
-- The manager must also be able to update a student's financial
-- aid records.
--
GRANT UPDATE ON STUDENT_FINANCIAL_AID TO Financial_Aid_Processor;

Although the configuration of individual users can be done through roles, a security feature in Oracle requires granted rights to individual tables (including views and snapshots) before a developer can compile objects that reference the table. The DBA must maintain a script to create developer accounts, like the one shown in Listing 3.4.

Listing 3.4 A script to create an application developer’s account.

GRANT CONNECT, RESOURCE TO &&1 IDENTIFIED BY &&2;
 
--
-- Allow the developer to create stored procedures, functions,
-- packages, and triggers.
--
GRANT CREATE ANY OBJECT TO &&1;
 
--
-- The developer must have full access to these tables.
--
GRANT ALL ON STUDENTS TO &&1;
GRANT ALL ON STUDENT_FINANCIAL_AID TO &&1;
GRANT ALL ON ENROLLED_CLASSES TO &&1;
EXIT

The scope of the rights and the fact that the rights granted directly to the account makes a developer’s account very powerful (as it should be). Most systems find it prudent to not create development accounts on production systems, for reasons that should be obvious.

 
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter