Confidentiality protects information from unauthorized access and disclosure. In a database, it means allowing only authorized people and processes to access the data they should control. section 7.4
Use two complementary approaches:
- Access control decides who may view, modify, or delete data. section 7.4
- Mandatory access control (MAC) assigns sensitivity classifications to data and grants access only when a user’s clearance matches; administrators configure the classifications manually. section 7.4
- Role-based access control (RBAC) assigns permissions to roles, then assigns users to those roles instead of managing every user individually. section 7.4
- Encryption provides a second confidentiality approach alongside access control. section 7.4
Applying RBAC to database tables
For a small commerce database, create roles that match real responsibilities. A standard user can read products and insert purchases, purchase-product records, and reviews. An analyst can read those tables. An administrator can select, insert, update, and delete records. Create each role, grant its permissions, and assign it to a database user. This keeps permissions grouped by job rather than duplicated across individual accounts. 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'@'%';
View the standard-user SQL pattern for creating a role, granting table permissions, and assigning the role to a user. 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 analyst; -- step 2: grant permissions to a role GRANT SELECT ON database_name.product TO analyst; GRANT SELECT ON database_name.purchase TO analyst; GRANT SELECT ON database_name.purchase_product TO analyst; GRANT SELECT ON database_name.review TO analyst; -- step 3: assign the role to a new user CREATE USER 'smith'@'%' IDENTIFIED BY 'password'; GRANT analyst TO 'smith'@'%'; -- step 4: enable role by default (MySQL 8.0+) ALTER USER 'smith'@'%' DEFAULT ROLE analyst;
View the analyst SQL pattern, which grants read access across the sample tables and enables the role by default for MySQL 8.0+. 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 admin; -- step 2: grant permissions to a role GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.product TO admin; GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.purchase TO admin; GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.purchase_product TO admin; GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.review TO admin; -- step 3: assign the role to a new user CREATE USER 'david'@'%' IDENTIFIED BY 'password'; GRANT admin TO 'david'@'%'; -- step 4: enable role by default (MySQL 8.0+) ALTER USER 'david'@'%' DEFAULT ROLE admin;
View the administrator SQL pattern, which grants select, insert, update, and delete permissions across the sample tables. section 7.4
