Grant Any Object Privilege

This example was tested on Oracle 11.2. It demonstrates the grant any object privilege system privilege. First create three users:

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

User created.

SQL> grant create session, create table

  2  to andrew

  3  /

Grant succeeded.

SQL> create user fred

  2  identified by bloggs

  3  /

User created.

SQL> grant create session, grant any object privilege

  2  to fred

  3  /

Grant succeeded.

SQL> create user nosey

  2  identified by parker

  3  /

User created.

SQL> grant create session to nosey

  2  /

Grant succeeded.

SQL>

Andrew Reid has won a lot of money on the lottery. He creates a table to record his bank balance but wants to keep it secret:

SQL> conn andrew/reid

Connected.

SQL> create table bank_account

  2  as select 1234567 balance

  3  from dual

  4  /

Table created.

SQL> select balance from bank_account

  2  /

   BALANCE

----------

   1234567

SQL>

Nosey Parker hears a rumour about the lottery win and tries to look at Andrew's bank account:

SQL> conn nosey/parker

Connected.

SQL> select balance from andrew.bank_account

  2  /

select balance from andrew.bank_account

                           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

Nosey knows that Fred Bloggs has the grant any object privilege system privilege and asks him to help. Fred grants select privilege on Andrew's bank_account table to Nosey: 

SQL> conn fred/bloggs

Connected.

SQL> grant select on andrew.bank_account to nosey

  2  /

Grant succeeded.

SQL>

Nosey can then see how much money Andrew has:

SQL> conn nosey/parker

Connected.

SQL> select balance from andrew.bank_account

  2  /

   BALANCE

----------

   1234567

SQL>

You might think that Fred would appear in dba_tab_privs as the grantor of this privilege as he ran the grant statement but you would be wrong. Andrew appears as the grantor instead: 

SQL> conn / as sysdba

Connected.

SQL> select grantor from dba_tab_privs

  2  where grantee = 'NOSEY'

  3  and owner = 'ANDREW'

  4  and table_name = 'BANK_ACCOUNT'

  5  and privilege = 'SELECT'

  6  /

GRANTOR

------------------------------

ANDREW

SQL>