Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureEasy
A global software company maintains customer data across multiple regions using Azure Database for MySQL. They need to create a new database user account that can only view data from a specific table named 'Customers' in the 'Sales' database. The user should not be able to modify, insert, or delete any data. Which SQL command should be used to grant these specific permissions?
- AGRANT ALL PRIVILEGES ON Sales.Customers TO 'newuser'@'%'
- BGRANT SELECT ON Sales.Customers TO 'newuser'@'%'
- CGRANT ALTER ON Sales.Customers TO 'newuser'@'%' WITH GRANT OPTION
- DGRANT INSERT ON Sales.Customers TO 'newuser'@'%'
Show answer & explanationAnswer & explanation
Correct answer: B. GRANT SELECT ON Sales.Customers TO 'newuser'@'%'
The GRANT SELECT statement provides read-only access to the specified table, fulfilling the requirement that the user can only view data and not modify, insert, or delete.
Why the other options are wrong
- A. GRANT ALL PRIVILEGES provides full administrative access, which is too broad and violates the principle of least privilege.
- C. GRANT ALTER provides permission to modify table structure and WITH GRANT OPTION allows passing on permissions, both of which are too broad and violate the least privilege principle.
- D. GRANT INSERT provides permission to add new rows, which is explicitly disallowed by the requirement.
MySQL User Permissions (GRANT SELECT)
The GRANT SELECT statement in MySQL is used to provide read-only access to a specific database, table, or column for a user.
- Provides read-only access.
- Essential for implementing the principle of least privilege.
- Syntax: GRANT SELECT ON database.table TO 'user'@'host'.
Memory trick: GRANT for Giving Rights, REVOKE for Removing.