plsql

GRANT and REVOKE Privileges in Oracle SQL

December 17, 2012 by techhoneyadmin

Oracle PLSQL allows us to GRANT and REVOKE privileges to/from an Oracle user. Privileges can be termed permissions given to a user so that he/she can interact or is able to modify the database.

In Oracle PLSQL terms:

Allowing or giving permission to a user is called as GRANT
Disallowing or taking away permission from a user is called REVOKE

The privileges can be a combination of any of the following statements or commands:

Privilege Name	Description
Select	Oracle user is allowed to query the database
INSERT	Oracle user can insert new records in database table
UPDATE	Oracle user can update records in database table
DELETE	Oracle user can delete records in database table
REFERENCES	Oracle user can create constraints that refers to a database table
ALTER	Oracle user can change the definition of table using ALTER statement
INDEX	Oracle user can create index on a database table using CREATE INDEX statement

GRANT Privilege to USER

Oracle PLSQL syntax to GRANT a Privilege to a user is:

GRANT PRIVILEGES ON OBJECT TO USER;

Example: Granting Privileges to a USER.

Suppose we want to grant SELECT, INSERT, UPDATE and DELETE privileges to ‘SCOTT’ user on employee table, then we have to execute the following statement;

GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO SCOTT;

The above statement will GRANT SELECT, INSERT, UPDATE and DELETE privileges to SCOTT user on employee table.

Example: Using ALL keyword for granting privileges to a user.

We can also use the ALL Keyword to grant all the privileges to a user.
e.g.

GRANT ALL ON employee TO SCOTT;

The above statement will grant all the privileges to SCOTT user on employee table.

Example: Grant privilege to all users

Oracle PLSQL allows us to grant privileges to all users at once using PUBLIC keyword
e.g.

GRANT SELECT ON employee TO PUBLIC;

Revoking Privileges from User:

Oracle PLSQL syntax to REVOKE Privilege from a user is:

REVOKE PRIVILEGES ON OBJECT FROM USER;

Example: Revoking Privileges from a USER.

Suppose we want to revoke SELECT, INSERT, UPDATE and DELETE privileges from ‘SCOTT’ user on employee table, then we have to execute the following statement;

REVOKE SELECT, INSERT, UPDATE, DELETE ON employee
FROM SCOTT;

Example: Using ALL keyword for revoking privileges to a user.

We can also use the ALL Keyword to revoke all the privileges from a user.
e.g.

REVOKE ALL ON employee FROM SCOTT;

The above statement will revoke all the privileges from SCOTT user on employee table.

Example: REVOKE privilege from all users

Oracle PLSQL allows us to revoke privileges from all users at once using PUBLIC keyword
e.g.

REVOKE SELECT ON employee FROM PUBLIC;

Granting Privileges to Oracle PLSQL Procedures and Functions

Oracle PLSQL allows us to GRANT PRIVILEGES to procedures and functions.

These privileges allow a user to execute a procedure or function.

Privilege Name	Description
EXECUTE	Oracle user can compile and execute the procedure or function

Oracle PLSQL syntax to GRANT EXECUTE privilege to a user on a function or procedure is:

GRANT EXECUTE ON object_name TO USER;

Example of GRANT EXECUTE privilege to SCOTT on ‘create_new_employee_proc’ procedure

GRANT EXECUTE ON create_new_employee_proc TO SCOTT;

Example to GRANT execute privilege to all users on ‘create_new_employee_proc’ using PUBLIC Keyword

GRANT EXECUTE ON create_new_employee_proc TO PUBLIC;

Revoking Privileges from Oracle PLSQL Procedures and Functions

Oracle PLSQL allows us to revoke privileges, on procedures and functions, which we have granted to users earlier using the REVOKE command.

Oracle PLSQL syntax to REVOKE privilege is:

REVOKE EXECUTE ON object_name FROM USER;

Example: REVOKE execute privilege from SCOTT on create_new_employee_proc

REVOKE EXECUTE ON create_new_employee_proc FROM SCOTT;

Example: Using PUBLIC keyword to revoke execute privilege from all users in create_new_employee_proc

REVOKE EXECUTE on create_new_employee FROM PUBLIC;

AFTER DELETE TRIGGER in Oracle PLSQL

December 14, 2012 by techhoneyadmin

Oracle PLSQL AFTER DELETE TRIGGER means that the trigger will get executed or triggered just after a DELETE operation is performed on the table for which the trigger is written.

Oracle PLSQL syntax to create a AFTER DELETE TRIGGER is:

CREATE OR REPLACE TRIGGER trigger_name
AFTER DELETE
ON table_name
[FOR EACH ROW]
DECLARE
–variable declaration section of code
BEGIN
–code to be triggered
EXCEPTION
WHEN..
–exception handling code
END;

