Table of contents
1.
Introduction
2.
Syntax
3.
Parameters Used
4.
Privileges
5.
Grant Privileges on Table
6.
Different Ways of Granting EXECUTE Privileges
7.
Frequently Asked Questions
7.1.
Can I take back permissions after giving them?
7.2.
What if I grant too many permissions by mistake?
7.3.
Can a user with granted privileges give those to someone else?
8.
Conclusion
Last Updated: Mar 27, 2024
Easy

Grant Command in SQL

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

SQL is a powerful tool for managing & manipulating databases. One of its essential features is the ability to control who can do what within a database. This is where the GRANT command comes in handy. It allows database administrators to assign specific privileges to users, ensuring that database access is both secure & efficient. 

Grant Command in SQL

In this article, we'll explore the GRANT command, its syntax, parameters, the types of privileges it can bestow, & how to use it effectively to manage access to your database tables & procedures.

Syntax

The GRANT command in SQL has a simple structure that tells the database exactly what permissions to give & to whom. It looks something like this:

GRANT permission_type ON database_name.table_name TO 'username'@'host';


In this line, permission_type is the kind of access you're giving, like reading or changing data. database_name.table_name specifies where in the database this permission applies. Finally, 'username'@'host' identifies the user & where they're connecting from.

For example, if you want to let a user named Alex read data from a table called Students in a database named School, you'd write:

GRANT SELECT ON School.Students TO 'Alex'@'localhost';


This command makes sure Alex can look at the data in the Students table, but he can't change it.

Parameters Used

When using the GRANT command, there are a few key parts you need to know about:

  • Privilege: This is what you're allowing the user to do. Common privileges include SELECT (to read data), INSERT (to add data), UPDATE (to change data), & DELETE (to remove data).
     
  • Object: This is what the privilege applies to. It could be a whole database, a specific table, or even a column in a table.
     
  • Grantee: This is who you're giving the privilege to. It can be a user or a group of users.
     
  • WITH GRANT OPTION: This is a special addition you can put at the end of a GRANT command. It lets the user you're giving privileges to, in turn, give those privileges to others.
     

For example, if you want to let a user named Jamie add data to a table called Orders, you would use the INSERT privilege on the Orders table for Jamie:

GRANT INSERT ON Orders TO 'Jamie'@'localhost';


This command gives Jamie the power to add new orders to the table, but nothing else.

Privileges

In SQL, privileges are like keys that unlock different doors. They let users do specific things like looking at data, changing it, or even deciding who else can access it. There are many types of privileges, but here are some common ones:

  • SELECT: Lets you read or see the data.
     
  • INSERT: Allows you to add new data.
     
  • UPDATE: You can change existing data.
     
  • DELETE: This lets you remove data.
     
  • CREATE: You can make new tables or databases.
     
  • DROP: This allows you to delete tables or databases.
     
  • EXECUTE: Lets you run stored procedures or functions.
     

For each privilege, you can specify where it applies, like a specific table or the whole database, making sure users have just the right access they need, not more, not less.

For example, if you want someone to only add data to a table but not change or delete it, you might give them the INSERT privilege but not UPDATE or DELETE.

Grant Privileges on Table

When you want to let someone do things with a table, like reading or changing the data, you use the GRANT command to give them the right permissions. Let's say you have a table called "Orders" in your database, and you want a user named Sam to be able to add new orders to the table. You would use a command like this:

GRANT INSERT ON Orders TO 'Sam'@'localhost';


This command tells the database that Sam has the permission to add new data to the Orders table. He won't be able to change existing orders or look at other people's orders unless you give him those permissions too.

If later you decide Sam should also be able to see all the orders, you'd give him the SELECT permission with a similar command:

GRANT SELECT ON Orders TO 'Sam'@'localhost';


Now, Sam can add new orders and look at all the orders in the table, but he still can't change or delete them unless you give him more permissions.

Different Ways of Granting EXECUTE Privileges

Sometimes, in a database, there are special sets of commands called 'procedures' or 'functions' that do specific tasks, like calculating something or updating data in a certain way. The EXECUTE privilege allows a user to run these procedures or functions.

To give someone the ability to run a procedure, you use the GRANT command, similar to how you give other permissions. For example, if there's a procedure named "CalculateDiscounts" and you want a user named Lisa to be able to use it, you'd write:

GRANT EXECUTE ON PROCEDURE CalculateDiscounts TO 'Lisa'@'localhost';


This command means Lisa can now run the "CalculateDiscounts" procedure whenever she needs to.

But what if you want to be more specific and only allow Lisa to execute a function, not all procedures? You'd just change the command a bit, like this:

GRANT EXECUTE ON FUNCTION GetCustomerAge TO 'Lisa'@'localhost';


Now, Lisa can use the "GetCustomerAge" function to find out a customer's age, but she won't have permission to run other procedures or functions unless you grant those separately.

Frequently Asked Questions

Can I take back permissions after giving them?

Yes, you can use the REVOKE command to remove permissions you've previously granted to a user.

What if I grant too many permissions by mistake?

Don't worry, you can always use the REVOKE command to remove any permissions that were granted by mistake.

Can a user with granted privileges give those to someone else?

It depends on how you set it up. If you include the WITH GRANT OPTION in your GRANT command, the user can pass on their privileges to others.

Conclusion

In this article, we walked through the GRANT command in SQL, a key tool for managing who can do what in a database. We started by understanding the basic syntax of the command, which helps set the rules on accessing & modifying data. Then, we explored different types of privileges like reading, adding, or changing data, & how to apply them to tables & procedures. We also touched on how to empower users to execute specific tasks through functions or procedures.

You can refer to our guided paths on the Coding Ninjas. You can check our course to learn more about DSADBMSCompetitive ProgrammingPythonJavaJavaScript, etc. 

Also, check out some of the Guided Paths on topics such as Data Structure and AlgorithmsCompetitive ProgrammingOperating SystemsComputer Networks, DBMSSystem Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.

Live masterclass