System privileges are some powerful special rights given to a user to perform standard administrator tasks in the database. These tasks can be any action on any schema objects for example create and drop a user or tablespace, flashback or lock any table and export as well as import the database and many more.
System privileges are quite powerful set of rights therefore when in wrong hands they could result in catastrophic events. Hence these should be granted very responsibly and only when absolutely necessary.
You can grant or revoke system privileges to users and roles. If you grant system privileges to roles, then you can use the roles to exercise system privileges.
Grant and Revoke
Grant and revoke data control languages (DCL)
help you in granting any kind of privilege to any role or user. Incidentally with the use of REVOKE statement you can revoke any privilege from a role or a user.
To grant a system privilege, you must either have been granted the system privilege with the ADMIN OPTION
or have been granted the GRANT ANY PRIVILEGE
For the demonstration of this particular topic I will use two users HULK and BATMAN which I created during my “Create user tutorials”. I would suggest you to read my article on how to create user
. Hulk and batman both these users are normal user and don’t have any rights or privileges except the create session system privilege
So the first example is
1. How to grant a System Privilege to a user
In our first query 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
GRANT create table TO hulk;
This query 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 HULK. Then we have another keyword TO which is followed by the username hulk to which we want to assign this privilege. Isn’t it simple?!
2. How to grant more than one system privilege to only one user in a single Grant statement.
Generally after creating a user we have to grant several privileges to them. In this case we can either write separate grant statements for granting individual privilege or we can club all the privileges in a single Grant statement.
Let’s see how.
Say we want to grant create synonym, create view and create sequence
system privileges to the user HULK, for that we will write
GRANT create synonym, create view, create sequence TO hulk;
Query 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 privilege. 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 then chances are there that you may face this question.
3. How to grant privileges to more than one user in single Grant Statement.
In the previous query we saw how to grant more than one system privilege to only one user in a single Grant statement. What if you want to grant privileges to more than one user? In this query we will see how to grant system privilege to more than one user in a single grant statement.
Let’s say we want to grant create procedure system privilege to both our user Hulk and Batman. For that the query will be.
GRANT create procedure TO hulk, batman;
Query is pretty similar to the last one except that this time we write the username separated by a comma.
4. With Admin Option
Before moving ahead at this point, let me tell you that you can use admin option flag only while granting system privilege not with object privilege
Granting a system privilege with Admin Option Flag means that the grantee can grant or revoke the system privilege or role to or from any user or other role in the database.
Also the grantee can further grant the system privilege or role with the ADMIN OPTION to any other user and 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.
GRANT create trigger TO batman WITH ADMIN OPTION;
On executing this query 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 that’s all guys on this topic. Hope it helped you understand the System Privilege concept. Please share this article on your social networking so that I can reach more people. Thanks & have a great day!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
SQL Script used in Video and in this article
I have used copy cloud to share these resourses. You can also join copy cloud and get 15GB free cloud storage for lifetime. If you will use this referal link https://copy.com?r=j7eYO7
You will get 5GB extra free cloud storage means total 20GB free cloud storage.