SQL GRANT

SQL GRANT statement is used to grant security privileges on database objects to specific users or roles. Normally, the GRANT (SQL) statement is used by the owner of a table or view to give other users access to the data. SQL GRANT statement includes a specific list of the privileges to be granted, the name of the table or other object to which the privileges apply (an object type is required for all objects except tables and views), and the user-id or role to which the privileges are granted. User accounts must exist before privileges can be granted to them.

Following are most common privileges granted on an object:

Privileges on database:

• CONNECT allows user to connect to database. All users must be granted CONNECT security privilege before they can be granted any other security privileges
• RESOURCE security privilege allows a user to create, alter, and drop- tables, domains, and indexes.
• DBA privilege has the same capabilities as the RESOURCE privilege, but may also create tablespaces and files. Users with the DBA privilege can also grant or revoke object privileges for schema objects owned by other users, except system schema objects.

Example 1:

The following SQL grants the CONNECT privilege to a user named Bryan with the password welcome1 and a user named Jenny with the password user123.

GRANT CONNECT TO bryan welcome1, jenny user123;

Example 2:

The following SQL grants the RESOURCE privilege to users Bryan and Jenny.

GRANT RESOURCE TO bryan, jenny;

Example 3:

The following SQL grants the DBA privilege to users Bryan and Jenny.

GRANT DBA TO bryan, jenny

Privileges on objects:

• SELECT privilege is used to select data in a database object, applies to the entire object, and cannot be granted to specific columns.
• INSERT privilege is used to insert new data into a database object. The privilege can also be restricted to specific columns.
• DELETE privilege is used to delete data from a database object, applies to the entire object and cannot be granted on specific columns.
• UPDATE privilege is used to update data in a database object. The privilege can also be restricted to specific columns.
• INDEX privilege is used to create an index on a database object, applies to the entire object, and cannot be granted on specific columns.
• ALTER privilege is used to alter the schema of a database object, applies to the entire object and cannot be granted on specific columns.
• REFERENCE privilege is used to create referential constraints, such as foreign keys, on a database object. The privilege can also be restricted to specific columns.
• EXECUTE privileges control which executable database objects a user can use.

The keywords ALL and ALL PRIVILEGES can also be used to grant all privileges on an object.

Example 4:

The following SQL grants SELECT, INSERT, and UPDATE object privileges on the
Employee table to the user named Vivian.

GRANT SELECT, INSERT, UPDATE ON employee TO Vivian;

Example 5:

The following grants all privileges on the Employee table to the user named Sue.

GRANT ALL PRIVILEGES ON employee TO sue;