Sayantan's Blog On Oracle

OBJECT PRIVILEGES FOR VIEWS IN ORACLE

Privileges are basically rights to access any object or perform any operation on a view.

To create a view on a schema the user must have CREATE VIEW system privilege. To create a view on another schema the user needs to CREATE ANY VIEW system privilege

Below are the object privileges available for Views:

Privilege Name	Privilege Description
SELECT	Selecting data from a view
INSERT	Inserting new row into the base table behind the view
UPDATE	Updating data of the base table behind the view
DELETE	Deleting data from the base table behind the view

Creating two new users.

CREATE USER SCOTT IDENTIFIED BY TIGER;

GRANT CONNECT TO SCOTT;

GRANT RESOURCE TO SCOTT;

GRANT CREATE ANY VIEW TO SCOTT;

CREATE USER SAN IDENTIFIED BY SAN;

GRANT CONNECT TO SAN;

GRANT RESOURCE TO SAN;

GRANT CREATE ANY VIEW TO SAN;

Creating a new view in HR schema.

CONN HR/HR@ORCL;

CREATE OR REPLACE VIEW EMP_DEPT_VIEW
AS
SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME,
       B.LOCATION_ID
FROM EMPLOYEES A,
     DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND A.DEPARTMENT_ID = 30;

SELECT * FROM EMP_DEPT_VIEW;

Now if we try to access the view from schema SCOTT or SAN then oracle will raise error:

CONN SCOTT/TIGER@ORCL;

SELECT * FROM HR.EMP_DEPT_VIEW;

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: 1 Column: 18

GRANTING SELECT

Giving access on view EMP_DEPT_VIEW to SCOTT and SAN schema.

CONN HR/HR@ORCL;

GRANT SELECT ON EMP_DEPT_VIEW TO SCOTT, SAN;

CONN SCOTT/TIGER@ORCL;

SELECT * FROM HR.EMP_DEPT_VIEW;

GRANTING INSERT

Creating a new view EMP_VIEW_GRANT from HR schema.

CONN HR/HR@ORCL;

CREATE TABLE EMP_TAB_GRANT
AS
SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID = 30;

CREATE OR REPLACE VIEW EMP_VIEW_GRANT
AS
SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID
FROM EMP_TAB_GRANT A;

SELECT * FROM EMP_VIEW_GRANT;

If we try to insert data through the view EMP_VIEW_GRANT from SCOTT schema oracle will raise error as there is no access provided.

CONN SCOTT/TIGER@ORCL;

Insert into HR.EMP_VIEW_GRANT (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,DEPARTMENT_ID) values (201,'Michael','Hartstein','MK_MAN',13000,20);

Error starting at line : 4 in command -
Insert into HR.EMP_VIEW_GRANT (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,DEPARTMENT_ID) values (201,'Michael','Hartstein','MK_MAN',13000,20)
Error at Command Line : 4 Column : 16
Error report -
SQL Error: 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

Now providing SELECT, INSERT access to user SCOTT and SAN.

CONN HR/HR@ORCL;

GRANT SELECT ON EMP_VIEW_GRANT TO SCOTT, SAN;

GRANT INSERT ON EMP_VIEW_GRANT TO SCOTT, SAN;

Now trying to insert a new record from SCOTT schema through the view EMP_VIEW_GRANT.

CONN SCOTT/TIGER@ORCL;

Insert into HR.EMP_VIEW_GRANT (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,DEPARTMENT_ID) values (201,'Michael','Hartstein','MK_MAN',13000,20);

1 row inserted.

SELECT * FROM HR.EMP_VIEW_GRANT;

GRANTING UPDATE

We have created a new view EMP_VIEW_GRANT and provided the SELECT and INSERT access to user SCOTT and SAN. If we try to update the data of EMP_VIEW_GRANT from user SCOTT and SAN then oracle will raise error.

CONN SCOTT/TIGER@ORCL;

UPDATE HR.EMP_VIEW_GRANT
SET JOB_ID = 'PU_MAN'
WHERE EMPLOYEE_ID = 201;

Error starting at line : 6 in command -
UPDATE HR.EMP_VIEW_GRANT
SET JOB_ID = 'PU_MAN'
WHERE EMPLOYEE_ID = 201
Error at Command Line : 6 Column : 11
Error report -
SQL Error: 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

Now providing the UPDATE access to user SCOTT and SAN.

CONN HR/HR@ORCL;

GRANT UPDATE ON EMP_VIEW_GRANT TO SCOTT, SAN;

CONN SCOTT/TIGER@ORCL;

UPDATE HR.EMP_VIEW_GRANT
SET JOB_ID = 'PU_MAN'
WHERE EMPLOYEE_ID = 201;

1 row updated.

GRANTING DELETE

Trying to delete a record from view EMP_VIEW_GRANT through user SCOTT.

CONN SCOTT/TIGER@ORCL;

DELETE HR.EMP_VIEW_GRANT
WHERE EMPLOYEE_ID = 201;

Error starting at line : 6 in command -
DELETE HR.EMP_VIEW_GRANT
WHERE EMPLOYEE_ID = 201
Error at Command Line : 6 Column : 11
Error report -
SQL Error: 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

Now providing DELETE access to user SCOTT and SAN.

CONN HR/HR@ORCL;

GRANT DELETE ON EMP_VIEW_GRANT TO SCOTT, SAN;

CONN SCOTT/TIGER@ORCL;

DELETE HR.EMP_VIEW_GRANT
WHERE EMPLOYEE_ID = 201;

1 row deleted.

SELECT * FROM HR.EMP_VIEW_GRANT;

WITH GRANT OPTION

If we create a view and provide access to a second user, then the second user will be able to grant access to a third user if the owner of the view uses the WITH GRANT OPTION clause while providing grant to second user. Otherwise the third user will not be able to access the view.

CONN HR/HR@ORCL;

CREATE OR REPLACE VIEW EMP_VIEW_GRANT_20
AS
SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID = 20;

SELECT * FROM EMP_VIEW_GRANT_20;

GRANT SELECT ON EMP_VIEW_GRANT_20 TO SCOTT;

CONN SCOTT/TIGER@ORCL;

SELECT * FROM HR.EMP_VIEW_GRANT_20;

Now if we try to grant the SELECT access on view HR.EMP_VIEW_GRANT_20 to user SAN from SCOTT user then oracle will raise error. The reason is that the user HR has not provided the SELECT privilege to SCOTT user by using WITH GRANT OPTION clause.

GRANT SELECT ON HR.EMP_VIEW_GRANT_20 TO SAN;

Error starting at line : 3 in command -
GRANT SELECT ON HR.EMP_VIEW_GRANT_20 TO SAN
Error report -
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

CONN HR/HR@ORCL;

GRANT SELECT ON EMP_VIEW_GRANT_20 TO SCOTT WITH GRANT OPTION;

CONN SCOTT/TIGER@ORCL;

GRANT SELECT ON HR.EMP_VIEW_GRANT_20 TO SAN;

Grant succeeded.

CONN SAN/SAN@ORCL;

SELECT * FROM HR.EMP_VIEW_GRANT_20;

RELATED OTHER TOPICS:

• Object Privileges For Table In Oracle
• UNION Operators In Oracle
• INTERSECT Operator In Oracle
• INDEXES in Oracle
• SYNONYMS in Oracle