laika222
11/7/2016 - 7:46 PM

How to GRANT and REVOKE privileges from users. Line 21 shows GRANT REFERENCES allows a user to reference another table. For instance, if a u

How to GRANT and REVOKE privileges from users. Line 21 shows GRANT REFERENCES allows a user to reference another table. For instance, if a user doesn't have access to the customers1 table, but has access to another table that has a foreign key that references a primary key in customers1, the user can INSERT into the second table, and if the foreign key is rejected, that means it doesn't exist in customers1. Therefore, the user can discern information from customers1 without actually having access to customers1. Without REFERENCES, the user can't discern this info. Line 25 shows how to GRANT USAGE, which allows a user to use a domain when creating a table. If a user has access to use the domain, the user can discern the boundary limits of the domain but INSERTing values that keep getting higher or lower until they're rejected. Lines 28-36 show a good way to grant privileges to a user for all columns except one.

GRANT AND REVOKE MS.mysql
content_copyfile_download
GRANT SELECT
ON products1
TO Kirk;

GRANT UPDATE (Price)
ON products1
TO Kirk;

GRANT INSERT
ON products1
TO Kirk;

REVOKE UPDATE (Price)
ON products1
FROM Kirk;

GRANT DELETE
ON products1
TO Kirk;

GRANT REFERENCES
ON customers1
TO Kirk;

GRANT USAGE ON DOMAIN PriceType
TO Kirk;

-- 
GRANT INSERT, DELETE, UPDATE
ON customers1
TO Kirk;

REVOKE UPDATE
ON customers1 (CustomerID)
FROM Kirk;
--