SQL tutorial 46: What are System Privileges & How To Grant them using Data Control Language

Key Takeaways

what's up internet welcome back once again this is Manish from reali.com and as I promised in my previous video today we will learn about system privileges in this SQL tutorial as we have already discussed in the previous video system privileges are some powerful special rights given to a user to perform a standard administrator task in the database as these rights are quite powerful that means you should be a bit cautious while granting them I have done a blog post over system privileges on my website where I have explained everything in detail I encourage you to read it so without wasting much time let's jump over SQL Developer and start the fun for the demonstration I will use two users Hulk and Batman which I created during my create user tutorial if you haven't watched the tutorial on how to create user then find the link in the description box Hulk and Batman both these users are normal users and don't have any rights or privileges except the create session system privilege so now I'm going to demonstrate you the different ways of granting system privilege to these user as you know to assign any privilege to any user in Oracle database we use Grant data control language statement or DCL statement and to use Grant statement you must have either Grant any privilege system privilege or you must have been granted the system privilege with admin option and if you are a CIS GBA then you can simply log on to your database using CIS user with CIS GBA privileges which I'm going to do right now I'm going to connect to my database using my CIS user and now I'm connected to my database using my sis user let me show you the user funds as you can see my user assist now let's perform our first cury in our first cury we will grant a very basic privilege which is create table to our user Hulk to Grant create table system privilege to user Hulk we will write this cury is fairly simple Grant is an oracle keyword followed by create table which is the name of system privilege which we want to Grant to our user H then we have another keyword two followed by the username hul to which we want to assign this privilege isn't it simple now let's execute Grant succeeded now let's move on to the second cury generally after creating a user we have to Grant several system privileges to them in this case you can either write separate Grant statement for granting individual privilege or we can Club all the privilege in a single Grant statement let's see how say we want to Grant create synonyms create View and create sequence system privilege to the user hul for that we will write cury is very similar to the above one except that here we write all the system privileges together and have them separated using comma but here we have to take care of a few things such as this list of privileges must only contain system Privileges and not any object privileges this is because system Privileges and object privileges cannot be granted together in a single Grant command remember this tip because if you are preparing for SQL expert exam then chances are there that you may face this question let's execute Grant succeeded now third cury in the previous quy we saw how to Grant more than one system privileges to only one user in single Grant statement what if you want to Grant privileges to more than one user in this cury we will see how to Grant system privileges to more than one user in single Grant statement let's say we want to Grant create procedure system privilege to both our user Hulk and Batman for that the cury will be cury is pretty similar to the last one except that this time we write the username separated by a comma now let's move on to our next cury cury 4 in this cury I will demonstrate you how to Grant system privilege with admin option but before moving ahead let me tell you that you can use admin option flag only while granting system privileges not with object privileges granting a system privilege with admin option flaged means that the grantee can grant or revoke the system privilege or role to or from any user or any other role in the database also the grantee can further Grant the system privilege or role with the admin option to any other user or role so let's say we want to Grant create trigger system privilege to our user Batman with admin option then for that we will write on executing this cury our user Batman not only gets create trigger system privilege but can also Grant revoke and drop the create trigger privilege to and from any user and roles so let's execute this query granted succeeded now our user Batman has create trigger system privilege with admin option means user Batman can now Grant this create trigger system privilege to any other user let me demonstrate this thing to you for that what I will do I will use my command prompt and here is our Command Prompt now what I will do I will will connect to my database using the user Batman for that I will write now I'm connected to my database using the user Batman let me show you as you can see the user is Batman now I will grant the create trigger system privilege to use a Hulk using the user batchman for that we have to write if you want you can also add with admin option here now hit enter Grant is succeeded see that is the purpose of with admin option flag that's it for today's guys follow me on my social networking for regular updates and tips and tricks on Oracle database all the links are in the description below give me thumbs up if you like this video and want me to do more such videos you can also comment and tell me how you like this video that will make me really happy don't forget to subscribe we'll see you soon guys this is Manish from Rebellion rer