A new dictionary view for privileges

Posted by

So I tweeted this out on Friday

image

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 Smile). 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, Smile 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

2 comments

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.