SQL tutorial 38: How to create user using SQL Developer in Oracle database

Key Takeaways

what's up internet I'm Manish from Rebellion rider.com and I'm back once again with another SQL tutorial today in this SQL tutorial you will going to learn how to create a new user account using SQL Developer tool in the previous video I showed you how to create a new user account using SQL create user statement there I explained the syntax of SQL create user with example in detail so I highly suggest you to please watch my previous video for the better understanding of this concept you can find all the links in the description box let's start today's tutorial as you can see here I am on my SQL Developer now the first thing as I have already told you in my previous tutorial is that in order to create a new user account you must have create user system privilege or you can use CIS or system user account for creating a new user these are two highly privileged administrative account created automatically during installation so I'm going to log on to my database using CIS user so let's quickly create a new connection to connect with our database using CIS user by the way I have done a detailed video on how to connect with database using SQL Developer and command prompt it's tutorial 4 you can watch it link is in the description box to make a new connection click this big Green Plus button it will open up a new window now here in this new window you have to fill in all these detail in connection name you can give any name you want I will write let's say sis at localhost now username here you have to give a valid username and in our case it's sis as we want to log onto our database with CIS user now password my password for CIS user is Oracle check this checkbox next to the save password if you want you can provide a color for this user I don't want to give any color connection type Let It Be basic it's okay rooll you must choose CIS DBA here otherwise you will get an error now host name my host name is local host for the obvious reason if your database is installed on server then you can give your server name or IP address 1521 port number it's okay for me I have configured my database on default Port which is 1521 you may have different port number you can check your listener.ora file for valid host name and port number my Sid is orcl watch my tutorial 4 for detail information Let's test this connection success now click save and then click connect now we are log on to our database using our CIS user let me quickly show you the user and as you can see our user is sis so now double click on your CIS user and expand it here select the last option which is other user right click and then select create user this will open up a create user visit write the name of your user in this username field you can give whatever name you want in my previous video I made a user by the name of Iron Man so today I'm going to make a user by the name of Batman write password in password field for the authentication of your user write the same password in confirm password if you check this check boox next to the password expire field then your user has to change the password on the first login attempt similarly you can log the user and enable the addition I don't want to lck the user and expire the password or enable addition so I'm not going to check any of the box here next is table spaces here you have to choose the default and temporary table spaces as default table space I will choose example this time you can choose any table space as default table space except Temp and undo table space and in my temporary table space I will choose temp I have only one temporary table space by the name of temp everything is done here now click this ground Ru tab now what are roles roles are group of privileges Suppose there are large number of user and you have to assign same system and object privileges to them this will become very tedious task if you start granting individual privilege to each of these user so instead of doing this you can create a role where you will group all the Privileges and Grant this role to the user don't worry I will do a separate video on role soon but for now I don't want to assign any role to my user Batman so I'm not going to select anything here okay now come to system privileges step these are all system privileges which you can assign to your user and if you remember I told you guys in my previous tutorial that to establish a connection with database every newly created user must have create session system privilege create session system privilege is mandatory privilege needed by all the users to log on to the database so here also we have to assign create session privil to our user Batman let's search for create session here and here it is now check this checkbox against the name of privilege in Grant column if you check this admin option checkbox then this user this means user Batman which I'm creating right now can grant or revok the system privilege or role to or from any user or other roles in the the database but I do not want to assign admin option to our user Batman so I'm not going to check this check box here okay say if you want to create table using this user then you grant create table to your user let's search for create table and here it is now check this check box here okay everything is done you can browse this list of Privileges and see what privilege you want to assign and then Grant accordingly done here now come to the Kota tab using Kota Clause you can specify the maximum amount of space that the user is capable of allocating in table space before that let me tell you one thing you cannot assign Kota on temp and undo table space if you try to do this you will get an error now if you want to allocate unlimited space to the newly created user then you can just sck on the checkbox provided against the name of table space which in this case is example you can even allocate a certain quota of space on the table space named example by double clicking on the appropriate box in the quota column and filling in the amount of space that you want to give similarly you could even specify the units such as k for kilobyte M for Megabyte and G for gabt so what I'm going to do I'm going to allocate 20 MB of example table space to our user Batman for that I will write 20 here and then hit enter and it's MB so I will write M here that's it now the next step is SQL which contains all the SQL statement corresponding to the settings which you have done so far if you are learning SQL then you can take help from these SQL statements everything is done here if you want you can review your setting once again otherwise click apply successful proceed SQL command now a new user account is created just click okay and then close you can check your user for that expand this other user folder here you can see the list of all the users account of this database let's search for our user and here it is so now you have your new user account created let's try to log onto our database using this user bman to save the time I will use the command prompt for the demonstration as you can see we are logged on to our database using our user Batman let me quickly show you the user and here is our user Batman that's how we create a new user account using SQL Developer tool that's it for today hope you enjoyed watching please hit the Thumbs Up Button if you like my video also do subscribe to my channel for more such interesting videos on SQL I will be back soon with another tutorial till then take care this is Manish