Sayantan's Blog On Oracle

OBJECT PRIVILEGES FOR TABLE IN ORACLE

Privileges are basically rights to access any object or perform any operation on a table. Updating the data of a table is an example of object privilege.

Below are the object privileges available for Tables:

Privilege Name	Privilege Description
SELECT	Selecting data from a table
INSERT	Inserting new row into a table. This can be used against column as well.
UPDATE	Updating data of a table
DELETE	Deleting data from a table
ALTER	Change the current definition of a table
INDEX	Create index on a table
READ	Selecting data from a table
REFERENCE	This allows us to use the REFERENCE clause in CREATE TABLE or ALTER TABLE statement while creating FOREIGN KEY constraints.

GRANT and REVOKE Command

• Table owner can provide access to other users by using GRANT command.
• Similarly the same owner can roll back the access by using REVOKE command.

WITH GRANT OPTION Clause

When an user provide access on table to a second user then that user can not provide the same access to any third user. To enable this feature the owner of the table must use the WITH GRANT OPTION clause while providing access to second user. Then second user can provide access to third user.

Creating two new users.

CREATE USER SCOTT IDENTIFIED BY TIGER;

GRANT CONNECT TO SCOTT;

GRANT RESOURCE TO SCOTT;

CREATE USER SAN IDENTIFIED BY SAN;

GRANT CONNECT TO SAN;

GRANT RESOURCE TO SAN;

Creating a new table in HR schema.

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

SELECT * FROM EMPLOYEE_GRANT;

GRANTING SELECT

Tried to access the table EMPLOYEE_GRANT from SCOTT and SAN schema. From both the schema we will receive the below error.

CONN SCOTT/TIGER@ORCL;

SELECT * FROM HR.EMPLOYEE_GRANT;

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 1 Column: 18

Now granting the read only access to SCOTT and SAN schema.

CONN HR/HR@ORCL;

GRANT SELECT ON EMPLOYEE_GRANT TO SCOTT, SAN;

Now if we try to access the table EMPLOYEE_GRANT from SCOTT and SAN schema we will be able to do so.

CONN SCOTT/TIGER@ORCL;

SELECT * FROM HR.EMPLOYEE_GRANT;

GRANTING INSERT

Now if we try to insert a new record in to EMPLOYEE_GRANT table from SCOTT and SAN schema, then oracle will raise the below error.

CONN SCOTT/TIGER@ORCL;

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

Error starting at line : 3 in command -
Insert into HR.EMPLOYEE_GRANT (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,DEPARTMENT_ID) values (201,'Michael','Hartstein','MK_MAN',13000,20)
Error at Command Line : 3 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 we will grant INSERT on table EMPLOYEE_GRANT to SCOTT and SAN from HR schema.

CONN HR/HR@ORCL;

GRANT INSERT ON EMPLOYEE_GRANT TO SCOTT, SAN;

CONN SCOTT/TIGER@ORCL;

Insert into HR.EMPLOYEE_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.EMPLOYEE_GRANT;

GRANTING UPDATE

If we try to update any record of table EMPLOYEE_GRANT from SCOTT and SAN schema then oracle will raise error.

CONN SCOTT/TIGER@ORCL;

UPDATE HR.EMPLOYEE_GRANT
SET JOB_ID = 'MK_MAN'
WHERE EMPLOYEE_ID = 119;

Error starting at line : 5 in command -
UPDATE HR.EMPLOYEE_GRANT
SET JOB_ID = 'MK_MAN'
WHERE EMPLOYEE_ID = 119
Error at Command Line : 5 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 if we grant UPDATE on table EMPLOYEE_GRANT to SCOTT and SAN schema from HR schema then we will be able to do so.

CONN HR/HR@ORCL;

GRANT UPDATE ON EMPLOYEE_GRANT TO SCOTT, SAN;

CONN SCOTT/TIGER@ORCL;

UPDATE HR.EMPLOYEE_GRANT
SET JOB_ID = 'MK_MAN'
WHERE EMPLOYEE_ID = 119;

1 row updated.

SELECT * FROM HR.EMPLOYEE_GRANT;

GRANTING DELETE

