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.

How to Create a User with Full Privileges?

As a database administrator, one of our key responsibilities is to create users with full privileges often referred to as superusers or administrators. Below are three methods that can help us to CREATE USERS and give FULL Privileges to access information in PL/SQL are as follows:

• Using the CREATE USER Statement
  
• Using the GRANT Statement
  
• Using the DBMS_SQL Package

1. Using CREATE USER Statement

The CREATE USER statement is a fundamental command for database administrators allowing them to create new users and grant them specific privileges within the database. It requires specifying the username and password for the new user.

Syntax for Create a New User:

CREATE USER username IDENTIFIED BY password;

Here we can substitute “username” for the desired name, whereas “password” stands for a secure access key.

Example: Let’s say we wanted to create a user called "Anya" with the password "pass123."

CREATE USER anya IDENTIFIED BY pass123;

Output:

Explanation: Our User has been created.

Let's Granting Privileges to the Anya

In PL/SQL, GRANT statement plays an important role in giving away administrative or DBA rights. Depending upon specific needs, GRANT statement is used for different allocation of privileges. This command grants all database administration rights across entire databases upon this specific user by dint of his DBA role.

Syntax for Grant Privileges:

GRANT DBA TO username;

Example: For instance, anya receives full database administration privileges through the DBA role.

GRANT DBA TO anya;

Output:

Explanation: As we can see in the above image that privileges grant to the Anya. See in the below image to get visual representation of grant privileges Anya

2. Using GRANT statement

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:

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:

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.

3. Using the DBMS_SQL Package

For a more programmatic approach to user privilege management in PL/SQL, the DBMS_SQL Package is used. This package allows for creating and running SQL statements within PL/SQL that are dynamic.

DECLARE
   sql_cursor INTEGER;
   sql_stmt VARCHAR2(100);
BEGIN
   sql_stmt := 'GRANT DBA TO anya';
   sql_cursor := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(sql_cursor, sql_stmt, DBMS_SQL.NATIVE);
   DBMS_SQL.CLOSE_CURSOR(sql_cursor);
END;

Output:

Explanation: In this dynamic example, the GRANT command assigns the DBA role to user “Anya”. The statement is parsed using the DBMS_SQL.PARSE procedure and then closed cursor.

Conclusion

Overall, themanaging user privileges in database administration is important. In this article we have explored three methods in PL/SQL for creating users with full privileges. The CREATE USER statement allows for user creation, while the GRANT statement offers to get control over privileges. Additionally, the DBMS_SQL package provides a programmatic approach for dynamic SQL execution. Mastering these methods is essential for ensuring users have the necessary access to manage and manipulate the database effectively.