Here in the above Oracle PLSQL trigger syntax the ‘trigger_name’ is the name given to trigger.

Some points to remember about PLSQL AFTER DELETE TRIGGER are:

We cannot create a AFTER DELETE TRIGGER on an Oracle PLSQL VIEW.

We cannot update :NEW values with AFTER DELETE TRIGGER.

We cannot update the :OLD values using AFTER DELETE TRIGGER.

Example of Oracle PLSQL AFTER DELETE TRIGGER

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	20000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

Let’s create a new table named ‘employee_count’ using the script as:

CREATE TABLE employee_count AS
  (no_of_employees   NUMBER
   ,department_name VARCHAR2(100));

At this moment we don’t have any data in the ‘employee_count’ and table.

Creating an Oracle PLSQL AFTER DELETE TRIGGER

Now let’s create an after delete trigger on ‘employee’ table so that whenever we are deleting any employee record from the ‘employee’ table (say ‘Sales’ department )the new count of employees in sales department is stored in ‘employee_count’ table with the department name.

For this purpose we can create a PLSQL AFTER DELETE TRIGGER as:

CREATE OR REPLACE TRIGGER employee_count_trigger
AFTER DELETE
ON employee
DECLARE
v_count_employees NUMBER;
BEGIN
  SELECT count(*) INTO v_count_employees
  FROM employee
  WHERE department = 'Sales';
  INSERT INTO employee_count
  VALUES (v_count_employees
          ,'Sales');
END;

Here we have created a PLSQL AFTER DELETE TRIGGER named ‘employee_count_trigger’ which will insert a record in the ‘employee_count’ table just after a record from the ‘employee’ table is deleted.

Let’s see PLSQL AFTER DELETE TRIGGER in action.

Let’s delete a row from the ‘employee’ table as:

DELETE FROM employee
WHERE employee_id = 107;

Once we run the above DELETE statement we can query the ‘employee’ as:

SELECT *
FROM employee;

We will get the following result:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	20000	Sales	5
108	Emp H	12000	Support

At this moment if we query the ‘employee_count’ table as

SELECT *
FROM employee_count;

We will get the following result:

NO_OF_EMPLOYEES	DEPARTMENT_NAME
3	Sales

Here we can see that while performing a delete operation on ‘employee’ table a record automatically gets inserted in the ‘employee_count’ table because of AFTER DELETE TRIGGER.

Please note that the values in CREATION_DATE and CREATED_BY columns may be different for you as they depend on system date and logged in user.

BEFORE DELETE TRIGGER in Oracle PLSQL

December 14, 2012 by techhoneyadmin

Oracle PLSQL BEFORE DELETE TRIGGER means that the trigger will get executed or triggered just before a DELETE operation is performed on the table for which the trigger is written.

Oracle PLSQL syntax to create a BEFORE DELETE TRIGGER is:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE DELETE
ON table_name
[FOR EACH ROW]
DECLARE
–variable declaration section of code
BEGIN
–code to be triggered
EXCEPTION
WHEN..
–exception handling code
END;

Here in the above Oracle PLSQL trigger syntax the ‘trigger_name’ is the name given to trigger.

Some points to remember about PLSQL BEFORE DELETE TRIGGER are:

We cannot create a BEFORE DELETE TRIGGER on an Oracle PLSQL VIEW.

We can update :NEW values with BEFORE DELETE TRIGGER.

We cannot update the :OLD values using BEFORE DELETE TRIGGER.

Example of Oracle PLSQL BEFORE DELETE TRIGGER

Suppose we create a table named ‘new_employee’ using the script below:

CREATE TABLE new_employee
(
  employee_id    NUMBER
  ,employee_name VARCHAR2(1000)
  ,creation_date DATE
  ,created_by    VARCHAR2(1000)
);

If we query the ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID

EMPLOYEE_NAME

CREATION_DATE

CREATED_BY

Also we create a ‘new_employee_duplicate’ table using the script as:

CREATE TABLE new_employee_duplicate AS
        (SELECT *
         FROM new_employee
        );

If we query the new_employee_duplicate table as:

SELECT *
FROM new_employee_duplicate;

We will get the following result:

EMPLOYEE_ID

EMPLOYEE_NAME

CREATION_DATE

CREATED_BY

At this moment we don’t have any data in the ‘new_employee’ and table ‘new_employee_duplicate’ table.

Creating an Oracle PLSQL BEFORE DELETE TRIGGER

Now let’s create a before delete trigger on ‘new_employee’ table so that whenever we are deleting any employee record from the ‘new_employee’ table the same records gets stored in the ‘new_employee_duplicate’ table.

For this purpose we can create a PLSQL BEFORE DELETE TRIGGER as:

