Two approaches to database security:
Application level
Users log into applications, which then restrict which functions are available depending on the class of user
Hackers can circumvent this by using an SQL tool that goes directly to the database engine
Database engine level
Enterprise-class DBMS allow tables and other database objects to be secured via a permissions system
Securable database objects (tables, views, stored procedures) are secured with permissions granted to Database Users
To secure a table/view/stored procedure, the DBA uses the GRANT statement to set permissions for that database user on the object
Users can be grouped into database roles.
A role is a security group that one or more users can belong to.
A user can belong to more than one role
GRANT statements used to grant SELECT, INSERT, UPDATE, and/or DELETE access on a securable item (such as a table, view, or stored procedure) to a user account
For fine grained control, grant users permission to execute stored procedures and views only. Do not grant permission to the underlying tables.
Best Practice: Grant permissions to roles, not users. See Table 14-3 (p. 476) in book.
See https://dev.mysql.com/doc/refman/8.0/en/roles.html for MySQL role details
The GRANT statement is used to
give users access to data in tables:
GRANT privileges
ON objects TO users
where
privileges
includes SELECT, INSERT, UPDATE, DELETE, where SELECT and UPDATE can
be column-specific
Examples:
GRANT INSERT,
DELETE ON Employee TO jmarshall
GRANT SELECT ON Customer TO
fjones
GRANT UPDATE(F_NAME, L_NAME) ON Patron TO jwilliams