Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

OBJECT PRIVILEGES FOR PROCEDURE IN ORACLE

Leave a Comment / SQL, OBJECT PRIVILEGES, PRIVILEGES_ROLES / By

OBJECT PRIVILEGES FOR PROCEDURE IN ORACLE

Privileges are basically rights to access any object or perform any operation through a procedure.

EXECUTE is the object privileges to run a procedure. This includes:

  • A standalone Procedure
  • A standalone Function
  • A Packaged Procedure
  • A Packaged Function

EXECUTE ON STANDALONE PROCEDURE

Creating a standalone procedure on HR schema.

CONN HR/HR@ORCL;

CREATE OR REPLACE PROCEDURE EMP_DATA_PROC(P_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
BEGIN
  FOR I IN (SELECT EMPLOYEE_ID,
                   FIRST_NAME,
                   LAST_NAME,
                   SALARY,
                   DEPARTMENT_ID
            FROM EMPLOYEES
            WHERE DEPARTMENT_ID = P_DEPARTMENT_ID
           )
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||I.EMPLOYEE_ID||' EMPLOYEE NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' -> SALARY -> '||I.SALARY||' DEPARTMENT ID -> '||I.DEPARTMENT_ID);
  END LOOP;
END;
/
SET SERVEROUTPUT ON;

EXEC EMP_DATA_PROC(30);
Object Privileges For Procedure In Oracle : Output

EMPLOYEE ID -> 208 EMPLOYEE NAME -> Jhon Smith -> SALARY -> 1000 DEPARTMENT ID -> 30
EMPLOYEE ID -> 114 EMPLOYEE NAME -> Den Raphaely -> SALARY -> 11000 DEPARTMENT ID -> 30
EMPLOYEE ID -> 115 EMPLOYEE NAME -> Alexander Khoo -> SALARY -> 3100 DEPARTMENT ID -> 30
EMPLOYEE ID -> 116 EMPLOYEE NAME -> Shelli Baida -> SALARY -> 2900 DEPARTMENT ID -> 30
EMPLOYEE ID -> 117 EMPLOYEE NAME -> Sigal Tobias -> SALARY -> 2800 DEPARTMENT ID -> 30
EMPLOYEE ID -> 118 EMPLOYEE NAME -> Guy Himuro -> SALARY -> 2600 DEPARTMENT ID -> 30
EMPLOYEE ID -> 119 EMPLOYEE NAME -> Karen Colmenares -> SALARY -> 2500 DEPARTMENT ID -> 30


PL/SQL procedure successfully completed.

Trying execute this procedure from SCOTT schema.

CONN SCOTT/TIGER@ORCL;

SET SERVEROUTPUT ON;

EXEC HR.EMP_DATA_PROC(30);
Error starting at line : 3 in command -
BEGIN HR.EMP_DATA_PROC(30); END;
Error report -
ORA-06550: line 1, column 10:
PLS-00904: insufficient privilege to access object HR.EMP_DATA_PROC
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Now granting EXECUTE on procedure EMP_DATA_PROC to SCOTT schema.

CONN HR/HR@ORCL;

GRANT EXECUTE ON EMP_DATA_PROC TO SCOTT;
CONN SCOTT/TIGER@ORCL;

SET SERVEROUTPUT ON;

EXEC HR.EMP_DATA_PROC(30);
EMPLOYEE ID -> 208 EMPLOYEE NAME -> Jhon Smith -> SALARY -> 1000 DEPARTMENT ID -> 30
EMPLOYEE ID -> 114 EMPLOYEE NAME -> Den Raphaely -> SALARY -> 11000 DEPARTMENT ID -> 30
EMPLOYEE ID -> 115 EMPLOYEE NAME -> Alexander Khoo -> SALARY -> 3100 DEPARTMENT ID -> 30
EMPLOYEE ID -> 116 EMPLOYEE NAME -> Shelli Baida -> SALARY -> 2900 DEPARTMENT ID -> 30
EMPLOYEE ID -> 117 EMPLOYEE NAME -> Sigal Tobias -> SALARY -> 2800 DEPARTMENT ID -> 30
EMPLOYEE ID -> 118 EMPLOYEE NAME -> Guy Himuro -> SALARY -> 2600 DEPARTMENT ID -> 30
EMPLOYEE ID -> 119 EMPLOYEE NAME -> Karen Colmenares -> SALARY -> 2500 DEPARTMENT ID -> 30


PL/SQL procedure successfully completed.

EXECUTE ON STANDALONE FUNCTION

Creating a standalone function on HR schema.

CONN HR/HR@ORCL;

CREATE OR REPLACE FUNCTION EMP_DATA_FUN(P_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE)
RETURN VARCHAR2
IS
  V_REMARKS VARCHAR2(4000);
BEGIN
  FOR I IN (SELECT EMPLOYEE_ID,
                   FIRST_NAME,
                   LAST_NAME,
                   SALARY,
                   DEPARTMENT_ID
            FROM EMPLOYEES
            WHERE EMPLOYEE_ID = P_EMPLOYEE_ID
           )
  LOOP
    V_REMARKS := 'EMPLOYEE ID -> '||I.EMPLOYEE_ID||' EMPLOYEE NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' -> SALARY -> '||I.SALARY||' DEPARTMENT ID -> '||I.DEPARTMENT_ID;
  END LOOP;
  
  RETURN V_REMARKS;
EXCEPTION
  WHEN OTHERS THEN
    V_REMARKS := NULL;
END;
/  
SELECT EMP_DATA_FUN(100) EMP_DATA FROM DUAL;
Object Privileges For Procedure In Oracle : Output
Object Privileges For Procedure In Oracle : Output

Trying execute this function from SCOTT schema.

CONN SCOTT/TIGER@ORCL;

SELECT HR.EMP_DATA_FUN(100) EMP_DATA FROM DUAL;
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges
Error at Line: 5 Column: 11

Now granting EXECUTE on function EMP_DATA_FUN to SCOTT schema.

CONN HR/HR@ORCL;

GRANT EXECUTE ON EMP_DATA_FUN TO SCOTT;
CONN SCOTT/TIGER@ORCL;

SELECT HR.EMP_DATA_FUN(100) EMP_DATA FROM DUAL;
Object Privileges For Procedure In Oracle : Output

EXECUTE ON PACKAGED PROCEDURE

Creating a package on HR schema.

CONN HR/HR@ORCL;

CREATE OR REPLACE PACKAGE EMP_PKG_GRANT
IS
  PROCEDURE EMP_DATA_PKG_PROC(P_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE);

END;
/
CONN HR/HR@ORCL;

CREATE OR REPLACE PACKAGE BODY EMP_PKG_GRANT
IS
  PROCEDURE EMP_DATA_PKG_PROC(P_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE)
  IS
  BEGIN
    FOR I IN (SELECT EMPLOYEE_ID,
                     FIRST_NAME,
                     LAST_NAME,
                     SALARY,
                     DEPARTMENT_ID
              FROM EMPLOYEES
              WHERE DEPARTMENT_ID = P_DEPARTMENT_ID
             )
    LOOP
      DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||I.EMPLOYEE_ID||' EMPLOYEE NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' -> SALARY -> '||I.SALARY||' DEPARTMENT ID -> '||I.DEPARTMENT_ID);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);     
  END;
END;
/
EXEC EMP_PKG_GRANT.EMP_DATA_PKG_PROC(30);
EMPLOYEE ID -> 208 EMPLOYEE NAME -> Jhon Smith -> SALARY -> 1000 DEPARTMENT ID -> 30
EMPLOYEE ID -> 114 EMPLOYEE NAME -> Den Raphaely -> SALARY -> 11000 DEPARTMENT ID -> 30
EMPLOYEE ID -> 115 EMPLOYEE NAME -> Alexander Khoo -> SALARY -> 3100 DEPARTMENT ID -> 30
EMPLOYEE ID -> 116 EMPLOYEE NAME -> Shelli Baida -> SALARY -> 2900 DEPARTMENT ID -> 30
EMPLOYEE ID -> 117 EMPLOYEE NAME -> Sigal Tobias -> SALARY -> 2800 DEPARTMENT ID -> 30
EMPLOYEE ID -> 118 EMPLOYEE NAME -> Guy Himuro -> SALARY -> 2600 DEPARTMENT ID -> 30
EMPLOYEE ID -> 119 EMPLOYEE NAME -> Karen Colmenares -> SALARY -> 2500 DEPARTMENT ID -> 30


PL/SQL procedure successfully completed.

Now try to execute the packaged procedure EMP_DATA_PKG_PROC from SCOTT schema.

CONN SCOTT/TIGER@ORCL;

EXEC HR.EMP_PKG_GRANT.EMP_DATA_PKG_PROC(30);
Error starting at line : 8 in command -
BEGIN HR.EMP_PKG_GRANT.EMP_DATA_PKG_PROC(30); END;
Error report -
ORA-06550: line 1, column 10:
PLS-00904: insufficient privilege to access object HR.EMP_PKG_GRANT
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Now granting EXECUTE on packaged procedure EMP_DATA_PKG_PROC to SCOTT schema.

CONN HR/HR@ORCL;

GRANT EXECUTE ON EMP_PKG_GRANT TO SCOTT;
CONN SCOTT/TIGER@ORCL;

EXEC HR.EMP_PKG_GRANT.EMP_DATA_PKG_PROC(30);
EMPLOYEE ID -> 208 EMPLOYEE NAME -> Jhon Smith -> SALARY -> 1000 DEPARTMENT ID -> 30
EMPLOYEE ID -> 114 EMPLOYEE NAME -> Den Raphaely -> SALARY -> 11000 DEPARTMENT ID -> 30
EMPLOYEE ID -> 115 EMPLOYEE NAME -> Alexander Khoo -> SALARY -> 3100 DEPARTMENT ID -> 30
EMPLOYEE ID -> 116 EMPLOYEE NAME -> Shelli Baida -> SALARY -> 2900 DEPARTMENT ID -> 30
EMPLOYEE ID -> 117 EMPLOYEE NAME -> Sigal Tobias -> SALARY -> 2800 DEPARTMENT ID -> 30
EMPLOYEE ID -> 118 EMPLOYEE NAME -> Guy Himuro -> SALARY -> 2600 DEPARTMENT ID -> 30
EMPLOYEE ID -> 119 EMPLOYEE NAME -> Karen Colmenares -> SALARY -> 2500 DEPARTMENT ID -> 30


PL/SQL procedure successfully completed.

EXECUTE ON PACKAGED FUNCTION

Adding a function in the same package EMP_PKG_GRANT.

CREATE OR REPLACE PACKAGE EMP_PKG_GRANT
IS
  PROCEDURE EMP_DATA_PKG_PROC(P_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE);
  --
  FUNCTION EMP_DATA_PKG_FUN(P_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY EMP_PKG_GRANT
IS
  PROCEDURE EMP_DATA_PKG_PROC(P_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE)
  IS
  BEGIN
    FOR I IN (SELECT EMPLOYEE_ID,
                     FIRST_NAME,
                     LAST_NAME,
                     SALARY,
                     DEPARTMENT_ID
              FROM EMPLOYEES
              WHERE DEPARTMENT_ID = P_DEPARTMENT_ID
             )
    LOOP
      DBMS_OUTPUT.PUT_LINE('EMPLOYEE ID -> '||I.EMPLOYEE_ID||' EMPLOYEE NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' -> SALARY -> '||I.SALARY||' DEPARTMENT ID -> '||I.DEPARTMENT_ID);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);     
  END;
  --
  FUNCTION EMP_DATA_PKG_FUN(P_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN VARCHAR2
  IS
    V_REMARKS VARCHAR2(4000);
  BEGIN
    FOR I IN (SELECT EMPLOYEE_ID,
                     FIRST_NAME,
                     LAST_NAME,
                     SALARY,
                     DEPARTMENT_ID
              FROM EMPLOYEES
              WHERE EMPLOYEE_ID = P_EMPLOYEE_ID
             )
    LOOP
      V_REMARKS := 'EMPLOYEE ID -> '||I.EMPLOYEE_ID||' EMPLOYEE NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' -> SALARY -> '||I.SALARY||' DEPARTMENT ID -> '||I.DEPARTMENT_ID;
    END LOOP;
    --
    RETURN V_REMARKS;
  EXCEPTION
    WHEN OTHERS THEN
      V_REMARKS := NULL;
  END;
END;
/
SELECT EMP_PKG_GRANT.EMP_DATA_PKG_FUN(100) EMP_DATA FROM DUAL;
Object Privileges For Procedure In Oracle : Output

Now try to execute the packaged function EMP_DATA_PKG_FUN from SCOTT schema.

CONN SCOTT/TIGER@ORCL;

SELECT HR.EMP_PKG_GRANT.EMP_DATA_PKG_FUN(100) EMP_DATA FROM DUAL;
Object Privileges For Procedure In Oracle : Output

Since we have already provided grant on entire package to schema SCOTT that's why we are able to access the packaged function EMP_DATA_PKG_FUN.

RELATED OTHER TOPICS:

Author

Leave a Comment

Your email address will not be published.