Reverse-Engineering a CRUD Matrix in Oracle

I think that title may need some explaining, especially if, like me, you spend your days trying to keep your database free of crud.
CRUD stands for Create Read Update and Delete. A CRUD Matrix contains details of which programs interact with which tables and in what way.
In the context of an application running on Oracle, it’s useful to know which stored program units interact with which tables for a whole host of reasons.
These mainly resolve themselves down to being able to analyse the impact of any structural changes you may want to make to your database such as changing a table definition or adding an index.

Incidentally, being such a useful concept, CRUD inevitably has many synonyms, ranging from the brutally frank ( Create Retrieve Alter Purge) to the positively hallucinogenic ( Add Change Inquire Delete) so I’m sure you can find an acronym suitable for whatever kind of day you happen to be having.

The Matrix

Resisting the temptation to mention spoons, or lack of, we come to the spec :
For each table, we want to know which stored program units interact with it and in what ways.
For our purposes here, stored program units are :

• Functions
• Packages
• Procedures
• Triggers
• Views
• Materialized Views

NOTE – an updated version of this application is now available. Have a look here.

At this point, it’s probably a good idea to mention some assumptions.
- Packages are stored as separate headers and bodies – the code to create this matrix looks specifically at PACKAGE BODY
- None of the source code is wrapped – we won’t be able to see the source code if it is
- Table aliases are not used in INSERT UPDATE DELETE and MERGE statements
- If a View or Materialized View is dependent on a table, we assume that it only does a Read of that table.

It’s pretty easy to find out if a program unit is dependent on a table, simply by looking in DBA_DEPENDENCIES. If we take the EMPLOYEES table in the HR schema as an example :

SQL> SELECT owner, name, type
  2  FROM dba_dependencies
  3  WHERE referenced_owner = 'HR'
  4  AND referenced_name = 'EMPLOYEES;

OWNER NAME           TYPE
----- -------------- ---------------------
HR    EMP_DETAILS_VIEW   VIEW
HR    SECURE_EMPLOYEES   TRIGGER
HR    UPDATE_JOB_HISTORY TRIGGER

However, we want a bit more. We want to know the nature of that dependency.
For each of these object types, we need to look at the source code in the Data Dictionary. At this point, we can say thanks to that nice Mr Ellison for giving use the extremely useful DBMS_METADATA package.

Before this package arrived in Oracle 10g, we would have had to work our way through each line of code in DBA_SOURCE. Triggers would have been even more fun as their source code is held in a LONG column in DBA_TRIGGERS.
Now, if you want to get the source code for a stored program unit, you just need to issue a query like this :

SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'SECURE_EMPLOYEES', 'HR')
FROM dual;

The get_ddl function accepts 3 arguments :

• program type ( the type of the program as listed in DBA_OBJECTS.object_type)
• program name
• program owner

It returns the source code of the desired object as a CLOB.
At this point it’s probably worth mentioning a little oddity with the DBMS_METADATA package.

In order to execute it for any object that you do not own, you need the SELECT_CATALOG_ROLE role. In a straight SELECT statement, such as the one above, this doesn’t present an issue, provided you either have SELECT_CATALOG_ROLE or have the DBA role as SELECT_CATALOG_ROLE is granted to DBA.
Things get a bit more interesting once you put a call to DBMS_METADATA into a package or a procedure. By default, a stored program unit will execute with the privileges of the object owner. The catch is that Oracle ignores any roles granted to this user during execution, so the fact that the owner has the DBA role doesn’t stop things falling over in a heap, as soon as you try to use DBMS_METADATA.GET_DDL on any object not owned by the owner of the procedure.
Granting SELECT_CATALOG_ROLE directly to the procedure owner is not going to fix things either, as it is itself a role and not a system privilege.

The error Oracle throws up under these circumstances is :
ORA-31603: object object_name of type program_type not found in schema schema_name