If we try to delete any record of table EMPLOYEE_GRANT from SCOTT and SAN schema then oracle will raise error.

CONN SCOTT/TIGER@ORCL;

DELETE HR.EMPLOYEE_GRANT
WHERE EMPLOYEE_ID = 119;

Error starting at line : 10 in command -
DELETE HR.EMPLOYEE_GRANT
WHERE EMPLOYEE_ID = 119
Error at Command Line : 10 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 if we grant DELETE on table EMPLOYEE_GRANT to SCOTT and SAN schema from HR schema then we will be able to do so.

CONN HR/HR@ORCL;

GRANT DELETE ON EMPLOYEE_GRANT TO SCOTT, SAN;

CONN SCOTT/TIGER@ORCL;

DELETE HR.EMPLOYEE_GRANT
WHERE EMPLOYEE_ID = 119;

1 row deleted.

SELECT * FROM HR.EMPLOYEE_GRANT;

GRANTING ALTER

If we try to change the definition of table EMPLOYEE_GRANT from SCOTT and SAN schema then oracle will raise error.

CONN SCOTT/TIGER@ORCL;

ALTER TABLE HR.EMPLOYEE_GRANT ADD(MANAGER_ID NUMBER(6));

Error starting at line : 14 in command -
ALTER TABLE HR.EMPLOYEE_GRANT ADD(MANAGER_ID NUMBER(6))
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

Now if we grant ALTER on table EMPLOYEE_GRANT to SCOTT and SAN schema from HR schema then we will be able to do so.

CONN HR/HR@ORCL;

GRANT ALTER ON EMPLOYEE_GRANT TO SCOTT, SAN;

CONN SCOTT/TIGER@ORCL;

ALTER TABLE HR.EMPLOYEE_GRANT ADD(MANAGER_ID NUMBER(6));

SELECT * FROM HR.EMPLOYEE_GRANT;

GRANTING INDEX

If we try to create any index on table EMPLOYEE_GRANT from SCOTT and SAN schema then oracle will raise error.

CONN SCOTT/TIGER@ORCL;

CREATE INDEX HR.EMPLOYEE_GRANT_INDX ON HR.EMPLOYEE_GRANT(EMPLOYEE_ID);

Error starting at line : 16 in command -
CREATE INDEX HR.EMPLOYEE_GRANT_INDX ON HR.EMPLOYEE_GRANT(EMPLOYEE_ID)
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

Now if we grant INDEX on table EMPLOYEE_GRANT to SCOTT and SAN schema from HR schema then we will be able to do so.

CONN HR/HR@ORCL;

GRANT INDEX ON EMPLOYEE_GRANT TO SCOTT, SAN;

CONN SCOTT/TIGER@ORCL;

CREATE INDEX EMPLOYEE_GRANT_INDX ON HR.EMPLOYEE_GRANT(EMPLOYEE_ID);

Error starting at line : 16 in command -
CREATE INDEX EMPLOYEE_GRANT_INDX ON HR.EMPLOYEE_GRANT(EMPLOYEE_ID)
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

Now we can see that although we have provided grant to create index on EMPLOYEE_GRANT table to SCOTT and SAN schema, still oracle is raising the error.

This is because we haven't provided the grant to create any index to schema SCOTT and SAN.

--Connect with SYS user--
GRANT CREATE ANY INDEX TO SCOTT, SAN;

CONN SCOTT/TIGER@ORCL;

CREATE INDEX HR.EMPLOYEE_GRANT_INDX ON HR.EMPLOYEE_GRANT(EMPLOYEE_ID);

Index HR.EMPLOYEE_GRANT_INDX created.

Checking the data dictionary.

CONN HR/HR@ORCL;

SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE 
FROM USER_INDEXES WHERE TABLE_NAME = 'EMPLOYEE_GRANT';

GRANTING READ

READ is similar to SELECT. But in SELECT we can lock the specific rows of the table or the entire table. No such locking option is available for READ. So from security stand point READ is more secure.

RELATED OTHER TOPICS:

• UNION Operators In Oracle
• INTERSECT Operator In Oracle
• INDEXES in Oracle
• SYNONYMS in Oracle