-4

How do I create a new user and give him access to ORCL database in oracle 10 g (TOAD/SQL Developer/SQL Plus). I tried to do this in SQL Developer, but when I try to login with the newly created user, it says "Insufficient privileges"

WAQ
  • 2,556
  • 6
  • 45
  • 86
  • 1
    `create user my_user identified by my_password` then `grant dba to my_user`; p.s. the question is well documented – are Dec 15 '15 at 11:31
  • @are where did you define which database you are giving access to this user? – WAQ Dec 15 '15 at 11:33
  • 1
    @WasimQadir Are you sure you mean database and not a schema? Database is isolation at physical level, schema is just a logical separation. Users and schema are synonymous. A user cannot access another database unless you have created the required database link and let the user do so. – Lalit Kumar B Dec 15 '15 at 11:34
  • 1
    please don't grant DBA to a user. start with the minimum privs required, such as CONNECT and go from there – thatjeffsmith Dec 15 '15 at 17:34

1 Answers1

0

I think you might be confused between database and schema. Also, remember, user and schema are synonymous. A schema contains the set of objects owned by the user. Other than that, they are similar.

What you need to do is:

SQL> create user test_new identified by test_new;

User created.

SQL> grant create session to test_new;

Grant succeeded.

SQL> conn test_new/test_new@pdborcl;
Connected.
SQL> show user
USER is "TEST_NEW"

Above example shows how to create a user, grant create session privilege just to make sure user is at least able to connect. You would obviously need more privileges for the user to do further tasks.

Usually, you would create roles and grant required privileges to the roles. And then assign the role to a user.

UPDATE

Per the comments, OP is unable to connect via SQL*Developer.

but when I try to login into the SQLDeveloper i get error message Insufficient privileges when Role is set to SysDba

You should not use SYSDBA for a normal user other than SYS.

ORA-01017: invalid username/password; logon denied

Either your username or the password is incorrect. In SQL*Developer you need to correctly provide the

  1. username
  2. password
  3. hostname
  4. port
  5. service_name

Test the connection, if it is success, start using it.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • this works fine in SQLPLUS/Command prompt but when I try to login into the SQLDeveloper i get error message `Insufficient privileges` when Role is set to `SysDba`, but role is set to `Default` it says `ORA-01017: invalid username/password; logon denied` – WAQ Dec 16 '15 at 09:05