Previous | Table of Contents | Next |
The use of scripts and batch processes is the oldest aspect of computing and still plays a very active role in most systems. Most major operating systems support at least one scripting language. Table 3.1 highlights the predominent scripting language for each major operating system.
| |
---|---|
Operating System | Scripting Language |
MS-DOS | MS-DOS batch language |
OS/2 | REXX |
VMS and OpenVMS | DCL |
Windows 95 | Visual Basic, Perl, MS-DOS batch language |
Windows NT | Visual Basic, Perl, MS-DOS batch language |
Unix | C shell, Bourne shell, Korn shell, Perl, awk, sed |
|
As you can see, Unix is particularly rich in scripting tools. Unix is also the predominant platform for Oracle databases (Windows NT is gaining strength, while VMS and OpenVMS are gradually disappearing ). Consequently, the scripts included in this chapter (where appropriate) contain examples of scripts on a Unix system, but keep in mind that SQL*Plus and PL/SQL can be used with the scripting language of any operating system.
This chapter will introduce you to the basic uses of scripts in an Oracle database and discuss the tools you ll need to script using SQL*Plus and PL/SQL.
Scripts are used to perform many types of tasks in an Oracle database. The database administrator s duties often involve complex tasks that are automated with scripts, but this isn t the entire world of scripting. Scripts perform many different tasks within a system, including:
The most well-known scripts in an Oracle database are those that the DBA uses to back up the database. Listing 3.1 is a simple generic script that a DBA could use to perform a cold backup of an Oracle database (you may remember this script from Chapter 1).
Listing 3.1 A generic cold backup script for an Oracle database.
# # Set up the environment variables. # ORACLE_SID=registrar_db; export ORACLE_SID ORACLE_HOME=/dbhost/database/oracle/v722; export ORACLE_HOME # # Shut down the database. # svrmgrl connect internal shutdown immediate # # Backup the database control files, redo logs, and dbf files. # tar -cvf /dev/backup/tdr $ORACLE_HOME # # Restart the database. # svrmgrl connect internal startup
One common use of SQL scripts is the generation of other SQL scripts. Consider the script shown in Listing 3.2, which dynamically builds and executes an SQL script containing commands to drop all the objects in the specified user s schema.
Listing 3.2 The DROP_ALL.SQL script.
set head off set pages 0 set verify off set lines 80 set feedback off set termout off spool &&1.sql SELECT 'DROP ' object_type ' ' &&1 '.' object_name ';' FROM ALL_OBJECTS WHERE owner = upper ('&&1') ORDER BY object_type desc; spool off @&&1.sql host rm &&1.sql exit
When run for the jschmoe schema ( assuming that the person running the script has access to the schema), this script would generate and execute an SQL script containing the following SQL commands:
DROP TABLE JSCHMOE.STUDENTS; DROP TABLE JSCHMOE.ENROLLED_COURSES; DROP TABLE JSCHMOE.STUDENT_FINANCIAL_AID; DROP PROCEDURE JSCHMOE.CALCULATE_GPA; DROP PACKAGE BODY JSCHMOE.STUDENT_UPDATES; DROP PACKAGE JSCHMOE.STUDENT_UPDATES; DROP FUNCTION JSCHMOE.GRANT_FINANCIAL_AID;
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.
Previous | Table of Contents | Next |