Creating an admin user in Oracle

All we need is an easy explanation of the problem, so here it is.

How can I create a “root” (like on MySQL) in Oracle?

I installed Oracle database on my system, for homework, for school (faculty) but it didn’t give me the option to make a root user. Well at least I don’t think so.

It asked me on preparation for install to create a password but when I tried connecting with the id and password it failed. (So that is why I presume it did not make a root user)

Connected on the databse with connect /as sysdba but don’t know how to create a user with admin privileges.

Tried

create user "user" identified by "password" with grant option;

(error)

create user "user" identified by "password";

(succes)

grant all on "user" with grant option;

(error)

I want to have all privileges on all tables, etc.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

you first need to connect as root to the database, then you create the admin user.

console

connect /as sysdba

sql

create user admin identified by secret
grant dba to admin 
conn admin/secret

for oracle 19C, use username with c## prefix .These commands are not working on 19c. Like create user c##admin identified by secret

Method 2

As far as the database is concerned, the most powerful user is the one connected as sysdba. As you have seen and by default, you can connect as sysdba locally using connect / as sysdba. While it is advisable to create another user for your work, a new user only granted the DBA role will have fewer privileges than / as sysdba.

Method 3

The answer current top answer will create a user with dba permissions, and that will give you all access to all tables, but it also gives you a bunch of additional permissions.

If all you really want, is a user that can read/write to any table, I would do the following

Create a new user and grant connect to it

sqlplus / as sysdba 
create user XXXXX identified by secret; 
grant connect to XXXXX;

I would then run the following query to generate a list of queries to grant insert, update, delete, select access to all user tables in the database. Take the list and run it once generated. This will give access to all tables, without providing the additional DBA access.

select 'GRANT INSERT, UPDATE, DELETE, SELECT ON '||owner||'.'||table_name||' to XXXXX;' from user_tables;

Method 4

create user Rokon identified by Rokon;

grant create session, grant any privilege to Rokon;
connect Rokon/[email protected]

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply