Introduction
Hi There ! In this write up, we will discuss about how to create & assign users to the oracle DB
As always, begin by connecting to your server where Oracle is hosted, then connect to Oracle itself as the SYSTEM account.
The SYSTEM account is one of a handful of predefined administrative accounts generated automatically when Oracle is installed. SYSTEM is capable of most administrative tasks, but the task we’re particularly interested in is account management.
Run the following query to connect to the SYSTEM & verify
connect system/manager;
show user;
Also verify that no users are exists in the dual table
select user from dual;
Let’s create a user with the name abc & password abc123
create user abc identified by abc123;With our new abc account created, we can now begin adding privileges to the account using the GRANT statement. GRANT is a very powerful statement with many possible options, but the core functionality is to manage the privileges of both users and roles throughout the database.
Run the following query
grant connect,resource to abc;
In some cases to create a more powerful user, you may also consider adding the RESOURCE role (allowing the user to create named types for custom schemas) or even the DBA role, which allows the user to not only create custom named types but alter and destroy them as well.
Now, let’s connect using our newly created user abc, as we have given the connect & resource GRANT
We have successfully connected.
Verify by showing the user
show user;
User is abc, so we have successfully grated the access.
Now, let’s try something different, let’s try to add another user using the abc account.
What do you think ? it will work ? let’ see
Run the following command
create user xyz identified by xyz123;
Oops ! We have stopped with an error saying that “insufficient privileges”
As we just gave the connect & resource privilege, we could not add another account.
Next we want to ensure the user has privileges to actually connect to the database and create a session using GRANT CREATE SESSION. We’ll also combine that with all privileges using GRANT ANY PRIVILEGE.
Let’s fix this. Get out & re connect using the system account again
Run the following command
connect system/manager
grant all privileges to abc;
Now, we have given all the privileges to the abc user. So , now it should have able to add another user
Let’s see if it works out !
Connect with the abc user again
connect abc/abc123;
Let’s verify again about the user connection & execute the useradd command to show if we can add that xyz user.
show user;
create user xyz identified by xyz123;
Bingo ! Looks like we have added successfully !
That’s all for today folks !
That’s all for today’s write up. If you want to know DB from beginning, you can head back to my previous tutorial about the oracle db installation here https://unixcop.com/how-to-install-oracle-database-21c-on-centos-8/
If you have any questions/queries/concerns, please let me know in the comment section. Find this write up useful ? Share with your friends now !
