| Lesson 3 | GRANT Command |
| Objective | Grant other Users Access to your Tables |
Oracle GRANT Command
When you create a table or view, you are automatically given access to the data contained within the object.
If you want to grant access privileges to another user, you have to explicitly give them access to the objects through the GRANT command.
GRANT Syntax
The syntax used to grant privileges for a table or view is shown using the syntax below.
Granting privileges in Oracle
GRANT privilege/ALL ON object TO user/PUBLIC
- Privilege is the name of a specific privilege. The ALL keyword means that all privileges will be granted on the target object to the specified user
- The name of the table or view to which the privileges apply.
- User is the name of an existing Oracle user. The keyword PUBLIC refers to a special user group that applies the privilege to all users of the database.
GRANT Command
GRANT privilege/ALL ON object
TO user/PUBLIC
GRANT: A required keyword.privilege/ALL: privilege is the name of a specific privilege.
The ALL keyword means that all privileges will be granted on the target object to the specified user.ON: A required keyword.object: The name of the table or view to which the privileges apply.TO: A required keyword.user/PUBLIC: user is the name of an existing Oracle user.
The keyword PUBLIC refers to a special user group that applies the privilege to all users of the database.
SYS
When you create an Oracle Database, the user SYS is automatically created and granted the DBA role.All of the base tables and views for the database data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.) Ensure that most database users are never able to connect to Oracle Database using the SYS account.
SYSTEM
When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.
The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to non-administrative users.
You may notice that you can grant privileges to something called PUBLIC.
PUBLIC is a special keyword that is explained at the following link.
PUBLIC privileges
Your Oracle database comes with a special user group called PUBLIC already defined.
The PUBLIC user group includes all users for the database.
Any privilege that you grant to PUBLIC will be automatically granted to every database user, so you should be very careful when assigning privileges to this user group.
Format for the grant Command
Here is the general format for the grant command for system privileges
grant {system privilege | role | all [privileges] }
[, {system privilege | role | all [privileges] } ...]
to {user | role} [, {user | role}...]
[identified by password ]
[with admin option]
You can grant any system privilege or role to another user, to another role, or to public. The with admin option clause permits the grantee to bestow the privilege or role on other users or roles. The all clause grants the user or role all privileges except the SELECT ANY DICTIONARY system privilege. The grantor can revoke a role from a user as well.
Revoking Privileges
Privileges granted can be taken away.
The revoke command is similar to the grant command:
revoke {system privilege | role | all [privileges] }
[, {system privilege | role | all [privileges] }. . .]
from {user | role} [, {user | role}]. . .
DBA Role
An individual with the DBA Role can revoke CONNECT, RESOURCE, DBA, or any other privilege or role from anyone, including another DBA.
This, of course, is dangerous, and is why DBA privileges should be given neither lightly nor to more than a tiny minority who really need them.
Note: Revoking everything from a given user does not eliminate that user from Oracle, nor does it destroy any tables that user had created; it simply prohibits the access of the user to them. Other users with access to the tables will still have exactly the same access they have always had.
To grant INSERT or UPDATE privileges to a specific column in a table, you use the same syntax as described in the MouseOver, except that you insert the name of the column between the list of privileges and the keyword ON.
Examples
If you wanted to grant all privileges to a user called "ADMIN" for the table COIN,
you would use the following syntax:
GRANT ALL ON COIN
TO ADMIN;
If you wanted to grant
INSERT privileges for the
WINNING_BID column in the
LOT table to the user called "
AUCTIONEER", you would use the following syntax:
GRANT INSERT winning_bid ON LOT TO AUCTIONEER;
The next lesson exploresusing Security Manager to assign object privileges.
grant Command - Exercise