 |
|
Creating and Maintaining
Users
Oracle Tips by Burleson
|
Before Oracle7 introduced roles, Oracle DBAs had to grant rights on
every table within a database to every user. Simply creating a new
user was an arduous task, even with customized scripts that emulated
the functionality that would come later with roles. Updating and
maintaining grants was also a time-consuming task for DBAs. Listing
1.3 shows a sample script used to create a new user in an Oracle
database.
Listing 1.3 A sample script to create a new user in an
Oracle database.
GRANT CONNECT TO '&&1' IDENTIFIED BY '&&1';
GRANT RESOURCE TO '&&1';
GRANT SELECT ON STUDENTS TO '&&1';
GRANT SELECT ON STUDENT_FINANCIAL_AID TO '&&1':
GRANT SELECT ON ENROLLED_COURSES TO '&&1';
GRANT SELECT ON DEGREE_PLANS TO '&&1';
Obviously, this is a very simple example. In systems that contained
hundreds of tables, these scripts often took hours to write and debug;
executing the scripts for just one user took a significant amount of
time. Furthermore, direct user grants had to be stored in the data
dictionary, consuming storage space and memory and thereby using
resources that can now be better used to improve performance.
An alternate method of handling this problem was to create an
application that stored the privileges for each user inside one or
more tables. Once the data was entered (usually via Oracle*Forms), an
SQL script could generate the proper grant statements. Unfortunately,
someone had to come up with a way to populate this table.
This is an excerpt from the book "High
Performance Oracle Database Automation" by
Jonathan Ingram and Donald K. Burleson, Series Editor.
|
