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;  

How To Create A User In Pl/SQL ?

 When we talk about Create a new user in PL/SQL then we think about System account (ADMINISTRATIVE ACCOUNT ). Once we connected as System account,
We issue the command

CREATE USER username IDENTIFIED BY userPassword;

Here you can see we issued a command CREATE USER ,the blue colour word username will be the new user name and the IDENTIFIED BY, Shows that this New user identified by this password.

FOR EXAMPLE

CREATE USER defuserali IDENTIFIED BY defuser123 ;

WHAT ARE PL/SQL FUNCTIONS?

 Function is similar to the procedure and it is a named PL/SQL block,The difference between Function and Procedure is, A procedure may or may not return the value but the function will return the value.

 Syntax To Create A Function


 

1. Implicit Cursors
2. Explicit Cursors