How Select Any Table Privilege Work in Oracle

1. Introduction

In this article, we will discuss about the Select Any Table Privilege in Oracle.

2. Select Any Table Privilege

Select Any table privilege is a system privilege which allows the grantee to query any Table, View or a Materialized views from any schema except the sys Schema.

It is a standalone single privilege visible in dba_sys_privs.

When Oracle wants to check if the user is allowed to access an object, it first checks in the system privileges. If the user is privileged, then Oracle allows the select operation…

..If the user is not having the system privilege, then object level privilege of the user is checked(Visible in dba_tab_privs) to check if the user is allowed to access the object. If yes, then it allows select operation.

2.1. Grant Select Any Table Privilege

The following SQl query shows how to Grant Select Any Table privilege to a user.

GRANT SELECT ANY TABLE TO USERNAME; 

Caution: Do not Grant Select ANY table privilege to Public as this privilege is only for trusted users.

2.2. Revoke Select Any Table Privilege

The follow SQL query shows how to Revoke Select Any table privilege to a user.

REVOKE SELECT ANY TABLE FROM USERNAME;

3. Conclusion

To Summarize, we have discussed about the Select Any table privilege in Oracle, Its Grant and Revoke Syntax and the cautions before using it. For more details about Roles and Privileges, refer Oracle Guide in this link.

If you still have questions, feel free to ask in our SQL forum.

FAQs

Is it possible to Grant select any table with Grant Option?

No, its not possible to Grant select any table with Grant option as it is a system privilege.

Is it possible to Grant select any table with Admin Option?

Yes, its possible to Grant Select any table with Admin option as select any table is a system privilege.