0

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.

  • 1
    possible duplicate of Create user with admin option oracle 11g command not working - rather than blindly trying things out from other databases, please search. – Mat Apr 21 '13 at 18:01
  • I searched on Google(its a search engine) and it was pointing to oracle documentations and blogs so....that is why I asked – adi Apr 21 '13 at 18:28
  • 1
    The link above, which answers your question, is the top Google result (thanks, I have a vague idea of what Google is) for 'oracle create admin user'. So you didn't use google enough. Also you didn't to read the Oracle docs, which you should have done. – Mat Apr 21 '13 at 18:30
  • 3
    Read the Oracle Server Concepts manual. Trying to use Oracle, an enterprise grade RDBMS, when coming from a MySQL background is a big step up. You'd do we'll to forget what you know about MySQL, start from scratch and read some Oracle books. – Philᵀᴹ Apr 21 '13 at 22:49
  • Thank you for tips Phil, but I'll use Oracle SQL for class only and to finish the stuff I start at classes. For own actual and future projects and stuff I will stay focused on MySQL. – adi Apr 22 '13 at 9:22
10

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
| improve this answer | |
1

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.

| improve this answer | |
0

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;
| improve this answer | |
-1

create user Rokon identified by Rokon;

grant create session, grant any privilege to Rokon; connect Rokon/Rokon@dlicl

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.