Home » SQL & PL/SQL » SQL & PL/SQL » How to create user in Oracle ?  () 1 Vote
How to create user in Oracle ? [message #296831] Tue, 29 January 2008 03:36 Go to next message
mannlucky
Messages: 3
Registered: January 2008
Junior Member
how to create a user in oracle?
Re: How to create user in Oracle ? [message #296832 is a reply to message #296831] Tue, 29 January 2008 03:45 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Create user

Thumbs Up
Rajuvan.
Re: How to create user in Oracle ? [message #296839 is a reply to message #296831] Tue, 29 January 2008 04:03 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
To create a user first you would have the previlages then youo can create a user by using the below statement.
create user user_name identified by user_password 

[Updated on: Tue, 29 January 2008 04:03]

Report message to a moderator

Re: How to create user in Oracle ? [message #296871 is a reply to message #296839] Tue, 29 January 2008 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Once again your answer does not add anything to previous post.
Are you able to answer more than what was already said?

Regards
Michel
Re: How to create user in Oracle ? [message #298696 is a reply to message #296831] Thu, 07 February 2008 03:36 Go to previous messageGo to next message
jyothsna1217
Messages: 9
Registered: February 2008
Location: hyderabad
Junior Member

conn system/manager
create user scott identified by tiger;

After that system/manager has to grant some privileges

grant connect,resource to scott;


Re: How to create user in Oracle ? [message #298745 is a reply to message #298696] Thu, 07 February 2008 07:55 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
jyothsna1217 wrote on Thu, 07 February 2008 04:36

After that system/manager has to grant some privileges

grant connect,resource to scott;





Not true.
Re: How to create user in Oracle ? [message #322014 is a reply to message #296831] Thu, 22 May 2008 02:49 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
HI i think the proper and technical answer should be like this please correct me if i made any mistake


connect username/password ---any username whose have privs to create user

create user test identified by test ----user created

but still this account can't be use as no privs is assigned till now.

so give privs as;

grant connect,resource to test

above commands create user who can connect only further if we want to use account for other purpose we need to give more privs.


Regards:
Gagan Deep Kaushal
Re: How to create user in Oracle ? [message #322016 is a reply to message #322014] Thu, 22 May 2008 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
grant connect,resource to test

No, never use these predefined roles, above all NEVER use RESOURCE. You can use CONNECT in 10g and up, it is equivalent to CREATE SESSION privilege.
Always define yours with the appropriate privileges.

Regards
Michel

[Updated on: Thu, 22 May 2008 03:03]

Report message to a moderator

Re: How to create user in Oracle ? [message #322252 is a reply to message #322016] Thu, 22 May 2008 23:07 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
Thanks A lot for correcting me .But Michel Cadot could you please clear me more that when i am granting RESOURCE to user what privs exactly he got ..

Again thanks for replying..


Regards:
Gagan Deep Kaushal

[Updated on: Thu, 22 May 2008 23:10]

Report message to a moderator

Re: How to create user in Oracle ? [message #322254 is a reply to message #322252] Thu, 22 May 2008 23:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
But Michel Cadot could you please clear me more that when i am granting RESOURCE to user what privs exactly he got ..

This is the reason why you MUST NOT give this role, you don't know what is inside.
Create your own role then you will know what privileges you give.

Regards
Michel
Re: How to create user in Oracle ? [message #322274 is a reply to message #322254] Thu, 22 May 2008 23:51 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
You are very right to this piont.
We are totally bookish or follow some trend that does not have base..

thanks Michel Cadot
but it give me more pleasure if you give me full detail about privs.



Regards:
Gagan deep Kaushal
Re: How to create user in Oracle ? [message #322285 is a reply to message #322274] Fri, 23 May 2008 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Privileges and roles granted can be seen in:
dba_role_privs
dba_sys_privs
dba_tab_privs
dba_col_privs

Regards
Michel
Re: How to create user in Oracle ? [message #322500 is a reply to message #322254] Fri, 23 May 2008 15:57 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 23 May 2008 06:12
Quote:
But Michel Cadot could you please clear me more that when i am granting RESOURCE to user what privs exactly he got ..

This is the reason why you MUST NOT give this role, you don't know what is inside.
Create your own role then you will know what privileges you give.



Back then, not all but some DBAs created a user and granted CONNECT and RESOURCE roles to this newly created user by default. For most purposes, everyone was quite happy as these predefined roles were enough for an ordinary user to start working, while DBA didn't have to think about all possible privileges one could need.

Then it was suggested not to grant CONNECT and RESOURCE any more, but create your own roles (for example, MY_CONNECT and MY_RESOURCE), make sure which privileges I've granted to them and deal with these roles from now on.

However, what's wrong with the original CONNECT and RESOURCE? Why wouldn't I know which privileges they own? Something like this:
SQL> SELECT grantee, privilege, admin_option
  2  FROM DBA_SYS_PRIVS
  3  WHERE grantee IN ('CONNECT', 'RESOURCE')
  4  ORDER BY grantee, privilege;

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE TYPE                              NO

9 rows selected.

SQL>

Now that I know what RESOURCE is, is it acceptable to grant it to my newly created user? Of course, if everyone's happy with these privileges? I guess it is.

However, not for long. Oracle announced that both CONNECT and RESOURCE roles will be deprecated in future Oracle versions.

Therefore, all these lovely privileges which once existed in Oracle 8i are about to be gone. For your information, these were
  • CONNECT:
    ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
  • RESOURCE:
    CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE
A conclusion? Do as Michel said - create your own role(s) and use them in the future.
Re: How to create user in Oracle ? [message #322520 is a reply to message #322500] Sat, 24 May 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In addition, well note that they are predefined role but the associated privileges list is Oracle proprietary and they can change it in any version as they want and they did it as LittleFoot showed it!
What happens now to your applications that rely on CONNECT?

Also note that with RESOURCE you hiddenly gain UNLIMITED TABLESPACE that is the privilege to full up your SYSTEM tablespace and crash your database. Useful this role? No, just dangerous.

Predefined roles are ONLY for Oracle, not for customers. Even DBA one.

Regards
Michel
Re: How to create user in Oracle ? [message #322685 is a reply to message #322500] Sun, 25 May 2008 22:58 Go to previous message
gaganzk
Messages: 36
Registered: May 2008
Member
Thanks Littlefoot your answer is really cool.I got everything i need .
Thanks a lot dear keep posting like this.
Great job


Regards:
Gagan Deep Kaushal
Previous Topic: how to grant all normal user previleges in a single statemnt..
Next Topic: plsql
Goto Forum:
  


Current Time: Fri Feb 14 10:06:42 CST 2025