What is the SQL Grant statement, and how is it used to assign users privileges/permissions?
In SQL, the GRANT statement is used to give (or “grant”) one or more privileges/permissions to a particular database user. Here’s what the syntax for the GRANT statement looks like:
Syntax for the GRANT statement
GRANT privilege [, privilege ... ] /* can add more privileges here too */ [ON database object ] /*object can be tables, views, etc.. */ TO grantee [, grantee ...] /*user, PUBLIC, or role*/ [WITH GRANT OPTION | WITH ADMIN OPTION];
Let’s go over some things that you should know if you plan on using the GRANT statement.
SQL GRANT privileges
When granting privileges, the list of privileges can only be either all system privileges or all object privileges. In other words, you can not grant both system and object privileges in the same SQL GRANT statement – you will need to use two different SQL GRANT statements in order to do this. You can read about the differences between system and object privileges here: System versus object privileges.
SQL GRANT ON
The ON clause is only used to grant object privileges – not system privileges. This clause specifies which object privileges (as in which table privileges, view privileges, etc..) are being granted.
The SQL GRANTEE list
It should be clear from the syntax of the GRANT statement shown above that you can specify more than one database user or role that should receive the privilege(s) being granted.
SQL GRANT PUBLIC
In the majority of SQL implementations, you can actually grant privileges to “PUBLIC”. What does PUBLIC mean in the SQL GRANT statement? Well, the PUBLIC keyword is used to mean all users of a database – so all users would receive whatever privileges are being granted by that SQL statement. This is typically not good practice because of the obvious security risks, but may be OK depending on the situation.
The WITH ADMIN OPTION or WITH GRANT OPTION
The WITH ADMIN OPTION or WITH GRANT OPTION clauses lets the grantee (the person receiving the privileges) to further grant the privileges to others. So, these options essentially grant the grantee the ability to grant the same privileges to others (lovely sentence right?). Depending on which DBMS you are using, the exact syntax of these clauses will vary.
SQL GRANT Example
Here’s an example of what an actual SQL GRANT statement would look like:
GRANT SELECT ON SOME_TABLE TO SOME_USER;
In the example above, the GRANT statement is used to give the privilege of being able to select from SOME_TABLE to the SOME_USER user.