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 system 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
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?!
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.
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.
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 and 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 network so that I can reach more people. Thanks & have a great day!