So, if we want to use this call in a procedure, we’ll need to declare the procedure with AUTHID of CURRENT_USER. This means that any user executing the procedure will need to have SELECT_CATALOG_ROLE either granted directly, or through another role ( such as DBA).

Once we’ve got the source code, we need to look through it to find out what DML is going on on the table in question. In short, we’re looking for the following statements :

• INSERT
• SELECT
• UPDATE
• DELETE
• MERGE

One final, but pertinent point – once we’ve built the matrix, we don’t necessarily want to have to rebuild it all from scratch every time something changes in the database. So, we need to keep track of when the matrix was last built and only look at stuff that’s changed since then.
Once again DBA_OBJECTS comes in handy here because it holds a timestamp of the last alteration of an object ( or, the date of it’s creation if it’s new) in LAST_DDL_TIME.

The Code

First off, we need a table to hold the matrix :-

CREATE TABLE db_crud(
    table_owner VARCHAR2(30),
    table_name VARCHAR2(30),
    object_owner VARCHAR2(30),
    object_name VARCHAR2(30),
    object_type VARCHAR2(20),
    create_flag VARCHAR2(1),
    read_flag VARCHAR2(1),
    update_flag VARCHAR2(1),
    delete_flag VARCHAR2(1))
/    

Next, we need a table to hold the last run date :-

CREATE TABLE crud_run_log (
    last_run_date DATE)
/

Finally, the code to produce the matrix.
NOTE – probably the best way to view this code is to hover your mouse over it until the pop-up menu appears in the top
left-hand corner of the code block. Then click the left-most icon ( view source) which will open up a text window :

CREATE OR REPLACE PROCEDURE crud ( pa_refresh IN VARCHAR2 DEFAULT 'N')
    AUTHID CURRENT_USER AS
--------------------------------------------------------------------------------
--  Description :                                                             --
--  Procedure to reverse-engineer a CRUD Matrix for all of the application    --
--  tables in the database.                                                   --
--                                                                            --
-- Parameter :                                                                --
--      pa_refresh - 'Y' refresh all of the data in the table                 --
--                   'N' just look at program units changes since the last    --
--                       run of this procedure                                --
-- Functionality :                                                            --
--  1) Identify all of the non-oracle user owned tables                       --
--  2) For each table, get a list of dependent objects                        --
--     If pa_refresh set to N, just look for objects that have been changed   --
--     or created since the last run.                                         --
--  3) Get the source code for each object                                    --
--  4) Work out the relationship                                              --
--  5) Dump the results into the db_crud table                                --
--                                                                            --
--------------------------------------------------------------------------------
    l_last_run DATE;
    
    CURSOR c_last_run IS
        SELECT last_run_date
        FROM crud_run_log;

    TYPE rec_tabs IS RECORD (
        table_owner dba_tables.owner%TYPE,
        table_name dba_tables.table_name%TYPE);
        
    TYPE typ_tabs IS TABLE OF rec_tabs INDEX BY PLS_INTEGER;
    tbl_tabs typ_tabs;
    
    TYPE rec_dep_objs IS RECORD (
        dep_owner dba_dependencies.owner%TYPE,
        dep_name dba_dependencies.name%TYPE,
        dep_type dba_dependencies.type%TYPE);
        
    TYPE typ_dep_objs IS TABLE OF rec_dep_objs INDEX BY PLS_INTEGER;
    
    tbl_dep_objs typ_dep_objs;
    
    l_clob CLOB;
    
    l_create VARCHAR2(1);
    l_read VARCHAR2(1);
    l_update VARCHAR2(1);
    l_delete VARCHAR2(1);
    
    CURSOR c_delta( cp_owner VARCHAR2, cp_name VARCHAR2, cp_last_run DATE) IS
        SELECT dep.owner, dep.name, REPLACE(dep.type,' ','_')
        FROM dba_dependencies dep, dba_objects obj
        WHERE dep.owner = obj.owner
        AND dep.name = obj.object_name
        AND dep.type = obj.object_type
        AND dep.type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE BODY', 'TRIGGER',
           'VIEW', 'MATERIALIZED VIEW')
        AND dep.referenced_owner = cp_owner
        AND dep.referenced_name = cp_name
        AND obj.last_ddl_time > cp_last_run;
        
    CURSOR c_full( cp_owner VARCHAR2, cp_name VARCHAR2) IS
        SELECT dep.owner, dep.name, REPLACE(dep.type,' ','_')
        FROM dba_dependencies dep
        WHERE dep.type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE BODY', 'TRIGGER',
            'VIEW', 'MATERIALIZED VIEW')
        AND dep.referenced_owner = cp_owner
        AND dep.referenced_name = cp_name;
    e_first_run EXCEPTION;        
