Database Management: Creating and Granting User Access in Oracle

Introduction

Proficiency in database management is an essential skill for data analysts. Oftentimes, colleagues from different departments need quick access to specific datasets, making the traditional method of exporting Excel files from databases inefficient and time-consuming. A more effective approach involves creating new user accounts in the database and granting them appropriate viewing rights. This allows colleagues to seamlessly connect to the database using tools like Power BI or Power Query, enabling them to access necessary data tables promptly and efficiently. This streamlined process fosters collaboration and supports data-driven decision-making across organizational departments.

Creating a new user

To create a new user in the Oracle database, you can execute the following SQL command:

CREATE USER 'USERNAME' IDENTIFIED BY 'PASSWORD';

For example:

CREATE USER TEST_USER IDENTIFIED BY TEST123;

Granting the Connect Role

After creating a new user, attempting to connect may result in an error message:

[72000][1045] ORA-01045: user TEST_USER lacks CREATE SESSION privilege; logon denied

To enable the user to connect to the database, you must grant the CONNECT role:

GRANT CONNECT TO TEST_USER;

Merely connecting is not enough - make sure to grant the RESOURCE privilege to allow access to the tables.

GRANT CONNECT, RESOURCE TO TEST_USER;

Table privilege

To grant specific privileges on tables to a user, you can utilize the GRANT command on specific table TO user. For example:

GRANT SELECT ON schema.table_name TO TEST_USER;

If you wish to provide the user with options beyond selection, such as insertion, updating, and deletion, you may proceed with the execution of the following command.

GRANT SELECT, INSERT, UPDATE, DELETE ON schema.table_name TO TEST_USER;

Delete a user

To remove a user from the database, you can execute the following SQL command:

DROP USER TEST_USER;

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn or X(@Luca_DataTeam). You can also catch glimpses of my personal life on Instagram, Happy exploring!👋

Also published here.