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