Grant or Revoke the Permission from Oracle User
An object-level privilege is a permission granted to an Oracle database user account or role to perform some action on a database object.
These object privileges include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX on tables and views and EXECUTE on procedures, functions, and packages.
Privileges at object Level
—————————
Privilege : Description
SELECT : Permission to perform SELECT operation on the table.
INSERT : Permission to perform INSERT operation on the table.
UPDATE : Permission to perform UPDATE operation on the table.
DELETE : Permission to perform DELETE operation on the table.
REFERENCES : Permission to create a reference constraint on the table.
ALTER : Permission to perform ALTER TABLE statements on the table.
INDEX : Permission to create an index on the table.
ALL : All Permission defined above grant on the table.
Grant the SYTEM Permission to USER
GRANT ALTER TABLESPACE, DROP TABLESPACE TO USER1;
GRANT CREATE SESSION TO USER2 WITH ADMIN OPTION;
Grant permission at object level
Grant create table to USER1;
GRANT SELECT, ALTER ON USER1.orders TO PUBLIC;
GRANT SELECT, DELETE ON USER1.order_details TO USER2;
GRANT ALL on USER1.Order_Details to USER2;
Note: Grant all command give all the following permission:
Grant all to scott.emp to HR;
Output:
ALTER EMP SCOTT
DEBUG EMP SCOTT
DELETE EMP SCOTT
FLASHBACK EMP SCOTT
INDEX EMP SCOTT
INSERT EMP SCOTT
ON COMMIT REFRESH EMP SCOTT
QUERY REWRITE EMP SCOTT
REFERENCES EMP SCOTT
SELECT EMP SCOTT
UPDATE EMP SCOTT
Revoke permission at object level
REVOKE SELECT ON dbock.orders FROM USER1;
REVOKE ALL on USER1.Order_Details FROM USER2;
Important view to check object level privileges
--Check role or permission on object
USER_TAB_PRIVS
ALL_TAB_PRIVS
ROLE_TAB_PRIVS
DBA_TAB_PRIVS
-- You can check roles ( Role is basically group of permissions)
USER_ROLE_PRIVS
ALL_ROLE_PRIVS
ROLE_ROLE_PRIVS
DBA_ROLE_PRIVS
