Search code examples
sqloracle-databaseprivileges

How SELECT ANY TABLE privilege work in Oracle?

I would like to know how the privilege SELECT ANY TABLE works internally in Oracle.

Is it treated as a single privilege? Or is it equivalent to make a GRANT SELECT ON MyTable TO MyUser for each table?

As example, I would like to know if this work :

GRANT SELECT ANY TABLE TO PUBLIC;
REVOKE ALL ON MY_TABLE FROM PUBLIC;

Would I still have access to MY_TABLE from any user after those queries?

Solution

  • Yes, all users would still be able to query MY_TABLE.

    You are looking at different privilege types:

    The main types of user privileges are as follows:

    • System privileges—A system privilege gives a user the ability to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the system privilege CREATE TABLE permits a user to create tables in the schema associated with that user, and the system privilege CREATE USER permits a user to create database users.
    • Object privileges—An objectprivilege gives a user the ability to perform a particular action on a specific schema object. Different object privileges are available for different types of schema objects. The privilege to select rows from the EMPLOYEES table or to delete rows from the DEPARTMENTS table are examples of object privileges.

    SELECT ANY TABLE is a system privilege that allows the grantee to:

    Query tables, views, or materialized views in any schema except SYS. Obtain row locks using a SELECT ... FOR UPDATE.

    When you grant that it is a standalone single privilege, visible in dba_sys_privs. When Oracle decides if the user is allowed to access a table it can look first at system privleges, and only goes on to look for specific object privileges (visible in dba_tab_privs) if there isn't a system privilege that allows the action being performed.

    System privileges are not translated into individual privileges on each object in the database - maintaining that would be horrible, as creating a new object would have to automatically figure out who should be granted privileges on it based on the system privilege; and it would mean that you couldn't tell the difference between that and individually granted privileges. So, for instance, if you explicitly granted select privs on a specific table, then the user was granted SELECT ANY TABLE, and then they had SELECT ANY TABLE revoked - what happens to the previous explicit grant?

    Your scenario is basically the same, except you've specifed all privileges on the object to be revoked. If those are the only two commands involved then PUBLIC has no explicit privileges on MY_TABLE so revoking doesn't really do anything; but if any explicit privileges on that table had been granted then they would be revoked. That has no impact on the higher-level SELECT ANY TABLE system privileg though.

    Privileges are cummulative; revoking a privilege on a specific object doesn't block access to that object, it just removes one possible access route.

    Incidentally, hopefully you've used a contrived example, as such powerful system privileges should be granted sparingly and only when really needed. Letting any user query any table in your database potentially blows a big hole in the security model. Again from the docs:

    Oracle recommends that you only grant the ANY privileges to trusted users

    and

    Oracle recommends against granting system privileges to PUBLIC.

    and read more in the database security guide.