Grant Privilege — SQL Technical Interview Questions & Answers

Grant Privilege

Grant Privilege

  • GRANT SELECT, INSERT ON college.* TO 'asha'@'localhost'; lets asha read and insert in that database. FLUSH PRIVILEGES is needed only for some older grant-table edits — GRANT statement usually applies at once.
  • Least privilege: the app user does not get DROP or GRANT OPTION. Host 'localhost' vs '%' matters.
  • Trap: GRANT ALL ON *.* TO an app. Trap: forgetting the quotes around 'user'@'host'.
  • On the example next to this theory: Grant Privilege: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: GRANT SELECT, INSERT ON college.* TO 'asha'@'localhost';

Code Examples for Grant Privilege

Grant Privilege — sample query

-- Grant Privilege
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Grant Privilege: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain Grant Privilege to an Interviewer

Give a student user read-only?

GRANT SELECT ON college.* TO 'asha'@'localhost';