I have seen one Post on facebook in Oracle DB group that someone is asking, How to create user in oracle database. I know it's very silly question but it happens if you are new to Oracle. So I am only writing this post for those people how do not want to study the books.
So here are the steps to create user in Oracle database. Theses steps will be same in 10g,11g and 12c. I will discuss two scenarios here :-
- create simple user
- create user with attributes
- Create simple user
Step1 :- You can connect to database with sysdba privileges.
e.g. sql > conn / as sysdba
Step2:- After connecting to sysdba, you need to use “CREATE USER” command to create new user. “ALTER USER” is used to modify the properties of the users like password, profile etc. Please note that users name should be unique. You cannot create user wit the same name.
e.g. sql > create user test identified by test;
Step 3:- With above command your database user created, But now you cannot connect to the database with this user, WHY? because you haven’t granted this user to connect to database and create the session.
You need to grant your user as below:-
e.g. sql > grant create session to test;
sql > grant connect to test;
Now you can connect to the database by this user test.
e.g. conn test/test@TNSNAME
- create user with attributes
creating user with attributes means you assign properties at the time of creation e.g. suppose we need to create a user and we need to specify its tablespace with quota and also want to assign a profile to this user.
below is the example:-
sql> create user test identified by test default tablespace test temporary tablespace temp quota unlimited on test profile test;
This will create a user test with password test tablespace test and profile test.
Keep Sharing :)