GRANT & REVOKE (Security)
GRANT and REVOKE control what database users and roles can do - SELECT, INSERT, UPDATE, DELETE, EXECUTE, and more. Proper privilege management is the foundation of database security: principle of least privilege means users get only the permissions they absolutely need.
Privilege System
- GRANT privilege ON object TO role/user
- Privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, EXECUTE, CONNECT, USAGE, CREATE
- REVOKE privilege ON object FROM role/user
- Roles: group users with shared permissions (GRANT role TO user)
- WITH GRANT OPTION: allows the grantee to further grant the privilege
- Principle of least privilege: grant minimum necessary permissions
- Row-Level Security (RLS): column/row level control (PostgreSQL)
GRANT & REVOKE Examples
-- Create a read-only user for reporting
CREATE USER reporter WITH PASSWORD 'securepass123';
GRANT CONNECT ON DATABASE ecommerce_db TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;
-- reporter can only SELECT, not INSERT/UPDATE/DELETE
-- Create a developer role with specific table access
CREATE ROLE app_developer;
GRANT SELECT, INSERT, UPDATE ON users, products, orders TO app_developer;
GRANT DELETE ON orders TO app_developer;
-- No access to payments (sensitive financial data)
-- Assign role to a user
CREATE USER alice_dev WITH PASSWORD 'devpass456';
GRANT app_developer TO alice_dev;
-- Revoke DELETE from the role
REVOKE DELETE ON orders FROM app_developer;
-- Grant execute on a stored procedure only
GRANT EXECUTE ON PROCEDURE process_order(INT, INT, INT, VARCHAR) TO app_developer;
-- Revoke all privileges
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM reporter;
-- MySQL GRANT syntax
GRANT SELECT ON ecommerce_db.* TO 'reporter'@'%' IDENTIFIED BY 'pass';
GRANT SELECT, INSERT, UPDATE ON ecommerce_db.orders TO 'app'@'%';
FLUSH PRIVILEGES;
-- Check grants (PostgreSQL)
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE table_schema = 'public';Quick Quiz
Tip
Tip
Practice GRANT REVOKE Security in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Parameterized queries always.
Common Mistake
Warning
A common mistake with GRANT REVOKE Security is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of GRANT REVOKE Security from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- GRANT and REVOKE control what database users and roles can do - SELECT, INSERT, UPDATE, DELETE, EXECUTE, and more.
- GRANT privilege ON object TO role/user
- Privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, EXECUTE, CONNECT, USAGE, CREATE
- REVOKE privilege ON object FROM role/user