Revoke Privilege — SQL Technical Interview Questions & Answers

Revoke Privilege

Revoke Privilege

  • REVOKE INSERT ON college.* FROM 'asha'@'localhost'; takes that right away. SHOW GRANTS FOR 'asha'@'localhost'; to see what they still have.
  • REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'asha'@'localhost'; is the blunt form before DROP USER.
  • Trap: REVOKE on the wrong host ('asha'@'%') while they still connect as localhost.
  • On the example next to this theory: Revoke Privilege: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: REVOKE INSERT ON college.* FROM 'asha'@'localhost';

Code Examples for Revoke Privilege

Revoke Privilege — sample query

-- Revoke 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;

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

How to Explain Revoke Privilege to an Interviewer

How do you take INSERT away?

REVOKE INSERT ON db.* FROM 'user'@'host';