title: Oracle : check for read/write privileges, automated testing
body: <p>I have a question about Oracle and checking privileges.</p>
  
  <h2>Some background Info</h2>
  
  <p>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.</p>
  
  <p>Here is the query</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>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.</p>
  
  <h2>The problem</h2>
  
  <p>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</p>
  
  <pre><code>select * from all_tab_privs 
  where table_name = 'KNOWN_TABLE' and 
  grantee = 'CURRENT_USER'
  and privilege in ( 'SELECT', 'UPDATE', 'INSERT' );
  </code></pre>
  
  <p>but instead of getting back 3 rows for a table I know 100% that I can already select/insert/update it returns nothing. </p>
  
  <p>Any ideas? Thank you.</p>
  
  <h3>Disclaimer</h3>
  
  <p>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.</p>
  
  <p>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.</p>