SQL · Theory

Revoke Privilege

Theory

76/439

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.

Exam tip

REVOKE INSERT ON college.* FROM 'asha'@'localhost';

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.

Short notes

DefREVOKE removes a GRANT.
RuleSame user@host as GRANT. SHOW GRANTS to check.
TrapWrong host in the account name.

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

Revoke Privilege — sample query

Short notes

Questions

Revoke Privilege

Revoke Privilege — sample query

Short notes

Questions