đ
SQL tutorial 46: What are System Privileges & How To Grant them using Data Control Language - YouTube
Channel: Manish Sharma
[0]
Whatâs up internet?
[1]
Welcome back once again this is Manish from
rebellionrider.com
[3]
And as I promised in my previous video, today
we will learn about system privileges in this
[8]
SQL tutorial.
As we have already discussed in the previous
[11]
video, System Privileges are some powerful
special rights given to a user to perform
[16]
standard administrator tasks in the database.
As these rights are quite powerful that means
[20]
you should be a bit cautious while granting
them.
[23]
I have done a blog post over System privilege
on my website where I have explained everything
[28]
in detail; I encourage you to read it.
So without wasting much time letâs jump
[32]
over to SQL developer and start the fun.
For the demonstration I will use two users
[37]
HULK and BATMAN which I created during my
âCreate user tutorialsâ. If you havenât
[42]
watched the tutorials on how to create user
then find the link in the description box.
[47]
Hulk and batman both these users are normal
user and donât have any rights or privileges
[52]
except the create session system privilege.
So now I am going to demonstrate you the different
[58]
ways of granting system privileges to these
users. As you know to assign any privilege
[64]
to any user in oracle database we use GRANT
data control language statement or DCL statements.
[70]
And to use GRANT statement you must have either
GRANT ANY PRIVILEGE system privilege or you
[75]
must have been granted the system privilege
with the ADMIN OPTION. And if you are a Sysdba
[80]
then you can simply log on to your database
using sys user with sysdba privileges. Which
[85]
I am going to do right now,
I am going to connect to my database using
[89]
âsysâ user.
Now I am connected to my database using my
[94]
âsysâ user. Let me show you the user once.
As you can see my user is sys.
[99]
Now letâs perform our first query.
In our first query we will grant a very basic
[104]
privilege which is create table to our user
HULK. To grant create table system privilege
[109]
to user hulk we will write
GRANT create table TO hulk;
[114]
This query is fairly simple, Grant is an oracle
keyword followed by create table which is
[120]
the name of system privilege which we want
to grant to our user HULK. Then we have another
[125]
keyword TO followed by the username hulk to
which we want to assign this privilege. Isnât
[133]
it simple?
Now letâs move on to Second Query
[139]
Generally after creating a user we have to
grant several system privileges to them. In
[144]
this case we can either write separate grant
statements for granting individual privilege
[148]
or we can club all the privileges in a single
Grant statement. Letâs see how.
[153]
Say we want to grant create synonym, create
view and create sequence system privileges
[158]
to the user HULK, for that we will write
GRANT create synonym, create view, create
[171]
sequence TO hulk;
Query is very similar to the above one except
[175]
that here we write all the system privileges
together and have them separated using comma
[180]
(,).
But here we have to take care of a few things
[183]
such as this list of privileges must only
contain system privileges and not any object
[188]
privilege. This is because system privileges
and object privileges cannot be granted together
[194]
in a single grant command.
Remember this tip because if you are preparing
[198]
for SQL Expert exam then chances are there
that you may face this question. Letâs execute
[204]
the query. Grant succeeded.
Now third query
[209]
In the previous query we saw how to grant
more than one system privilege to only one
[214]
user in a single Grant statement. What if
you want to grant privileges to more than
[218]
one user? In this query we will see how to
grant system privilege to more than one user
[224]
in a single grant statement.
Let say we want to grant create procedure
[227]
system privilege to both our user Hulk and
Batman. For that the query will be.
[237]
GRANT create procedure TO hulk, batman;
Query is pretty similar to the last one except
[244]
that this time we write the username separated
by a comma.
[248]
Now letâs move on to our next query (query
4).
[252]
In this query I will demonstrate you how to
grant system privilege with Admin Option.
[256]
But before moving ahead let me tell you that
you can use admin option flag only while granting
[261]
system privilege not with object privilege.
Granting a system privilege with Admin Option
[267]
Flag means that the grantee can grant or revoke
the system privilege or role to or from any
[273]
user or any other role in the database.
Also the grantee can further grant the system
[279]
privilege or role with the ADMIN OPTION to
any other user and role.
[282]
So letâs say we want to grant CREATE TRIGGER
system privilege to our user Batman with Admin
[288]
Option then for that we will write.
GRANT create trigger TO batman WITH ADMIN
[296]
OPTION;
On executing this query our user batman not
[299]
only gets create trigger system privilege
but can also grant, revoke, and drop the create
[304]
trigger privilege to and from any user and
roles.
[311]
Now our user batman has create trigger system
privilege with ADMIN option, Means user batman
[316]
can now grant this create trigger system privilege
to any other user. Let me demonstrate this
[321]
thing to you. For that what I will do I will
use my command prompt.
[327]
And here is our command prompt.
Now What I will do I will connect to my database
[330]
using the user Batman. For that we have to
write. Now I am connected to my database using
[338]
the user Batman. Let me show you the user.
As you can see the user is Batman.
[343]
Now I will grant the create trigger system
privilege to user HULK using this user BATMAN.
[348]
For that we have to write. If you want you
can also add WITH ADMIN OPTION here. Now hot
[357]
enter, grant is succeeded.
See ! that is the purpose of WITH ADMIN OPTION
[362]
flag.
Thatâs all for today guys.
[364]
Follow me on my social network for regular
updates and tips and tricks all the links
[370]
are in the description below.
Give me thumbs up if you liked this video
[373]
and want me to do more such videos. You can
also comment and tell me how you like this
[377]
video. That will make me really happy.
Donât forget to subscribe
[381]
Will see you soon. This is Manish from RebellionRider.com
Most Recent Videos:
You can go back to the homepage right here: Homepage