Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureHard
A company uses Azure Database for MySQL to store customer data. They need to create a new database user account for a reporting application. This application should only be able to read data from the `customer_orders` table and nothing else. Which SQL command sequence should the database administrator use to achieve this?
- ACREATE USER 'reporter'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'reporter'@'%';
- BCREATE USER 'reporter'@'%' IDENTIFIED BY 'password'; REVOKE ALL PRIVILEGES ON *.* FROM 'reporter'@'%'; GRANT SELECT ON customer_data.customer_orders TO 'reporter'@'%';
- CCREATE USER 'reporter'@'%' IDENTIFIED BY 'password'; GRANT SELECT ON customer_data.customer_orders TO 'reporter'@'%';
- DCREATE USER 'reporter'@'%' IDENTIFIED BY 'password'; GRANT READ ON customer_data.customer_orders TO 'reporter'@'%';
Show answer & explanationAnswer & explanation
Correct answer: C. CREATE USER 'reporter'@'%' IDENTIFIED BY 'password'; GRANT SELECT ON customer_data.customer_orders TO 'reporter'@'%';
The most secure and correct way to grant specific read-only access to a single table is to first create the user, and then explicitly grant only the SELECT privilege on that specific table. Granting ALL PRIVILEGES (A) is too permissive. REVOKE ALL (C) is unnecessary if no privileges have been granted yet. 'READ' (D) is not a standard MySQL privilege for tables.
Why the other options are wrong
- A. This option grants all privileges on all databases and tables, which is a significant security risk and does not meet the 'only be able to read data from the `customer_orders` table and nothing else' requirement.
- B. While technically functional if 'reporter' had existing privileges, the REVOKE ALL is redundant if the user is newly created and has no inherited permissions. The primary goal is to grant minimal necessary permissions, which 'B' does more directly.
- D. MySQL does not have a `READ` privilege for tables. The correct privilege for read-only access is `SELECT`.
MySQL User Permissions (GRANT SELECT)
The `GRANT SELECT` statement in MySQL is used to provide read-only access to a database, table, or specific columns for a user, ensuring the principle of least privilege.
- SELECT privilege allows retrieving data.
- Can be granted at global, database, table, or column level.
- Essential for implementing the principle of least privilege.
- Syntax: `GRANT SELECT ON database_name.table_name TO 'user'@'host';`
Memory trick: Grant SELECT to see, but nothing more.