How do GRANT, REVOKE and row-level security enforce least privilege in SQL databases?
How do GRANT, REVOKE and row-level security enforce least privilege in SQL databases?
Least privilege means each account can do only what its job needs. SQL does this with GRANT / REVOKE on objects, roles to group privileges, and row-level security (RLS) to restrict which rows a user sees.
CREATE ROLE app_read NOLOGIN;
CREATE ROLE app_write NOLOGIN;
GRANT USAGE ON SCHEMA shop TO app_read, app_write;
GRANT SELECT ON ALL TABLES IN SCHEMA shop TO app_read;
GRANT INSERT, UPDATE, DELETE ON shop.orders TO app_write;
-- future tables get the same privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA shop GRANT SELECT ON TABLES TO app_read;
CREATE ROLE reporting_user LOGIN;
GRANT app_read TO reporting_user;
CREATE ROLE support_role NOLOGIN;
REVOKE DELETE ON shop.orders FROM app_write; -- take it back
GRANT SELECT (id, name) ON shop.customers TO support_role; -- column-level privilege
MySQL 8 has CREATE ROLE, GRANT role TO user and SET DEFAULT ROLE. SQL Server also has DENY, which beats GRANT, and database roles such as db_datareader.
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.