Database User Has Access to Tables Not Defined in its Roles and Privileges

Posted: December 16, 2016 in Basic Necessities, User Security

Good afternoon, Oracle Database Security Professionals!

I was tasked to investigate a Schema that had direct access to some tables it had no business having. I was told to remove this direct access. Should be a quick easy task, right?

  1. I looked at the user’s permissions with SQL Plus. The user had no direct access to any of the tables I was told it had access to.
  2. I created a user with the create like function in SQL Developer DBA View.
  3. I logged into my new user and ran the following query:SQL> select * from all_tables where owner in (‘Schema_1′,’Schema_2’);Wow! My new user had access to all the tables my customer told me it had access to. I thought about public synonyms but that would still require access to the table. I even tried to revoke access to one of the tables. Got something along the lines of “Can’t revoke a privilege that is not granted by Grantee”. I am a SYSDBA. How can I not revoke?
  4. Next, I looked at Grants on the individual table…


What’s this? Who is PUBLIC? Well, PUBLIC seemed to be granting access to this table to any Schema or User that can connect to the database. I was quite correct in my assumption. After confirming with the customer, what I did next was to revoke these table privileges as SYSDBA.

SQL> revoke all on “SCHEMA”.”TABLE_NAME” from “PUBLIC”;

The statement below also works but would require 11 steps instead of one.


Finally, I went back to my created user to confirm:

SQL> select * from all_tables where owner in (‘ODS’,’DOC’);

no rows selected

Now that is what I expected. I never knew there was a way to grant public use of objects in Oracle. Now I know. This may be news to some of you as I seem to be constantly learning what I don’t know about Oracle. The bottom line is, be careful of what you grant to PUBLIC.

Thanks for reading!



Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s