Grant Command in SQL

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 DSA, DBMS, Competitive Programming, Python, Java, JavaScript, etc. 

Also, check out some of the Guided Paths on topics such as Data Structure and Algorithms, Competitive Programming, Operating Systems, Computer Networks, DBMS, System Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.