oracle table_privileges values

Question

Does anybody know the values that are given in the table_privileges? I already found out what "A" means. But I did not find out for what the "S" stands for. I think this isnt documented. It has something to do with update privileges on particular columns.

Answer 1

The thing you are missing is that we can grant UPDATE on a subset of a table's columns.

First of all, let's just grant SELECT on a table. The value of UPDATE_PRIV is 'N', for None:

SQL> grant select on t23 to mr_x;

Grant succeeded.


SQL> select select_priv, update_priv 
  2  from table_privileges
  3  where table_name = 'T23'
  4  /

S U
- -
Y N

SQL>

Now, if I grant UPDATE on a single column the value of UPDATE_PRIV is 'S', presumably for Some:

SQL> grant update (col2) on t23 to mr_x
  2  /

Grant succeeded.

SQL> select select_priv, update_priv 
  2  from table_privileges
  3  where table_name = 'T23'
  4  /

S U
- -
Y S

SQL>

Finally, I grant UPDATE on the whole table the value of UPDATE_PRIV is 'A', for All:

SQL> grant update  on t23 to mr_x
  2  /

Grant succeeded.

SQL> select select_priv, update_priv 
  2  from table_privileges
  3  where table_name = 'T23'
  4  /

S U
- -
Y A

SQL> 

Answer 2

I'm sorry but having noticed an answer that @JustinCave gave to this very question back in 2005 I have to post it.

From the SQL Reference documentation on table_privileges

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2486.htm#1318903

"TABLE_PRIVILEGES contains information on grants on objects for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee. This view is included for compatibility with Oracle version 6. Oracle Corporation recommends that you do not use this view."

Given that Oracle recommends you not use this view, I would strongly suggest that you use the DBA_TAB_PRIVS view instead. The information there should be a bit easier to decipher.