Oracle : check for read/write privileges, automated testing

Question

I have a question about Oracle and checking privileges.

Some background Info

I wrote some php scripts that will 'test' various things in our environment. One of those tasks is checking that a user has execute privileges on a procedure and that the procedure is valid/compiled.

Here is the query

select ao.object_name, utp.grantee, ao.status, utp.privilege
from all_objects ao, user_tab_privs utp 
where utp.owner = ao.owner and
ao.object_name = utp.table_name and
upper( ao.object_name ) = :object_name and 
ao.object_type = 'PACKAGE' and
utp.privilege = 'EXECUTE' and
ao.status = 'VALID'

This has worked well and has saved us time on procedure privileges; I do realize now that I can also double check the all_tab_privs to check execute access as well.

The problem

Now my question is, how do I do something similar with tables? We ran into an issue where a certain user had SELECT privs on a table but not UPDATE/INSERT privs. How can I check for each of these privileges individually. I've looked into all_tab_privs but haven't found it shows me what I want. It has procedures I can execute but when I check to see if a known table is there it isn't. For example, I'll run the following

select * from all_tab_privs 
where table_name = 'KNOWN_TABLE' and 
grantee = 'CURRENT_USER'
and privilege in ( 'SELECT', 'UPDATE', 'INSERT' );

but instead of getting back 3 rows for a table I know 100% that I can already select/insert/update it returns nothing.

Any ideas? Thank you.

Disclaimer

I am aware that I could just try inserting/updating data and then deleting it but I'd rather not do this. I'd rather not leave any trace since these scripts will run periodically, should be repeatable, and shouldn't alter the state of any piece of data, even if it's just a sequence on the table.

Also, if you could provide a 'list' of possible queries that I can use to determine privileges that would be fine. For example, to determine if i have select access run query 1, 2 and 3. If either returns data then you have select privs and so on for insert/update.

Answer 1

This looks rather optimistic to me, as the role issue could get really complex, particularly if roles ever get password protected, and I'd never really trust the method 100% without actually trying the DML.

It might be more simple to try queries such as:

select count(*)
from   schema_name.table_name
where  1=0;

insert into schema_name.table_name
select *
from   schema_name.table_name
where  1=0;

delete from schema_name.table_name
where  1=0;

update schema_name.table_name
set    column_name = column_name
where  1=0;

I believe that such queries would fail if no privileges were granted (no database handy to check it), and they would never modify any data.

By the way, the ANY privileges are generally regarded as a security problem, so you might like to fail the system if any user is granted them.