Oracle PL/SQL/User Previliege/Grant Permission

add the additional ALL clause to a GRANT statement in order to grant all possible privileges

  
GRANT SELECT,INSERT,UPDATE,DELETE on EMPLOYEES TO oe;
GRANT ALL ON EMPLOYEES TO oe;

--

Column-Level Object Privileges

  
GRANT UPDATE (product_id) ON sales01 TO salapati;
--

Create a user and grant the permission

 
create user sqle identified by password;
grant create session, dba to sqle;
connect sqle/password;
--DROP USER sqle;

Directory privileges: READ and WRITE

  
GRANT READ ON DIRECTORY bfile_dir TO userName;

grant all on directory DirName to User

  

grant all on directory ext_data_files to scott
/

grant one type or all types of privileges at once on any given object.

  

GRANT SELECT ON ods_process TO tester;
GRANT INSERT ON ods_process TO tester;
GRANT ALL ON ods_servers TO tester;
--

Grant Permissions

    
SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> GRANT SELECT, INSERT ON emp TO Alice WITH GRANT OPTION;
SQL>
SQL> drop user alice;
User dropped.
SQL>
SQL>
SQL>
SQL>

Grant permission to system

  
grant select on george.mytable to system;

Grant update permission

  

create table sprockets (
        id number,
        description varchar2(200),
        quantity    number )
    /
grant update (id, description)
       on sprockets
       to scott;
insert into sprockets (id, description, quantity)
values( 1, "Titanium", 25 );
    commit;
-- connect scott/tiger
update george.sprockets set quantity = 3;
update george.sprockets set description = "N";
drop table sprockets;

Materialized view privileges: SELECT and QUERY REWRITE

  
GRANT QUERY REWRITE TO userName;

Procedure, function, and package privileges: EXECUTE and DEBUG

  
GRANT EXECUTE ON employee_pkg TO hr;