Re: Generate a CRUD matrix

From: Galen Boyer <galen_boyer_at_yahoo.com>
Date: Sat, 16 Jan 2010 13:45:00 -0500
Message-ID: <uhbqlyjmb.fsf_at_www.yahoo.com>

"Terry Dykstra" <tddykstra_at_forestoil.ca> writes:

> Is it possible to extract a CRUD matrix for a procedure or package in
> Oracle. I'm using 10.2.0.4 SE.
> Getting a list off tables etc is easy enough to do using ALL_DEPENDENCIES,
> but I would like to know for each table whether the proc is updating,
> inserting etc.

If you took selection out of the equation you might be able to do it.

UPDATE TABLE
INSERT INTO TABLE
DELETE TABLE Those should be able to be found from the source if you did regexp replace of all whitespace characters down to say, a space, '\s+' works. There could be comment characters between these ie:

   UPDATE -- Updating because of X
   TABLE So get rid of those as well. Sort of an interesting exercise. You'd have to have a way to find beginning and ending of comments.

The dependencies found that aren't in that list would be a subset of the select objects. Its the objects in that DML list that also have selection that seems more complicated.

ANOTHER OPTION.
What you could try is create a new schema and create synonyms to all the objects in the Matrix schema. Grant CRUD from MATRIX to the new schema on all objects. Then, psuedo-code (Probably run as a user with appropriate privileges in both schemas):

   FOR obj in correct compilation order from MATRIX LOOP

       FOR priv IN SELECT DELETE UPDATE INSERT  LOOP
           1) AS MATRIX schema, revoke priv from obj;
           2) AS new schema, compile all objects force;
           3) Capture the errors;
           4) AS MATRIX schema, grant priv on obj to new schema;
       END LOOP;

   END LOOP; The captured errors would give you good information on the dependencies.

-- 
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news_at_netfront.net ---

Received on Sat Jan 16 2010 - 12:45:00 CST