GRANT Statement

GRANT is the single most important statement when it comes to security in SQL. The GRANT statement controls all access to the SQL database. The statement can give users permission to adjust the database structure through DDL (Data Definition Language) and DML (Data Manipulation Language).

The syntax for GRANT is:

GRANT privilege
ON object_name
TO user or ROLE or PUBLIC

This is actually a fairly straightforward statement. It GRANTs a privelege (the ability to do something - SELECT, UPDATE, ALL, etcetera). It specifies what objects can be operated upon (database, table, column, constraint, etcetera). Finally it states who gets this privilege (a user, a role, or even the general public).

The most unfamiliar idea in this statement may be the ROLE. It is not available in all applications of SQL (not mySQL 5.1). A ROLE is an entity that has been granted a group of priveleges to which a user may be assigned. This means that the permissions of each user will not have to be stored. To CREATE a ROLE:

CREATE ROLE role

An example of GRANT:

GRANT SELECT ON fict_chars TO user1

< DCL Data Control Language | REVOKE Statement DCL >


Send notes in disappearing ink!

Interesting Pages