BEGIN
    --
    -- First, check to see if we're doing a full refresh or just looking
    -- for changes since the last run
    --
    IF pa_refresh = 'N' THEN
        OPEN c_last_run;
        FETCH c_last_run INTO l_last_run;
        IF c_last_run%NOTFOUND THEN
            CLOSE c_last_run;
            RAISE e_first_run;
        END IF;
        CLOSE c_last_run;
    ELSE
        --
        -- Cleardown the table for a complete refresh
        --
        EXECUTE IMMEDIATE 'TRUNCATE TABLE db_crud';
    END IF;
    --
    -- Get a list of all the tables not owned by Oracle supplied users
    --
    SELECT owner, table_name BULK COLLECT INTO tbl_tabs
    FROM dba_tables
    WHERE owner NOT IN( 'ANONYMOUS', 'CTXSYS', 'DBSNMP', 'DIP', 'FLOWS_020100', 
      'FLOWS_FILES', 'MDSYS', 'OUTLN', 'SYS', 'SYSTEM', 'TSMSYS', 'XDB');
    
    --
    -- Now loop through the list of tables. This is the outer loop that we
    -- use to pick up all of the dependent objects for the table
    --
    FOR i IN 1..tbl_tabs.COUNT LOOP
        IF pa_refresh = 'N' THEN
            OPEN c_delta( tbl_tabs(i).table_owner, tbl_tabs(i).table_name,
                l_last_run);
            FETCH c_delta BULK COLLECT INTO tbl_dep_objs;
            CLOSE c_delta;
        ELSE
            OPEN c_full( tbl_tabs(i).table_owner, tbl_tabs(i).table_name);
            FETCH c_full BULK COLLECT INTO tbl_dep_objs;
            CLOSE c_full;
        END IF;
        --
        -- Inner loop - go through the source of the dependent object
        --
        IF tbl_dep_objs.COUNT > 0 THEN
            FOR j IN 1..tbl_dep_objs.COUNT LOOP
                --
                -- Start by re-setting the CRUD flags
                --
                l_create := 'N';
                l_read := 'N';
                l_update := 'N';
                l_delete := 'N';
                --
                -- Work out the type of object. If it's a VIEW or 
                -- MATERIALIZED VIEW we'll assume it's just a READ. 
                -- Otherwise, we'll look through the source code.
                -- 
                IF tbl_dep_objs(j).dep_type IN ('VIEW', 'MATERIALIZED VIEW') 
                THEN
                    l_read := 'Y';
                ELSE
                    --
                    -- Get the sourcecode - strip spaces and uppercase it to 
                    -- make searching more straightforward
                    --
                    l_clob := REPLACE( UPPER( DBMS_METADATA.GET_DDL( 
                        tbl_dep_objs(j).dep_type, tbl_dep_objs(j).dep_name,
                        tbl_dep_objs(j).dep_owner)),' ');
                    IF l_clob LIKE '%INSERTINTO'||tbl_tabs(i).table_name||'%' 
                        OR l_clob LIKE '%MERGEINTO'||tbl_tabs(i).table_name
                            ||'%THENINSERT%' 
                    THEN
                        l_create := 'Y';
                    END IF;
                    IF l_clob LIKE '%SELECT%FROM%'||tbl_tabs(i).table_name||'%' 
                    THEN
                        l_read := 'Y';
                    END IF;
                    IF l_clob LIKE '%UPDATE'||tbl_tabs(i).table_name||'%' 
                        OR l_clob LIKE '%MERGEINTO'||tbl_tabs(i).table_name
                            ||'%THENUPDATE%'
                    THEN
                        l_update := 'Y';
                    END IF;
                    IF l_clob LIKE '%DELETEFROM'||tbl_tabs(i).table_name||'%' 
                        OR l_clob LIKE '%MERGEINTO'||tbl_tabs(i).table_name||'%'
                            ||'%THENDELETE%' 
                    THEN                    
                        l_delete := 'Y';
                    END IF;
                    --
                    -- Finally, if we haven't found a CRUD, the fact there is a 
                    -- dependency means it's going to be a read ( even if it's 
                    -- just a %type reference to one of the tables columns).
                    --
                    IF l_create = 'N' AND l_read = 'N' AND l_update = 'N' 
                        AND l_delete = 'N' 
                    THEN
                        l_read := 'Y';
                    END IF;
                END IF;
                --
                -- Now add the result to the table / update the record in the
                -- table as appropriate
                --
                MERGE INTO db_crud
                USING dual
                ON (
                    table_owner = tbl_tabs(i).table_owner
                    AND table_name = tbl_tabs(i).table_name
                    AND object_owner = tbl_dep_objs(j).dep_owner
                    AND object_name = tbl_dep_objs(j).dep_name
                    AND object_type = tbl_dep_objs(j).dep_type)
                WHEN MATCHED THEN
                    UPDATE
                        SET create_flag = l_create,
                            read_flag = l_read,
                            update_flag = l_update,
                            delete_flag = l_delete
                WHEN NOT MATCHED THEN
                    INSERT (table_owner, table_name, object_owner,
                        object_name, object_type, create_flag,
                        read_flag, update_flag, delete_flag)
                    VALUES( tbl_tabs(i).table_owner, tbl_tabs(i).table_name,
                        tbl_dep_objs(j).dep_owner, tbl_dep_objs(j).dep_name,
                        tbl_dep_objs(j).dep_type, l_create, l_read, l_update,
                        l_delete);
            END LOOP; -- dependent objects loop
        END IF; -- count of dependent objects
    END LOOP; -- tables loop
    --
    -- Finally, update the crud_run_log table
    --
    UPDATE crud_run_log
    SET last_run_date = SYSDATE;
    COMMIT;
EXCEPTION WHEN e_first_run THEN
    RAISE_APPLICATION_ERROR(-20001, 
        'No last run information for CRUD. Run with pa_refresh set to Y.');    
END;
/

Querying the CRUD matrix

To see the CRUD matrix for the EMPLOYEES table in the HR schema :

SQL> SELECT object_name, object_type, create_flag,
  2     read_flag, update_flag, delete_flag
  3  FROM db_crud
  4  WHERE table_owner = 'HR'
  5  AND table_name = 'EMPLOYEES';

OBJECT_NAME        OBJECT_TYPE CRE REA UPD DEL
-----------        ----------  --- --- --- ---
EMP_DETAILS_VIEW   VIEW         N   Y   N   N
SECURE_EMPLOYEES   TRIGGER      N   Y   N   N
UPDATE_JOB_HISTORY TRIGGER      N   Y   N   N

Having the matrix readily available for any given table is quite useful, so it would be good to add it as a tab in SQLDeveloper. Have a look here for details of how you can add your own tabs.

About these ads

Tags: Anti-Kyte, crud, dba_dependencies, DBMS_METADATA, merge, ORA-31603, reverse engineer crud oracle, SELECT_CATALOG_ROLE