CREATE OR REPLACE TRIGGER new_employee_trigger
BEFORE DELETE
ON new_employee
FOR EACH ROW
BEGIN
  INSERT INTO new_employee_duplicate
  VALUES (:old.employee_id
          ,:old.employee_name
          ,:old.creation_date
          ,:old.created_by);
END;

Here we have created a PLSQL BEFORE DELETE TRIGGER named ‘new_employee_trigger’ which will insert a record in the new_employee_duplicate’ table just before a record from the ‘new_employee’ table is deleted.

Let’s see PLSQL BEFORE DELETE TRIGGER in action.

Let’s add a row in ‘new_employee’ table as:

INSERT INTO new_employee
VALUES
(1, 'Emp ABCD',SYSDATE,'SCOTT');

Once we run the above INSERT statement we can query the ‘new_employee’ as:

SELECT *
FROM new_employee;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp ABCD	12/14/2012 4:38:32 PM	SCOTT

At this moment if we query the ‘new_employee_duplicate’ table as

SELECT *
FROM new_employee_duplicate;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY

Now, let’s delete the employee record from the ‘new_employee’ table.

DELETE FROM new_employee;

Now, if we query ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY

And if we query the ‘new_employee_duplicate’ table as:

SELECT *
FROM new_employee_duplicate;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp ABCD	12/14/2012 4:38:32 PM	SCOTT

Here we can see that while performing a delete operation on ‘new_employee’ table a record automatically gets inserted in the ‘new_employee_duplicate’ table because of BEFORE DELETE TRIGGER.

Please note that the values in CREATION_DATE and CREATED_BY columns may be different for you as they depend on system date and logged in user.

AFTER UPDATE TRIGGER in Oracle PLSQL

December 13, 2012 by techhoneyadmin

Oracle PLSQL AFTER UPDATE TRIGGER means that the trigger will get executed or triggered just after an UPDATE operation is performed on the table for which the trigger is written.

Oracle PLSQL syntax to create an AFTER UPDATE TRIGGER is:

CREATE OR REPLACE TRIGGER trigger_name
AFTER UPDATE
ON table_name
[FOR EACH ROW]
DECLARE
–variable declaration section of code
BEGIN
–code to be triggered
EXCEPTION
WHEN..
–exception handling code
END;

Here in the above Oracle PLSQL trigger syntax the ‘trigger_name’ is the name given to AFTER INSERT TRIGGER.

Some points to remember about PLSQL AFTER UPDATE TRIGGER are:

We cannot create an AFTER INSERT TRIGGER on an Oracle PLSQL VIEW.

We cannot update :NEW values with AFTER INSERT TRIGGER.

We cannot update the :OLD values using AFTER INSERT TRIGGER.

Example of Oracle PLSQL AFTER UPDATE TRIGGER

Suppose we create a table named ‘new_employee’ using the script below:

CREATE TABLE new_employee
(
  employee_id NUMBER
  ,employee_name VARCHAR2(1000)
  ,creation_date DATE
  ,created_by VARCHAR2(1000)
);

If we query the ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID

EMPLOYEE_NAME

CREATION_DATE

CREATED_BY

Also we create a duplicate table of ‘new_employee’ table as new_employee_duplicate’ using the script below:

CREATE TABLE new_employee_duplicate
AS(SELECT *
      FROM new_employee);

If we query the ‘new_employee_duplicate’ table as

SELECT *
FROM new_employee_duplicate;

We will get the following output:

EMPLOYEE_ID

EMPLOYEE_NAME

CREATION_DATE

CREATED_BY

At this moment we don’t have any data in ‘new_employee’ and ‘new_employee_duplicate’ tables.

Now let’s create a trigger on ‘new_employee’ table so that whenever we are updating any employee record in the ‘new_employee’ table the same record also gets stored in ‘new_employee_duplicate’ table.

For this purpose we can create a PLSQL AFTER UPDATE TRIGGER as:

CREATE OR REPLACE TRIGGER new_employee_trigger
AFTER UPDATE
ON new_employee
FOR EACH ROW
DECLARE
  v_creator_name VARCHAR2(1000);
  v_creation_date DATE;
BEGIN
--Getting the name of the current logged in User
  SELECT USER INTO v_creator_name
  FROM dual;
--setting system date in v_creation_date
  v_creation_date := sysdate;
--Inserting data the new_employee_duplicate table
  INSERT INTO new_employee_duplicate
  VALUES (:new.employee_id
          ,:new.employee_name
          ,v_creation_date
          ,v_creator_name);
END;

Here we have created a PLSQL AFTER UPDATE TRIGGER named ‘new_employee_trigger’ which will insert a record in the ‘new_employee_duplicate’ table as soon as update operation is performed on ‘new_employee’ table.

