DDL - Part 6 - GRANT REFERENCES,GRANT UPDATE,GRANT INDEX

GRANT Privileges:

V2R2 introduced some new privileges including REFERENCES, INDEX and expanded UPDATE privileges.

To create references to existing tables, a REFERENCES privilege is needed.

GRANT REFERENCES ON employee TO SQL01; /* On all columns */
GRANT REFERENCES (employee_number) ON employee to SQL01;           /* On employee_number only */

GRANT REFERENCES (ALL BUT employee_number) on employee to SQL01; /* On all but employee_number */

GRANT UPDATE ON employee TO SQL01;

GRANT UPDATE (salary_amount) ON employee to SQL01; /* On salary_amount only */

GRANT UPDATE (ALL BUT salary_amount) ON employee to SQL01; /* On All except salary_amount  */

Above grant commands can be used to allow or disallow updates on specific columns

GRANT INDEX ON employee to SQL01'; --> To CREATE or DROP indexes on a table

Example:

CREATE SET TABLE EDW_RESTORE_TABLES.TEST ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      column1 INTEGER NOT NULL PRIMARY KEY)

;

REVOKE REFERENCES ON TEST FROM SUKUL;

/* We revoke the REFERENCES privileges to the USER SUKUL*/

CREATE TABLE EDW_ReSTORE_TABLEs.TEST2

(

column1 integer NOT NULL CONSTRAINT pk_2 PRIMARY KEY,

column2 integer NOT NULL CONSTRAINT Uk_2 UNIQUE,

column3 integer CONSTRAINT ch2 CHECK(column3 > 100),

column4 integer constraint fk_1 references test(column1)

);

As we don’t have REFERENCES privileges the above query fails with following error:

CREATE TABLE Failed. 5315:  The user does not have REFERENCES access to EDW_RESTORE_TABLES.TEST.column1.