Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Note: PL/Scope allow the browsing of PL/SQL source code in a manner similar to the way that Cscope (https://cscope.sourceforge.net) allows browsing of C source code.
Users can search for and display definitions, declarations, assignments, and references.
Additional use cases and code demos of PLSCOPE compilation parameters can be found on the Inquiry Directives page linked at the bottom of this page.
Collection Identifiers
ASSOCIATIVE ARRAY
DATATYPETIMESTAMP
OPAQUE (for example AnyData)
CONSTANT
DATATYPE
OUTFORMAL OUT
CURSOR
EXCEPTION
PACKAGE
BFILE
FORMAL
PROCEDURE
DATATYPEBLOB
FUNCTION
RECORD
DATATYPEBOOLEAN
INDEX TABLE
REFCURSOR
DATATYPECHARACTER
INFORMAL IN
SUBTYPE
DATATYPECLOB
ITERATOR
(loop iterator)
SYNONYM
DATATYPEDATE
LABEL
TRIGGER
DATATYPEINTERVAL
LIBRARY
UROWID
DATATYPENUMBER
NESTED TABLE
VARRAY
DATATYPETIME
OBJECT
VARIABLE
Data Dictionary Objects
ALL_IDENTIFIERS
CDB_PLSQL_OBJECT_SETTINGS
USER_IDENTIFIERS
ALL_PLSQL_OBJECT_SETTINGS
DBA_IDENTIFIERS
USER_PLSQL_OBJECT_SETTINGS
CDB_IDENTIFIERS
DBAL_PLSQL_OBJECT_SETTINGS
V$SYSAUX_OCCUPANTS
First Available
11.1
Usage Column Values
ASSIGNMENT
An assignment can be made only to an identifier that can have a value, such as a VARIABLE
CALL
In the context of PL/Scope, a CALL is an operation that pushes a new call stack;
that is a call to a FUNCTION or PROCEDURE or executing or fetching a cursor identifier (a logical call to SQL)
DECLARATION
A DECLARATION tells the compiler that an identifier exists, and each identifier has exactly one DECLARATION. Each DECLARATION can have an associated datatype.
For a loop index declaration, LINE and COL (in *_IDENTIFIERS views) are the line and column of the FOR clause that implicitly declares the loop index.
For a label declaration, LINE and COL are the line and column on which the label appears (and is implicitly declared) within the delimiters << and >>.
DEFINITION
A DEFINITION tells the compiler how to implement or use a previously declared identifier. Each of the following types of identifiers has a DEFINITION:
EXCEPTION (can have multiple definitions)
FUNCTION
OBJECT
PACKAGE
PROCEDURE
TRIGGER
For a top-level identifier only, the DEFINITION and DECLARATION are in the same place.
REFERENCE
A REFERENCE uses an identifier without changing its value. Examples of references are:
Raising an exception identifier
Using a type identifier in the declaration of a variable or formal parameter
Using a variable identifier whose type contains fields to access a field. For example, in myrecordvar.myfield := 1, a reference is made to myrecordvar, and an assignment is made to myfield.
Using a cursor for any purpose except FETCH
Passing an identifier to a subprogram by value (IN mode)
Using an identifier as the bind argument in the USING clause of an EXECUTE IMMEDIATE statement in either IN or IN OUT mode.
An identifier that is passed to a subprogram in IN OUT mode has both a REFERENCE usage (corresponding to IN) and an ASSIGNMENT usage (corresponding to OUT).
PL/SCOPE Demo
Step 1: Recompile the package STANDARD
Read the warning to the right and then ignore this step unless you think dropping a brick on your foot is lovely fun.
Oracle stated this in their docs at one time.
Note:
This query produces this output only if package STANDARD was compiled with PLSCOPE_SETTINGS='IDENTIFIERS:ALL'.
By default, this query returns no identifier data. Please see the
11gR1 release notes for more information on how to compile package
STANDARD for PL/Scope if you want to see something both
irresponsible and ... well ... irresponsible.
so I tried it to see what would happen:
conn / as sysdba
sho parameter plscope
NAME TYPE VALUE
----------------- ----------- -----------------
plscope_settings string IDENTIFIERS:NONE
ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL';
sho parameter plscope
NAME TYPE VALUE
----------------- ----------- -----------------
plscope_settings string IDENTIFIERS:
ALTER PACKAGE standard COMPILE;
SELECT COUNT(*)
FROM dba_objects_ae
WHERE status = 'INVALID';
The end result was a totally compromised database with so many invalid objects that utlrp and UTL_RECOMP were useless.
Do not try this with any database you are not prepared to drop.
Needless to say I reported the doc bug and the comment was removed. But
just in case someone finds a copy of this lunacy in some obscure corner of the internet ... you have been warned.
Ignore this step! Not this warning!
Step 2: Create PL/SQL Objects
conn sys@pdbdev as sysdba
CREATE OR REPLACE DIRECTORY ctemp AS '/home/oracle';
GRANT read, write ON DIRECTORY ctemp TO uwclass;
conn uwclass@pdbdev
CREATE TABLE uwclass.sources_import (
sourceno VARCHAR2(10),
sizeno VARCHAR2(10),
status VARCHAR2(10),
latitude VARCHAR2(10),
longitude VARCHAR2(10),
testfor VARCHAR2(15));
-- to download the file sources.txt [click here] and place it in a directory identified by the Oracle directory name CTEMP
vLineNo := vLineNo+1;
END LOOP;
utl_file.fclose(v_InHandle);
FORALL i IN 1..l_data.COUNT
INSERT INTO sources_import VALUES l_data(i);
DELETE FROM sources_import WHERE sourceno = 'SOURCENO';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END blended;
/
Step 3: Browse PL/SQL
SELECT inst_id, space_usage_kbytes
FROM gv$sysaux_occupants
WHERE occupant_name='PL/SCOPE';
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%scope%';
ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL';
ALTER PROCEDURE blended COMPILE;
desc user_plsql_object_settings
desc user_identifiers
set linesize 121
-- view user identifier metadata
SELECT LPAD(' ', LEVEL*2, ' ') || name AS name, type, usage, usage_id, line, col
FROM user_identifiers
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id;
-- narrow the search with a WHERE clause
SELECT LPAD(' ', LEVEL*2, ' ') || name AS name, type, usage, usage_id,
line, col
FROM user_identifiers
WHERE name LIKE '%PROFARRAY%'
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id;
-- verify PL/SCOPE collection
SELECT plscope_settings
FROM user_plsql_object_settings
WHERE name = 'BLENDED'
AND type = 'PROCEDURE';
-- find unique identifiers
SELECT name, type, signature
FROM user_identifiers
WHERE usage = 'DECLARATION'
ORDER BY 2,1;
-- find the local variables
col variable_name format a30
col context_name format a20
SELECT a.name VARIABLE_NAME, b.name CONTEXT_NAME, a.signature
FROM user_identifiers a, user_identifiers b
WHERE a.usage_context_id = b.usage_id
AND a.type = 'VARIABLE'
AND a.usage = 'DECLARATION'
AND a.object_name = 'BLENDED'
AND a.object_name = b.object_name
AND a.object_type = b.object_type
AND (b.type = 'FUNCTION' OR b.type = 'PROCEDURE')
ORDER BY a.object_type, a.usage_id;
-- find all uses of a named variable
SELECT usage, usage_id, object_name, object_type
FROM user_identifiers
WHERE signature = 'F71FEA2C3C52DE483E265916351F5DB1'
ORDER BY object_type, usage_id;
-- from the declaration of a local identifier find its type
SELECT a.name, a.type, a.usage, a.signature
FROM user_identifiers a, user_identifiers b
WHERE a.usage_context_id = b.usage_id
AND a.object_type = b.object_type
AND a.object_name = b.object_name
AND a.usage = 'DECLARATION'
ORDER BY 1;
-- find where assignments occur
SELECT line, col, name, object_name, object_type
FROM user_identifiers
WHERE usage = 'ASSIGNMENT';
ALTER SESSION SET plscope_settings='IDENTIFIERS:NONE';
