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>

DBMS_JOB

I decided to do a worked example with DBMS_JOB as I had not used it before. This was done on an Oracle 9 database. DBMS_JOB was superseded by DBMS_SCHEDULER in Oracle 10. In the documentation for version 10, Oracle says that ... you should consider disabling DBMS_JOB by revoking the package execution privilege for users ...

First, create a table to store the output from the job I am about to create:

SQL> create table job_record

  2  (date_and_time date)

  3  /

 

Table created.

 

SQL>

Then note the start time:

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')

  2  time_now from dual

  3  /

 

TIME_NOW

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

14-JUL-11 14:36:18

 

SQL> 

The following query shows that no special permission is required to run DBMS_JOB:

SQL> select grantee from dba_tab_privs

  2  where owner = 'SYS'

  3  and table_name = 'DBMS_JOB'

  4  /

 

GRANTEE

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

PUBLIC

 

SQL>

Now create a job to insert SYSDATE into the job_record table every minute. Oracle assigns the job the number 6:

SQL> variable job_no number;

SQL> exec dbms_job.submit( -

> job       => :job_no, -

> what      => 'begin insert into job_record -

>               values(sysdate); end;', -

> next_date =>  sysdate + 1/1440, -

> interval  => 'sysdate + 1/1440');

 

PL/SQL procedure successfully completed.

 

SQL> print job_no

 

    JOB_NO

----------

         6

 

SQL> commit;

 

Commit complete.

 

SQL>

Confirm that parameter job_queue_processes is zero. This will stop the job running:

SQL> col value format a10

SQL> select value from v$parameter

  2  where name = 'job_queue_processes'

  3  /

 

VALUE

----------

0

 

SQL> 

Wait 5 minutes:

SQL> exec dbms_lock.sleep(300);

 

PL/SQL procedure successfully completed.

 

SQL>

Note the new time to show that 5 minutes have passed:

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')

  2  time_now from dual

  3  /

 

TIME_NOW

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

14-JUL-11 14:41:18

 

SQL>

Check that no rows have been written to the table as job_queue_processes is zero:

SQL> select to_char(date_and_time,'DD-MON-YY HH24:MI:SS')

  2  job_log from job_record

  3  /

 

no rows selected

 

SQL> 

Now run the job manually to show that it works:

SQL> exec dbms_job.run(6);

 

PL/SQL procedure successfully completed.

 

SQL>

And query the row that has been written to the table:

SQL> select to_char(date_and_time,'DD-MON-YY HH24:MI:SS')

  2  job_log from job_record

  3  /

 

JOB_LOG

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

14-JUL-11 14:41:18

 

SQL> 

Set job_queue_processes to be greater than zero and wait another 5 minutes to see if the job starts to run automatically:

SQL> alter system set job_queue_processes = 1

  2  /

 

System altered.

 

SQL> exec dbms_lock.sleep(300);

 

PL/SQL procedure successfully completed.

 

SQL> 

Note the new time to show that another 5 minutes have passed:

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')

  2  time_now from dual

  3  /

 

TIME_NOW

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

14-JUL-11 14:46:18

 

SQL>

And verify that several more rows have been written to the table as job_queue_processes is no longer zero:

SQL> select to_char(date_and_time,'DD-MON-YY HH24:MI:SS')

  2  job_log from job_record

  3  /

 

JOB_LOG

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

14-JUL-11 14:41:18

14-JUL-11 14:42:21

14-JUL-11 14:43:26

14-JUL-11 14:44:32

14-JUL-11 14:45:37

 

SQL> 

Finally, remove the job from the database:

SQL> exec dbms_job.remove(6);

 

PL/SQL procedure successfully completed.

 

SQL>