CompTIA DataSys+ (DS0-001)Database FundamentalsMedium
A database administrator needs to create a new database user account named 'report_reader' that can only execute `SELECT` statements on tables within the 'sales' schema. Which SQL DDL statement sequence correctly grants these specific permissions?
- ACREATE USER report_reader; GRANT EXECUTE ON SCHEMA sales TO report_reader;
- BCREATE USER report_reader; GRANT ALL PRIVILEGES ON SCHEMA sales TO report_reader;
- CCREATE USER report_reader; GRANT INSERT, UPDATE ON SCHEMA sales TO report_reader;
- DCREATE USER report_reader; GRANT SELECT ON ALL TABLES IN SCHEMA sales TO report_reader;
Show answer & explanationAnswer & explanation
Correct answer: D. CREATE USER report_reader; GRANT SELECT ON ALL TABLES IN SCHEMA sales TO report_reader;
The `GRANT SELECT ON ALL TABLES IN SCHEMA sales TO report_reader;` statement specifically grants the `SELECT` privilege on all current and future tables within the 'sales' schema to the 'report_reader' user, fulfilling the requirement for read-only access to that schema.
Why the other options are wrong
- A. `EXECUTE` is typically for functions or procedures, not for table data access.
- B. `GRANT ALL PRIVILEGES` is too broad, giving more permissions than requested.
- C. `INSERT, UPDATE` grants write permissions, which is not what was requested (read-only).
SQL GRANT Statement
A SQL DDL command used to assign specific database privileges to users or roles.
- Controls access to database objects (tables, views, functions).
- Syntax: `GRANT privilege_type ON object_name TO user_or_role;`
- Common privileges include SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALL PRIVILEGES.
Memory trick: GRANT 'G'ives 'R'ights 'A'nd 'N'o 'T'rouble.