DeFuser.Tk Blog

 
HOW TO GRANT PRIVILEGES IN ORACLE PL/SQL?

In our previous post we discussed about CREATE USER statement for new user in PL/SQL. now we will talk about GRANT statement that Grant privileges and roles at same time to the new user.

  FOR EXAMPLE

  1 >  GRANT ROLES
                                We will assign the privileges to  New user account starting with Connect                                                 Role

GRANT CONNECT TO defuserali ;

if you want to make a powerful user then we can grant him Resource and DBA role

GRANT CONNECT, RESOURCE, DBA TO defuserali ;

2 > Assigning Privileges

After granted roles we will check the user for their granted roles by connecting to DATABASE  with creating a session using GRANT CREATE SESSION like as

GRANT CREATE SESSION GRANT ANY PRIVILEGE TO defuserali ; GRANT UNLIMITED TABLESPACE TO defuserali ;

3 > Table Privileges Now we will give him table privileges By issuing following statement

GRANT SELECT, INSERT, UPDATE, DELETE ON schema.books TO defuserali;