Access control protects information by deciding who may view, modify, or delete it. In a database, it gives authorized people and processes only the authority they need, helping preserve confidentiality. section 7.4
Choose permissions using one of two main approaches:
- Mandatory access control (MAC) applies centrally managed, rigid rules. Data receives classifications such as sensitive or confidential, and users need matching clearance; exceptions are not allowed. section 7.4
- Role-based access control (RBAC) assigns permissions to roles rather than individual users. Users then receive roles such as standard user, analyst, or administrator. section 7.4
RBAC makes database permissions easier to manage. For a sample retail database, a standard user might read products and add purchases or reviews; an analyst might read several tables; an administrator might select, insert, update, and delete across them. The practical sequence is simple: create a role, grant table permissions to it, create a user, and assign the role. MySQL 8.0+ can also enable the assigned role by default. section 7.4
-- comment: works for MySQL and MariaDB -- comment: consult ChatGPT on adaptations -- comment: for other RDBMS -- step 1: create a role CREATE ROLE standard_user; -- step 2: grant permissions to a role GRANT SELECT ON database_name.product TO standard_user; GRANT INSERT ON database_name.purchase TO standard_user; GRANT INSERT ON database_name.purchase_product TO standard_user; GRANT INSERT ON database_name.review TO standard_user; -- step 3: assign the role to a new user CREATE USER 'morpheus'@'%' IDENTIFIED BY 'password'; GRANT standard_user TO 'morpheus'@'%';
Follow the role-based setup as executable SQL: create a standard_user role, grant table-specific permissions, create a user, and assign the role. The listing targets MySQL and MariaDB and notes that other relational database systems may require adaptations. section 7.4
