SQL · Theory

Grant Privilege

Theory

75/439

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.

Exam tip

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

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.

Short notes

DefGRANT gives a user rights on a DB/table.
RuleLeast privilege. 'user'@'host'.
TrapGRANT ALL ON *.* for an app user.

SQL · Theory

Grant Privilege

← All stacks

Theory

75/439

Grant Privilege

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.

Exam tip

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

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.

Short notes

DefGRANT gives a user rights on a DB/table.
RuleLeast privilege. 'user'@'host'.
TrapGRANT ALL ON *.* for an app user.

Grant Privilege

Grant Privilege — sample query

Short notes

Questions

Grant Privilege

Grant Privilege — sample query

Short notes

Questions