Oracle Database - 21C | Create User, Delete User and Grant Permission to User using CDB and PDB

right guys so this brings up to another video on Oracle database which is running on the version 21c now what we are going to discuss is not about the container database or the plugable database it's about how do you create the user within the container database and within the plugable database so why container and Y pluggable that's something that we will anyways deal with in the later part of the tutorial but now we're just going to deal with what is container how do we create a user and drop the user in container database how do you create the user and drop the user in the plugable database that's all it is okay so pretty cool with that the version that I'm running right now is 21c Okay so 21c cool so in the command prompt not in the command run in the windows run button you can say SQL plus okay I'm just going to open that and I'm going to connect with system and I want to say password admin123 now the moment if I say show connection underscore name as you can see right now I'm in container database if you can see here foreign okay I need to shift it to pluggable I'll tell you how to do that now first what you're going to do you're going to create the user okay if I say create uh I'm just about to type the command type it the command I'm just going to take it from my notepad what I've created it's here I'll just take this and I paste it okay create user I'll say uh Oracle orc orc USR 0 1 now if I say if I enter obviously it says that invalid common user or role name see this this guy okay it just means that if you are working in a container database make sure that you add something like this see hash and see the C that I write I've written over here denotes the container database see now the user has been created now if I say select star from all underscore users now if you can see here here you have the user okay I just ignore the above one see there are totally 39 users so far identified users if you can see here like dvf LBA c s y s and let's crawl up a little bit here and as you can see you have many users here one two here and each of them are are having the default users created okay now we'll go back here um sorry um we'll go back here okay let's scroll a little bit up okay I just want to show you certain default users here uh says and then you have answers system and you remember I just logged in Via system okay now with that being said there are total of 39 okay come on okay now this is a custom user that had created long back so if you want to drop the user you can say drop user take this copy paste it and say Cascade and that's it the user will be dropped and if I say select star now the count should be 38 here 38 the user have been dropped off now obviously once the user is created you need to give the permission to user that hey I'm going to give the permission to this user by enabling a session using that I'll give a session access I am going to give a session access to you and with the help of a session access you can able to login to the to that particular user okay so to give the session let's take this grant create session see Grant create session let me copy this copy this and this is the let me get this yeah so we can see this is the syntax Grant create session and give this username that's it whichever user you are intended to give the session and give the session okay could see Grant has been succeeded now with this user which is in the container database you can log in so just log into command prompt and take this syntax okay the syntax would be just I'll show that we're going to say SQL Plus this is a username this is a password at the rate at the rate zero zero zero this is nothing but your Local Host see in this localhost this is this is my machine in this my in this machine where I've installed my database right if you have installed in a different server then this IP address would be different obviously right since I've installed in my local machine obviously you would have given as 0.0.0.0 and the port obviously the port and here right so here is the thing here I have given as xepdb1 now what is this now this is a plugable database this is a plugable database now first before getting into this plugable database let's create a user in the plugable database and see how we can log in now obviously if I give this user name and try to log the if I give this option and try to log in with this it kind of doesn't work out okay so let me show that anyways here I'll take this username okay and I'm going to paste it over here okay paste it over here okay and just let me check the password yes I've been one two three it's had been one two three zero dot zero dot zero at the rate of localhost Port is one five two one I'm gonna give us this obviously this is not correct this is not correct because it's not a plugable database so just let me exit this out sorry just let me exit it out right seems I've exited out the whole window okay cool um I think I've done a mistake okay see here um you're not able to connect with this particular syntax because you are using xkp DB1 okay now let me bring up the command prompt again and see this what I'm going to do and I'm going to remove this xcpdb one Remember by default it is container database if it's a container database you don't need to explicitly say slash container database okay I want to take this okay place it over here again password and rest of them Remains the Same okay now can you see this it has been connected because I've removed this card xapdb1 because there's no need there is no need to give the content database because obviously by default it is connected to container now here is the thing now now we are going to shift from container to plug a bell okay we are going to shift from container to plugable let's go back to this guy and we are going to say alter see again let's say show connection underscore name we are in container database now we are going to alter alter this session set container equal to xeb DB1 by the way how do I know this is my plugable database once again let me show that I'm going to say show plugable databases okay okay so pdbs okay I think the one of the reason why you can't see this is because uh of the sysdb access that's quite natural it's not it's not a problem you can just say you can again login as connect as sys DBA okay so maybe this can show us pdbs let's see this yeah see it's only because the previous user does not have the taxes to show ssdva I just connected to assist DBA and I've used show plugable databases now let me alter this session alter session set container is equal to xepdb1 now let's see show connection underscore name as you can see right now we are in xcpdb1 now I'm going to so let me select the user select star from all users and yeah see obviously you have the one that I've created right now okay now I'm going to create another user okay in plugable database what I'm going to do I'm going to take this take this user syntax paste it come on paste it I'm going to say the user called Web M user webm user and admin123 now can you see this it has created initially we have to give and see hash in container database but in plugable database you don't need to give C hash it has automatically picked it up now obviously you have to Grant decision take this Grand session two problem user you have granted the session that's awesome then let's try to correct again here um okay let's say let's exit from the SQL and here I'm going to say Grant session okay webmusic okay so I'm going to say here it has like this webmusic admin123 at the rate of localhost Port is 5521 and I'm going to say x-e-pdb1 can you see this now with the help of xcpdb1 it is connected to this version okay so that's how you create the plugable database create using blockable database and create user in the container database obviously we have also seen how to drop the user and it's it's quite not that difficult if you have to drop a user say again drop user username so webmusic let's say Cascade that's it now here's the stuff I don't want to do that at this stage now I've given the permission to session in a similar Way what all permissions do we have so here we have permission we have we have privileges we can grant with connect user privilege privileges we can give unlimited table spaces we can give create view create procedures create sequence create trigger we can give permission to alter any tables alter any procedure alter any trigger we can give permission to delete drop so many stuff of all those things what I'll say I'm going to use this command I'm going to say Grant all privileges check boss I'm going to give you all the privileges do well whatever you want with that I want to say Grant all privileges to where I'm right now so I should be in SQL plus grant all privileges to let me copy this again it should be in SQL plus and it's a grand all Provisions to webm user okay I'm sorry remove This chords grab them user see I've given all the permission all the Privileges now web Amazon user is become kind of become like a super system DBA Superman kind of thing Okay cool so webmusic has been created now it's time to test in our SQL Developer I just want to bring up SQL Developer okay it's another software that will help you to connect to the data process just want to check that before you kind of wind up this session okay and one more thing I'm just going to say select all select star from all underscore users okay so you have C Oracle user from container database and webm user from plugable database okay so just let me exit this exit and exit okay so there is a skill developer so I have open by now now so just let me open it up again sorry not this one so SQL Developer okay there you go it's kind of opening up okay there you go it has opened up okay now let's go back here and instead of the plus icon and here the first thing that I'm going to test is our system user okay the Oracle database system user okay the password is admin123 okay and I'm going to test with the service name xeb DB1 that's a plugable database test as you can see it's success save it now I'm going to test with our webm user okay copy it so webmusic.com user and the password Remains the Same again and it's a plugable database I'm going to say connect test before that test as you can see success I'm Gonna Save the password I'm going to save the connection okay and here also I'm going to save the password just before I don't forget it okay because I'm getting old okay save just want to override it that's okay now this is webm user as you can see this webmusic this is a username and the password and this is a local host as we as we had seen it right 0.0.0 and the port and the plugable database we are given slash remember that syntax that syntax is getting translated here that's all it is see in the syntax what we had given we had said SQL Plus SQL plus and then username and password username and password and then at the rate of these two things okay not this or not these two these two yeah at the rate of localhost and one five two one slash and finally we are given this guy over here remember that is exactly what we are seeing right now okay that's that's how that's connected and yeah there you go it has been connected successfully cool okay now let's go back and check with this guy C hash user um see hash user let's see what happens okay and same let us test obviously see it's a failure because it is not a plugable database remember it's not a plugable database okay and uh since it's not a plugable database okay let me see if this works see it's it should be in the uh Global name now what is a global name see I'll tell you if I go back here and say select star from global underscore name see now right now this is an xcp DB1 now if I try to exit out of it then reopen my scale plus and I'm going to say connect as this DBA here select star from global underscore name can you see it the global name is xe XE here since this C hash which is a container based is connected to the global database that's the reason the service identifier Sid service identifier should be in XE because we are connected through container database and we have worked it out all right guys so this is how you create the user and obviously I forgot to give the permission to this guy I'm going to say Grant all privileges to our C hash user come on copy paste so it'll be unfair if I don't give a complete um access to it correct so all the Privileges have been given to this guy as well now cool with that being said okay so let's let me connect it test it one more time save it um okay this is not acceptable CA hash user okay save it that's it connect as you can see I'm able to connect to this as well cool with that being said we are done with this part of the video and this tutorial talks about the user creation user deletion Grant the user permission the help of Oracle database 21c cool I hope you have learned something yeah okay that's it for this video and see you guys in the next tutorials until then take care peace

Channel: Integration Anarchy

Views: 12,418

Keywords: online class, online tutorial, online training, computer programming, computer training, software development, software training, oracle, oracle database, oracle database express edition, oracle 21c, oracle training, oracle online classes, oracle 19c, oracle training session, oracle pluggable database, oracle container database, oracle create user, oracle delete user, oracle grant permission, oracle database 21C

Id: dzM_56uNWg0

Length: 18min 21sec (1101 seconds)

Published: Mon Nov 28 2022