Oraclewizard.com, Inc is a Veteran Owned HUBZONE Certified Small Business, Principle owner Robert Lockard is an Oracle ACE Director and Winner of the 2015 Oracle Developers Choice Award for Database Design.
It’s been a year long process now the book is finally been released. There are a few things I would have written different and a few other subjects I would have liked to cover. Perhaps that will come in my next book or future posts.
In this book we cover Secure Coding, setting up Encryption, and audit. We also dive deep into performing privilege analysis.
Here is a quick tip on Oracle privilege analysis. Frequently I want to find out all of the ways a user can get to an object for any privilege. DBA_TAB_PRIVS and DBA_ROLE_PRIVS are the two views I go to. I want to also see all the privileges that are granted on any object. This is good for starting at the user tracking privileges to the object, it’s also good for starting at an object and walking back to the user. This query does a pivot on the users and roles to get the path to the object and what privileges are associated with that path. ===========================================================================
"'ON COMMIT REFRESH'" OCR,
"'INHERIT PRIVILEGES'" IPRV,
"'QUERY REWRITE'" QR,
FROM (SELECT R.GRANTEE "GRANTEE_TO",
FROM DBA_TAB_PRIVS T,
WHERE T.GRANTEE = R.GRANTED_ROLE (+)
AND t.grantee != 'SYS'
AND t.grantee != 'SYSTEM'
AND R.GRANTEE != 'SYS'
AND R.GRANTEE != 'SYSTEM' )
PIVOT (COUNT(PRIVILEGE) FOR PRIVILEGE IN ('SELECT',
'ON COMMIT REFRESH',
ORDER BY TABLE_NAME;