Grant or Revoke the Permission from Oracle User

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.