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 decision-making across organizational departments. data-driven 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 or (@Luca_DataTeam). You can also catch glimpses of my personal life on , Happy exploring!👋 LinkedIn X Instagram Also published . here

Too Long; Didn't Read

Developers: Build Less. Deliver More. [Learn how]	

Database Management: Creating and Granting User Access in Oracle

Too Long; Didn't Read

Luca Liu

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

Database Management: Creating and Granting User Access in Oracle

Too Long; Didn't Read

Luca Liu

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES