How is the grant command used in Oracle?

1 year ago
Sophia Anderson
1 minute

In Oracle database, the GRANT command is used to give specific permissions and privileges to users or roles. The basic syntax is as follows:

GRANT privilege [, privilege ...]
    ON [object]
    TO {user | role | PUBLIC}
    [WITH GRANT OPTION];
  1. Privilege: Refers to the specific permission that can be granted, which can include system permissions, roles, or object permissions.
  2. Object: specifies the object for which permissions are being granted, such as a table, view, or stored procedure in the database.
  3. user: Specify the user you want to grant permission to.
  4. role: specify the role to grant permissions.
  5. PUBLIC: Granting permission to all users.
  6. WITH GRANT OPTION allows the user who has been granted permission to pass on that permission to other users or roles.

For example, the following command grants the user “test_user” SELECT and INSERT permissions on the table “employees”:

GRANT SELECT, INSERT ON employees TO test_user;

This command grants the role “admin_role” full permissions to the table “employees” and allows the role to delegate these permissions to other users.

GRANT ALL ON employees TO admin_role WITH GRANT OPTION;

Note: The GRANT command must be executed by a user with the appropriate permissions.

bannerAds