Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article I will be explaining how we can Authorize the User with ‘WITH GRANT OPTION’.
When Grant is given with Grant Option, the person to whom Grant is given can also authorize the objects they want.
If the user who gave the authorization with the Grant Option gets the authorization, the authorization given by the user to the other user is also taken.
For example:
Let’s create users named SYS and AHMET, EMRAH and ONUR and let AHMET authorize to create tables under his own schema.
1 2 3 4 5 6 7 | CREATE USER AHMET IDENTIFIED BY "Passw0rd1" account unlock; GRANT CREATE SESSION TO AHMET; GRANT RESOURCE TO AHMET; CREATE USER EMRAH IDENTIFIED BY "Passw0rd1" account unlock; GRANT CREATE SESSION TO EMRAH; CREATE USER ONUR IDENTIFIED BY "Passw0rd1" account unlock; GRANT CREATE SESSION TO ONUR; |
Let the AHMET user create a table under his own schema and grant EMRAH the right to select the table he created with ‘WITH GRANT OPTION’.
1 2 3 4 5 6 7 | CREATE TABLE AHMET_TEST ( ID NUMBER (2), DESCRIPT VARCHAR2 (10) ); GRANT SELECT ON AHMET_TEST TO EMRAH WITH GRANT OPTION; |
Let the EMRAH user also authorize the ONUR user to select this table.
1 | GRANT SELECT ON AHMET.AHMET_TEST TO ONUR; |
The ONUR user queries the table and sees that the data has arrived.
Let AHMET user get all permissions related to the table created from EMRAH user.
1 | REVOKE ALL ON AHMET_TEST FROM EMRAH; |
EMRAH and ONUR users get the following error when they try to query the AHMET_TEST table.
