SQL tutorial 40: DBA Trick, How to drop a user when it is connected to the database

Key Takeaways

was a burden a diamond is from rebellionrider.com and today I'm going to do a bonus equal tutorial which is all about how to drop a user I know you must be thinking what is so special about it yes I agree that dropping a user is fairly simple task when user is not connected to the database but what if user is connected to your database then what will you do in that case dropping a user can be quite tricky being a database administrator creating and dropping a user is one of the most frequent tasks that you will have to perform till now I have shown you three different ways of creating a user in Oracle database if you haven't watched these tutorial then please find the link in the description box having said that today I will show you how to drop a user when it is connected to the database and when it is not connected to the database as well so let's start today's tutorial first we will see how to drop a user which is not connected to the database to drop a user you must have either drop user system privilege or you must log on to your database using sis user with system your privileges I have 3 user by the name of Ironman Batman and Superman which I created in my previous tutorial on how to create a user right now I am not connected with any of them and I'm going to drop these 3 user in today's tutorial for the demonstration so first I will connect to my database using my sis u services DBA privileges I have already created a connection by the name of sis at localhost if you don't know how to make this connection using sis user please watch my tutorial 38 where in the first half I have explained how to make connection which says user so let's try to connect to a database using sis user so we are connected to the database using sis user let me show you the user was user is sis now I am going to drop a user Ironman user Ironman is not connected to the database this user does not on any object such as tables indexes views etc etc this means I have not created any tables sequences indexes or anything using this user this is just an empty user in this case when user does not on any object you can simply write drop user followed by username which you want to drop so to drop user Ironman we will write just a simple Kure drop user followed by the name of user which you want to drop in our case it's Ironman now let's execute this Kure user Ironman is draw in case user on some object that is if you have created tables or indexes or anything using the user then we use cascade dropping I have created a table in user Batman for the demonstration let's first try to drop this user normally let's execute you got an error now just add cascade keyword in this drop user Kure right after the username which is Batman but first let me close this script output okay now add cascade right after the username which is Batman now let's try to execute this query and user Batman dropped so till now we have seen how to drop a user which are not connected to the database now let's see how to drop a user which is connected to the database for the demonstration purpose I will use user Superman let's first connect to the database using this user Superman for this I will use command prompt let me show you the user as you can see now we are connected to the database using this user Superman now let's come back over sequel developer and try to drop it using drop user come on let me close this script output first now we will try to drop a user Superman which is now connected to our database execute we got an error just to be clear let's also try cascade drop let me again close this script output again error let's assume this user Superman is a user with malicious intention to hamper your database and you caught this while auditing and now you want to drop this user the first thing which comes into the mind is just to disconnect this user and then drop it what if as soon as you disconnect this user it reconnect itself like this you will stuck into a loop and won't be able to drop it so you have to make sure that after you disconnect the user it will not be able to reconnect itself to the database there are series of steps which you have to perform to do this task of dropping this user and I am going to demonstrate all these steps now first let me close this script output and let me also minimize this connection tab okay everything is perfectly visible to you guys I think now step one make sure you are connected to your database using sis user and sister B of privileges I am already connected to my database Vicis user step 2 now you have to put your database into restricted mode there are two ways of doing so first is shut down your database completely and then started with startup restrict option I don't think this is a wise option as you will probably not want to disturb all the database user just for dropping a single user and get fired the next morning second option which I recommend is using alter system come on let me show you how as I have already told you that you must be login as a sis you services DBA privileges once you have done that then right just execute this command system enabled altered by default our database is in open startup mode now we just change the mode from open to restricted when the database is started in a restricted mode the user that are already connected can continue their work without any interruptions unless they are somehow disconnected once any of them are disconnected they have to have a restricted session privilege which is a special system privilege to connect with the database again furthermore no new user can connect with the database step 3 now we have to get this session identifier and this serial number of the user which we want to drop for that we will use we dollar session view such as this curie is fairly simple we are retrieving s ID which is session identifier not the database s ID which is stand for system identifier and the serial number from v dollar session view and we have our where clause where we have specified the user name make sure user name will be in all caps and enclosed in single quotes now let's execute this query here we get the s ID and serial number of the user Superman which we are going to delete now step 4 in this step we will disconnect the user Superman from the database and as we are in restricted mode then this user will not be able to reconnect to the database that's the purpose of restricted mode using the S ID and serial number which we obtained in the previous step we will now kill the session or say disconnect the user for that just write the queue let's execute this QD system kill altered now this user is disconnected from the database and you are all free to drop it now step 5 drop the user let me first close this script output panel and this Curie result also using drop user command you can drop this user as we did at the starting of this tutorial if this user has object then use drop user cascade command otherwise simple drop user comma in my case I will use costly dropping okay let's execute this drop user command user Superman is dropped now step 6 the final step now user has been dropped but our database is still into the restricted mode we have to bring our database back into the normal mode in step 2 we enable the restricted session now we will disable it for that we will again write an alter system Curie s let me again close this script output now let's write the Curie let's execute system disabled all turn that's it that's all you have to do you can also visit my blog where I have written every step in DT link is in the description box hope you learned something please tell me how you liked this video and do you want more such videos just leave a comment below also please like my video this makes me really happy and keep me motivated I am very thankful to you all guys signing off for today we'll see you soon guys with another amazing sequel tutorial till then take care keep watching and liking my videos and don't forget to subscribe thank you this is manish from rebellionrider