Let’s see PLSQL AFTER UPDATE TRIGGER in action.

Let’s add a row in ‘new_employee’ table as:

INSERT INTO new_employee
VALUES
(1, 'Emp ABCD',sysdate,'SCOTT');

Once we run the above INSERT statement we can query the ‘new_employee’ as:

SELECT *
FROM new_employee;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp ABCD	12/12/2012 5:34:49 PM	SCOTT

Also if we query the ‘new_employee_duplicate’ table as

SELECT *
FROM new_employee_duplicate;

We will get the following output:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY

Let’s update the record in ‘new_employee’ table as:

UPDATE new_employee
SET employee_name = 'Emp EFGH';

Now if we query the ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp EFGH	12/12/2012 5:34:49 PM	SCOTT

Also, if we query the ‘new_employee_duplicate’ table we will get:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp EFGH	12/12/2012 5:34:49 PM	SCOTT

Here using the Oracle PLSQL AFTER UPDATE TRIGGER we can see that in the ‘new_employee_duplicate’ table a record got inserted as soon as we updated a record in ‘new_employee’ table.

Please note that the values in CREATION_DATE and CREATED_BY columns may be different for you as they depend on system date and logged in user.

BEFORE UPDATE TRIGGER in Oracle PLSQL

December 13, 2012 by techhoneyadmin

Oracle PLSQL BEFORE UPDATE TRIGGER means that the trigger will get executed or triggered just before an UPDATE operation is performed on the table for which the trigger is written.

Oracle PLSQL syntax to create a BEFORE UPDATE TRIGGER is:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE UPDATE
ON table_name
[FOR EACH ROW]
DECLARE
–variable declaration section of code
BEGIN
–code to be triggered
EXCEPTION
WHEN..
–exception handling code
END;

Here in the above Oracle PLSQL trigger syntax the ‘trigger_name’ is the name given to trigger.

Some points to remember about PLSQL BEFORE UPDATE TRIGGER are:

We cannot create a BEFORE UPDATE TRIGGER on an Oracle PLSQL VIEW.

We can update :NEW values with BEFORE UPDATE TRIGGER.

We cannot update the :OLD values using BEFORE UPDATE TRIGGER.

Example of Oracle PLSQL BEFORE UPDATE TRIGGER

Suppose we create a table named ‘new_employee’ using the script below:

CREATE TABLE new_employee
(
  employee_id    NUMBER
  ,employee_name VARCHAR2(1000)
  ,creation_date DATE
  ,created_by    VARCHAR2(1000)
);

If we query the ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID

EMPLOYEE_NAME

CREATION_DATE

CREATED_BY

At this moment we don’t have any data in the ‘new_employee’ table

Creating an Oracle PLSQL BEFORE UPDATE TRIGGER

Now let’s create a plsql Before Update trigger on ‘new_employee’ table so that whenever we are updating any employee record, the CREATION_DATE and CREATED_BY columns for that record gets filled automatically.

For this purpose we can create a PLSQL BEFORE UPDATE TRIGGER as:

CREATE OR REPLACE TRIGGER new_employee_trigger
BEFORE UPDATE
ON new_employee
FOR EACH ROW
DECLARE
  v_creator_name VARCHAR2(1000);
BEGIN
--Getting the name of the current logged in User
  SELECT USER INTO v_creator_name
  FROM dual;
--setting system date in created_date  column
  :new.creation_date := sysdate;
--setting the user name to created_by column
  :new.created_by    := v_creator_name;
END;

Here we have created a PLSQL BEFORE UPDATE TRIGGER named ‘new_employee_trigger’ which will update the CREATED_BY and CREATION_DATE in the ‘new_employee’ table.

Let’s see PLSQL BEFORE UPDATE TRIGGER in action.

Let’s add a row in ‘new_employee’ table as:

INSERT INTO new_employee
VALUES
(1, 'Emp ABCD',NULL,NULL);

Once we run the above INSERT statement we can query the ‘new_employee’ as:

SELECT *
FROM new_employee;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp ABCD

Notice that the CREATION_DATE and CREATED_BY Columns have NULL values.

Now, let’s update the employee record in the ‘new_employee’ table.

UPDATE new_employee
SET employee_name = 'Emp EFGH';

Now, if we query ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp ABCD	12/13/2012 3:46:33 PM	SCOTT

Here we can see that after performing insert operation on ‘new_employee’ table the CREATION_DATE and CREATED_BY columns were having NULL values, but as soon as we performed an UPDATE operation, the BEFORE UPDATE TRIGGER got fired and updated the record for CREATION_DATE and CREATED_BY columns.

Please note that the values in CREATION_DATE and CREATED_BY columns may be different for you as they depend on system date and logged in user.