An object privilege allows a user to perform certain actions on database objects. For example, a user can execute DML statements on tables.
|Object Privilege||Allows a User to...|
|SELECT||Perform a select.|
|INSERT||Perform an insert.|
|UPDATE||Perform an update.|
|DELETE||Perform a delete.|
|EXECUTE||Execute a stored procedure.|
Granting Object Privileges to a User
GRANT command grants an object privilege to a user.
GRANT SELECT, INSERT, UPDATE ON store.products TO tom; GRANT SELECT ON store.employees TO tom;
The next example grants the
UPDATE privilege on the last_name and sal columns to tom:
GRANT UPDATE (last_name, sal) ON store.employees TO tom;
The following example uses the
GRANT option to enable a user to grant a privilege to another user.
GRANT SELECT ON store.employee TO tom WITH GRANT OPTION;
GRANT OPTION grants an object privilege to another user,
ADMIN OPTION grants a system privilege to another user.
SELECT ON store.employee privilege can then be granted to another user by tom.
The following example connects as tom and grants this privilege to another user:
CONNECT tom/password GRANT SELECT ON store.employee TO anotherUser;