How to use GRANT statement In SQL
The GRANT statement is an adaptable tool that can be used to allocate privileges granting fine-grained control over a user’s access to various database resources. The syntax for granting DBA role with admin option is:
GRANT DBA TO username WITH ADMIN OPTION;
This does not only give “anya” the DBA role but also allows her to pass it on to other users.
GRANT DBA TO anya WITH ADMIN OPTION;
Output:
GRANT DBA TO anya WITH ADMIN OPTION
On top of that, specific system privileges like creating tables and sessions may be granted one by one:
GRANT CREATE TABLE, CREATE SESSION TO username;
In this way, a more differentiated assignment of privileges can be made depending on individual requirements.
GRANT CREATE TABLE, CREATE SESSION TO anya;
Output:
Output
Explanation: In the above query we grants the user “anya” the permissions to create tables and to establish a session in the database. This allows “anya” to create new tables and to connect to the database for executing queries and accessing data.
How to Create a User With Full Privileges in PL/SQL?
In database administration, the ability to manage user privileges is very important. As a database administrator, we may find ourselves in the position of creating users with full privileges and granting them the authority to manage and manipulate the database.
In this article, we will learn about CREATE USERS and give them GRANT or Privileges to access information using various methods and examples too.