GRANT SELECT Updates LAST_DDL_TIME

DDL stands for Data Definition Language. The CREATE TABLE, ALTER TABLE and DROP TABLE statements are examples of DDL. LAST_DDL_TIME is a column in the USER_OBJECTS view. It records the date and time of the most recent DDL statement applied to the object in question. Even granting SELECT access on a table will update its LAST_DDL_TIME. You can see this in the example below, which I tested in an Oracle 11.2 database.

 

First I created a table and checked that its LAST_DDL_TIME matched the creation time:

 

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

18:39:50

 

SQL> create table tab1(col1 number)

  2  /

 

Table created.

 

SQL> select to_char(last_ddl_time,'hh24:mi:ss') time_now

  2  from user_objects

  3  where object_name = 'TAB1'

  4  /

 

TIME_NOW

--------

18:39:50

 

SQL>

 

Then I waited 10 seconds, noted the time again, ran some DDL on the table and checked that this had updated the LAST_DDL_TIME:

 

SQL> exec sys.dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

18:40:00

 

SQL> alter table tab1 add(col2 number)

  2  /

 

Table altered.

 

SQL> select to_char(last_ddl_time,'hh24:mi:ss') time_now

  2  from user_objects

  3  where object_name = 'TAB1'

  4  /

 

TIME_NOW

--------

18:40:00

 

SQL>

 

Finally, I waited a further 10 seconds, noted the time, did a GRANT SELECT on the table to another user and checked that the LAST_DDL_TIME had been updated again:

 

SQL> exec sys.dbms_lock.sleep(10);

 

PL/SQL procedure successfully completed.

 

SQL> select to_char(sysdate,'hh24:mi:ss') time_now

  2  from dual

  3  /

 

TIME_NOW

--------

18:40:10

 

SQL> grant select on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL> select to_char(last_ddl_time,'hh24:mi:ss') time_now

  2  from user_objects

  3  where object_name = 'TAB1'

  4  /

 

TIME_NOW

--------

18:40:10

SQL>

ORA-02205

I found some notes from a course I took in 1990. They said that it was only possible to GRANT ALTER or GRANT SELECT on a sequence. This seemed reasonable to me but I wanted to check if it was still the case. I did this test on Oracle 12.1. First I created a user who would own a sequence:

SQL> create user u1 identified by pw1

  2  /

 

User created.

 

SQL> grant create session, create sequence to u1

  2  /

 

Grant succeeded.

SQL>

Then I created a user who would be granted access to the sequence:

SQL> create user u2 identified by pw2

  2  /

 

User created.

SQL>

The first user created a sequence then did a GRANT ALL on it to the second user:

SQL> conn u1/pw1

Connected.

SQL> create sequence s1

  2  /

 

Sequence created.

 

SQL> grant all on s1 to u2

  2  /

 

Grant succeeded.

SQL>

I looked for the privileges which had been given to the second user but only found ALTER and SELECT. This confirmed what I had read in my notes:

SQL> select privilege from all_tab_privs

  2  where grantor = 'U1'

  3  and grantee = 'U2'

  4  and table_name = 'S1'

  5  /

 

PRIVILEGE                                             

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

ALTER                                                 

SELECT

SQL>

Finally I tried to GRANT UPDATE on the sequence but this failed with an ORA-02205:

SQL> grant update on s1 to u2

  2  /

grant update on s1 to u2

                *

ERROR at line 1:

ORA-02205: only SELECT and ALTER privileges are valid

for sequences

 

SQL>

GRANT UPDATE by Column

(Tested on Oracle 9.) When you grant update privilege on a table, you can restrict it to specific columns. First create a table and add a row to it:

SQL> conn andrew/reid
Connected.
SQL> create table emp
  2  (first_name varchar2(10),
  3  salary    number)
  4  /

Table created.

SQL> insert into emp values ('Boris', 10000)
  2  /

1 row created.

SQL> select * from emp
  2  /

FIRST_NAME    SALARY
---------- ----------
Boris          10000

SQL>

Then allow Fred to update the salary column:

SQL> grant select, update (salary) on emp to fred
  2  /

Grant succeeded.

SQL> 

When Fred tries to update the first_name column, he is unable to do so:

SQL> conn fred/bloggs
Connected.
SQL> --
SQL> update andrew.emp set first_name = 'David'
  2  /
update andrew.emp set first_name = 'David'
              *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

But he is able to update the salary as specified in the grant statement above:

SQL> update andrew.emp set salary = 12000
  2  /

1 row updated.

SQL> select * from andrew.emp
  2  /

FIRST_NAME    SALARY
---------- ----------
Boris          12000

SQL>