Friday, June 22, 2012

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>

No comments: