Posted by So I tweeted this out on Friday
and this reflects one of my joys with working with Oracle technology. The field is so broad and so deep, there is never a point where you would ever say “Yup…I’ve finished…I’ve learnt it all”. Many people think that as we get more experience with the product, that finding new things could only mean we pick up more information about the deepest internals or most niche of features, but I disagree. One of the joys of working with AskTom is that I get to challenge myself every day when people ask questions about subject matter that I’m unfamiliar with, and during those investigations you discover new things.
This particular snippet was not from an AskTom question, but I was just doing some tinkering with PLSQL security and stumbled across a new data dictionary view (where “new” simply means that I hadn’t known about it, because it dates back to Oracle 9 
). When it comes to looking at privileges and grants, my go-to view has always been USER_TAB_PRIVS or the ALL_ / DBA_ equivalent. Here’s an example of those views with a couple of schemas:
SQL> grant resource, connect to mike identified by mike;
Grant succeeded.
SQL> alter user mike quota 100m on users;
User altered.
SQL> grant resource, connect to sue identified by sue;
Grant succeeded.
SQL> alter user sue quota 100m on users;
User altered.
SQL> create table mike.t1(x int);
Table created.
SQL> create table mike.t2(x int);
Table created.
SQL> create table sue.t1(x int);
Table created.
SQL> create table sue.t2(x int);
Table created.
SQL> grant select on mike.t1 to sue;
Grant succeeded.
SQL> grant select on sue.t1 to mike;
Grant succeeded.
SQL> grant select on scott.emp to mike;
Grant succeeded.
SQL> grant select on scott.dept to sue;
Grant succeeded.
SQL> conn sue/sue@db19_pdb1
Connected.
SQL> select grantee, owner, table_name from user_tab_privs;
GRANTEE OWNER TABLE_NAME
------------ ------------------------------ ------------------------------
SUE MIKE T1
MIKE SUE T1
SUE SCOTT DEPT
PUBLIC SYS SUE
4 rows selected.
SQL> select grantee, grantor, table_name from all_tab_privs
2 where grantee != 'PUBLIC';
GRANTEE GRANTOR TABLE_NAME
------------ ------------ ------------------------------
SUE MIKE T1
MIKE SUE T1
SUE SCOTT DEPT
SODA_APP XDB JSON$USER_COLLECTION_METADATA
SODA_APP XDB DBMS_SODA_ADMIN
SODA_APP XDB DBMS_SODA_USER_ADMIN
6 rows selected.
However, because the xxx_TAB_PRIVS shows the totality of all privileges for a user, namely, privileges either offered out to others, or granted from others, whenever it came to answering the simple question: “What privileges has this user offered out to the world?” I would need to add a predicate to that view.
And thus…after 25 years, lo and behold, I have discovered the USER_TAB_PRIVS_MADE view. As the name suggests, it is what privileges (or grants) I have “made” to others.
SQL> select grantee, grantor, table_name from user_tab_privs_made;
GRANTEE GRANTOR TABLE_NAME
------------ ------------ ------------------------------
MIKE SUE T1
PUBLIC SUE SUE
2 rows selected.
As an aside, if the second row looks a little confusing, we can dig a little deeper to see what that privilege is.
SQL> select * from user_tab_privs_made
2 @pr
==============================
GRANTEE : MIKE
TABLE_NAME : T1
GRANTOR : SUE
PRIVILEGE : SELECT
GRANTABLE : NO
HIERARCHY : NO
COMMON : NO
TYPE : TABLE
INHERITED : NO
==============================
GRANTEE : PUBLIC
TABLE_NAME : SUE
GRANTOR : SUE
PRIVILEGE : INHERIT PRIVILEGES
GRANTABLE : NO
HIERARCHY : NO
COMMON : NO
TYPE : USER
INHERITED : NO
PL/SQL procedure successfully completed.
A description of the INHERIT PRIVILEGES privileges and the need for it even I never granted it explicitly is covered in a video I published a while back
And just for completeness, there is a partnering view USER_TAB_PRIVS_RECD for the privileges you have RECeiveD
Learning is not a spectator sport 
Nice !
Whaaaaaa? But I knew it all along!!!!! I knew *one* thing that Connor Mcdonald didn’t. Absolutely. Unbelievable.