book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein

October 2005

Intermediate to advanced

454 pages

14h 44m

English

O'Reilly Media, Inc.

Read now

Unlock full access

1.2.1.1. Sections of the block1.2.1.2. Anonymous blocks
1.2.3.1. NULLs
1.2.4.1. String literals1.2.4.2. Numeric literals1.2.4.3. Boolean literals
1.2.6.1. Single-line comment syntax1.2.6.2. Multi-line comment syntax
1.3.1.1. Character data1.3.1.2. Numbers1.3.1.3. Dates, timestamps, and intervals1.3.1.4. Booleans1.3.1.5. Binary data1.3.1.6. ROWIDs1.3.1.7. REF CURSOR datatype1.3.1.8. Internet datatypes1.3.1.9. “Any” datatypes
1.3.2.1. Declaring a variable1.3.2.2. Declaring constants1.3.2.3. Anchored declarations
1.4.2.1. Simple CASE statement1.4.2.2. Searched CASE statement
1.6.2.1. RAISE statement1.6.2.2. Using RAISE_APPLICATION_ERROR
1.6.3.1. Built-in error functions1.6.3.2. Unhandled exceptions1.6.3.3. Propagation of an unhandled exception
1.7.2.1. Record-level operations1.7.2.2. Field-level operations
1.8.2.1. Using an associative array1.8.2.2. Using a nested table1.8.2.3. Using a VARRAY
1.9.1.1. Structure of a procedure1.9.1.2. Calling a procedure
1.9.2.1. Structure of a function1.9.2.2. Calling a function
1.9.3.1. Defining parameters1.9.3.2. Actual and formal parameters1.9.3.3. Parameter modes
1.9.4.1. Rules for building packages1.9.4.2. Rules for calling packaged elements1.9.4.3. Package data
1.10.3.1. Error handling with implicit cursors1.10.3.2. Implicit SQL cursor attributes
1.10.5.1. Limiting rows retrieved with BULK COLLECT
1.10.6.1. Declaring REF CURSOR types1.10.6.2. Declaring cursor variables1.10.6.3. Opening cursor variables1.10.6.4. Fetching from cursor variables
1.11.6.1. Syntax of the FORALL Statement1.11.6.2. FORALL Examples
1.13.1.1. Transaction participation1.13.1.2. Creating a DML trigger1.13.1.3. The WHEN clause1.13.1.4. Working with NEW and OLD pseudo -records1.13.1.5. Determining the DML action within a trigger
1.13.2.1. Creating a DDL Trigger
1.13.3.1. Creating a database event trigger
2.4.2.1. Strong vs. weak REF cursors2.4.2.2. REF cursor attributes2.4.2.3. Dynamic data access
3.3.2.1. Random partitioning (PARTITION BY ANY)3.3.2.2. Range partitioning (PARTITION BY RANGE)3.3.2.3. Order streaming (ORDER)3.3.2.4. Hash partitioning (PARTITION BY HASH)3.3.2.5. Cluster streaming (CLUSTER)
4.2.7.1. Using DES3GETKEY4.2.7.2. Using the key in encryption
4.3.3.1. Specifying the encryption type4.3.3.2. Specifying chaining4.3.3.3. Specifying padding4.3.3.4. Combining options in the typ parameter4.3.3.5. Handling and converting RAW data4.3.3.6. Specifying the encryption algorithm4.3.3.7. Putting it together
5.2.2.1. Problems with static policies5.2.2.2. Using a pragma
5.3.2.1. Shared static policy5.3.2.2. Context-sensitive policy5.3.2.3. Shared context-sensitive policy
5.4.3.1. Data dictionary view5.4.3.2. Event-based tracing
6.2.3.1. Turning off bind variable capture
6.2.4.1. Drawbacks with the default FGA approach6.2.4.2. Creating a user-defined audit facility
6.3.2.1. The ADD_POLICY procedure6.3.2.2. The DROP_POLICY procedure6.3.2.3. The DISABLE_POLICY procedure6.3.2.4. The ENABLE_POLICY procedure
6.4.2.1. The DBA_FGA_AUDIT_TRAIL view6.4.2.2. The FLASHBACK_TRANSACTION_QUERY view
6.5.1.1. The case for FGA6.5.1.2. The case for triggers
7.1.1.1. Controlling the precision7.1.1.2. Controlling the range
7.1.2.1. The RANDOM function
8.2.1.1. Running OS executables and anonymous blocks8.2.1.2. DBA_SCHEDULER_JOBS view
8.2.2.1. Enabling and disabling jobs8.2.2.2. Stopping running jobs8.2.2.3. Running a job8.2.2.4. Dropping a job
8.3.1.1. Examples of calendar strings8.3.1.2. Determining future calendar strings
8.7.1.1. DBA_SCHEDULER_JOB_LOG8.7.1.2. DBA_SCHEDULER_JOB_RUN_DETAILS8.7.1.3. Pruning the job log8.7.1.4. Log levels8.7.1.5. Setting the retention period
8.7.2.1. DBA_SCHEDULER_WINDOW_LOG8.7.2.2. DBA_SCHEDULER_WINDOW_DETAILS

Content preview from Oracle PL/SQL for DBAs

Encryption Algorithms

There are many widely used and commercially available encryption algorithms, but we’ll focus here on the symmetric key algorithms supported by Oracle for use in PL/SQL applications. The DES and Triple DES algorithms are supported by both of Oracle’s built-in encryption packages: DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT; only DBMS_CRYPTO, introduced in Oracle Database 10g Release 1, supports AES, however.

Data Encryption Standard (DES)

Historically, DES has been the predominant standard used for encryption . It was developed more than 20 years ago for the National Bureau of Standards (which later became the National Institute of Standards and Technology, or NIST), and subsequently DES became a standard of the American National Standards Institute (ANSI) . There is a great deal to say about DES and its history, but my purpose here is not to describe the algorithm but simply to summarize its adaptation and use inside the Oracle database. This algorithm requires a 64-bit key, but discards 8 of them, using only 56 bits. An intruder would have to use up to 72,057,594,037,927,936 combinations to guess the key.

DES was an adequate algorithm for quite a while, but the decades-old algorithm shows signs of age. Today’s powerful computers might find it easy to crack open even the large number of combinations needed to expose the key.

Triple DES (DES3)

NIST went on to solicit development of another scheme based on the original DES that encrypts data twice or thrice, depending ...