Oracle SQL: GRANT

The Oracle DCL grant statement allows to give a user or a role an object privilege which is needed for the user or role to access the respective object.

The granted privileges can be queried through the data dictionary views dba_tab_privs, all_tab_privs and user_tab_privs.

A given privilege can be taken away with the revoke statement.

connect / as sysdba

create user usr_1
       identified by pw_1
       default tablespace data
       quota unlimited on data;

create user usr_2
       identified by pw_2;

grant  connect,
       create table
    to usr_1;

grant  connect
    to usr_2;

connect usr_1/pw_1

create table tab_1 (
   col_1   number,
   col_2   varchar2(10),
   col_3   varchar2(10),
   col_4   varchar2(10)
);

insert into tab_1 values (1, 'one', 'foo', 'important');
insert into tab_1 values (2, 'two', 'bar', 'data!'    );

grant select,
      update(       col_2, col_3)
   on tab_1
   to usr_2;

connect usr_2/pw_2

select
   *
from
   usr_1.tab_1;

update
   usr_1.tab_1
set
   col_2 = 'TWO'
where
   col_1 = 2;

--
-- ORA-01031: insufficient privileges
--
-- update
--    usr_1.tab_1
-- set
--    col_4 = '***'
-- where
--    col_1 = 1;

connect / as sysdba

drop user usr_2;
drop user usr_1 cascade;