Grant is a command coming from the SQL grammar used to grant the rights to a table, to a user, or to a role. Admin rights are required to execute this command. This command commits an open transaction in this connection.
In this chapter, we will discuss the different scenarios of the Grant command.
Grant Right
Grant Right is a command to provide admin rights to a table, to a user, or to a role.
Syntax
Following is the generic syntax of the Grant command.
GRANT { SELECT | INSERT | UPDATE | DELETE | ALL } [,...] ON
{ { SCHEMA schemaName } | { tableName [,...] } }
TO { PUBLIC | userName | roleName }
Example
In this example, we will grant the test table as read-only using the following command.
GRANT SELECT ON TEST TO READONLY
The above command produces the following output.
Grant successfully
Grant Alter Any Schema
Grant Alter Any Schema is a command to grant schema altering rights to a respective user.
Syntax
Following is the generic syntax of the Grant Alter Any Schema command.
GRANT ALTER ANY SCHEMA TO userName
Example
In this example, we will grant altering privileges of a schema to a user named test_user. Make sure that test_user exists. Following is the query to grant altering privileges.
GRANT ALTER ANY SCHEMA TO test_user;
The above query produces the following output.
Granted successfully to test_user
