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>
