On Tap for Today:

I had a chance to have a little fun today browsing these database tables:

sys.obj$,
sys.objauth$,
sys.user$, and
table_privilege_map.

This last migration, the result of at least two other blogs posts, has forced me to think about how we do object migrations.  It’s one of the few times that we had a consultant do ALL the work.  Because the consultant had the APPS user name and password (not ready to talk about that right now), he was able to set up a considerable amount of the new XML Gateway interface without engaging the development team.  Unfortunately, this resulted in us not knowing what was happening behind the scenes as it related to the SYNONYMS, GRANTS, and OBJECT OWNERS.

Earlier in the week, I saw this post by @thatJeffSmith: Grants Now Included in SQL Developer SQL Pages, and began to think about how could I “see” what this developer was doing as it related to the database object he was creating.  Unfortunately, I didn’t see a way to also include SYNONYMS in the export referenced in the post (disclaimer: I didn’t really look that hard).  The consultant’s documentation was relatively good, plus I already had trigger in place to see what DDLs where happening and by whom. This was to satisfy some previous audit requirements.  My biggest concern was missing some GRANT or something during the migration through QA and to Production and something not work.  In any event, I found some great queries on the internet and was able to piece this together:

SELECT

so.name “Object Name”
, DECODE(so.TYPE#, 1, ‘INDEX’, 2, ‘TABLE’, 4, ‘VIEW’, 5, ‘SYNONYM’, 6, ‘SEQUENCE’,
7, ‘PROCEDURE’, 8, ‘FUNCTION’, 9, ‘PACKAGE’,
11, ‘PACKAGE BODY’, 28, ‘JAVA SOURCE’, 29, ‘JAVA CLASS’, 30, ‘JAVA RESOURCE’,
56, ‘JAVA DATA’, ‘UNDEFINED’) “Object Type”  — There are quite a few of these which you can look up. I only used what I was looking for.
, so.type#  — not really necesssary
, tpm.name “Grants/Privs”
, su.name “Object Owner”
FROM sys.obj$ so
, sys.objauth$ soa
, sys.user$ su
, table_privilege_map tpm
WHERE 1=1
AND so.owner# = su.user#
AND so.obj# = soa.obj#(+)   
AND soa.privilege# = tpm.privilege (+) 
AND so.name LIKE  :objName   — enter your object name.  You can also edit this to include a list of objects.
ORDER BY 1, 2, 4, 5

 

Once I got this all figured out, I ran this in QA using SQL Developer, exported it to Excel and gave to my DBA as a guide for the migration to Production.  Here is a sample of the output:

 We were able to successfully pass our Change Control Board approval process and the objects were migrated to Production.  Eventually we will use SQL Developer for that too.  Here is a post about that.  Once is Production, I was again able to run this in Production (let’s not talk about how I can query SYS in Production).  Taking the two exports: QA and Production, I wrote a quick Excel formula to compare each row.  If they matched, then I can be relatively confident the migration was successful.

 

My Consistency Check formula: =IF(AND(A3=G3,B3=H3,C3=I3,D3=J3,E3=K3),”Match”,”No Match”)

While I’m sure there are better ways to do this, it was a great distraction for a couple of hours.

Enjoy: