Archive for the ‘User Security’ Category

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…

grants_on_tables

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.

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

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!

Jason

Advertisements