How to Create and Manage Oracle Database Users and Privileges?

Oracle Database is considered the most popular and widely-used RDBMS to manage huge volumes of data effectively. However, the data can be crucial and sensitive even. To protect the data, Oracle offers features for access control which allow the admin to create different users to access the database and assign them roles and privileges. Privileges are the operations they are allowed to perform, such as “VIEW TABLE” and “DELETE TABLE”.

This blog will cover the enlisted topics to create and manage Oracle Database users and privileges:

• Prerequisite: Login to Oracle Database as “sysdba”
• Create Oracle Database User
• GRANT Privileges to the Oracle Database User
• Manage Oracle Database User and Privileges

Prerequisite: Login to Oracle Database as “sysdba”

To begin with the blog makes sure that the user logs in to the Oracle database as a system database administrator. To do so open the command prompt and execute the command below:

In the above command, the database username is “root”. Replace it with your database username.

Output

The output depicted a success message after connecting to the Oracle database.

How to Create an Oracle Database User?

To access a database securely, it is a good approach to create different users with roles and privileges assigned to them to ensure data access. Let us create a user with the username “c##database_user” by executing the provided command:

Here in the above command, the new user creates with a username “c##database_user” and password “Hello1234”.

Output

The output returns a success message “User created” after the creation of the user.

Test User Login
After the creation of the user, it is time to connect to the database using the user credentials. For that open SQL Developer and type the name for the database connection and your newly created user username and password. Click on the “Test” button to test the connection:

The failure message prompts due to lack of privileges. It means a new user does not have any privileges so assign privileges for performing any action using this user.

How to GRANT Privileges to the Oracle Database User?

The “GRANT” statement in Oracle aids in assigning privileges to any user. There are privileges for performing every task, such as “DELETE”, “CREATE VIEW”, “CREATE TABLE” and “MANAGE TABLESPACE”. To read more about privileges visit the Oracle Official Documentation. However, we faced an error message due to the lack of “CREATE SESSION” privileges. To resolve it, grant this privilege to the user by executing the command given below:

The above command will grant the “CREATE SESSION” privilege to the user “c##database_user”.

Output

The output displayed a success message after granting the “CREATE SESSION” privilege to the user.

Test by User Login
Let us test whether the privilege is granted successfully or not by creating an Oracle database connection in the SQL Developer:

The output displayed the connection status as “Success”.

After connecting to the Oracle database. Let us try to create a new table to see if this user can do any routine tasks of database management or not. For instance, create a “Bakery” table in the database by executing the command below:

The above command is supposed to create a table with three columns. Each column’s name, data type, and constraint are defined in the command.

Output

The error is prompted due to “insufficient privileges”.

To resolve the above error, grant the privilege of “CREATE TABLE” to the user or grant all privileges to the user. For that run, the command given below:

The above command grants all privileges to the user “c##database_user”.

Note: Granting all privileges can be harmful if the user should not have maximum powers to manipulate the database.

Output

The output returned the message “Grant succeeded” after granting privileges to the user.

Again, run the “CREATE TABLE” command to verify whether the privileges are granted or not:

The above command creates a new table named “Bakery” in the database.

Output

The output displayed a message “Table BAKERY created”.

How to Manage Oracle Database User and Privileges?

Once the user is created and granted necessary privileges the Oracle database. It allows the system database administrator to manage the user and its privileges, such as altering, viewing, and deleting a user and its privileges.

Let us see the practical implementation of some important management tasks for the user and privileges:

View Granted Privileges to the Oracle Database User
The user can see all the privileges granted by running the command given below:

The command fetches and returns the names of granted privileges to the user.

Output

The output displayed on the table contains all necessary information about granted privileges.

Modify the Privileges for the Oracle Database User
To modify/add the privileges for the Oracle, execute the command containing the username and new privilege name:

The above command grants the privilege “CREATE ANY RULE” to the user.

Output

In the above output, a success message “Grant succeeded” is returned.

Delete Oracle Database User
To delete an Oracle database user can utilize the “DROP USER” statement. Here, deletes the user “c##database_user”:

This command deletes the user from the database and the “CASCADE” ensures that it also deletes all the related privileges and roles.

Output

The output returned the message “User dropped” after deleting a user successfully.

Conclusion

To ensure access control and data security, it is essential to create users and assign them privileges. Login as “sysdba” in the database and use the syntax “CREATE USER <username> IDENTIFIED BY <password>;” to create a new user. After that, assign the necessary privileges to a user using the syntax “GRANT <privilege_name> TO <username>;”. Users can also alter privileges and view them. Moreover, a user can be dropped using the syntax “DROP USER <username> CASCADE;”.