SOL-C01 Question #55: Real Exam Question with Answer & Explanation

Question

A data engineer needs to create a new database named `SALES DATA for storing sales transactions. They want to ensure that only users with the 'DB_ADMIN' role can manage the database. After creating the database, the engineer needs to grant the `SELECT privilege on all tables within the `SALES DATA.PUBLIC' schema to a role named 'ANALYST'. Which of the following is the MOST secure and efficient sequence of SQL commands to accomplish this?

Options

• ACREATE DATABASE SALES DATA; GRANT OWNERSHIP ON DATABASE SALES DATA TO
• BCREATE DATABASE SALES DATA; GRANT OWNERSHIP ON DATABASE SALES DATA TO
• CCREATE DATABASE SALES DATA; GRANT OWNERSHIP ON DATABASE SALES DATA TO
• DCREATE DATABASE SALES DATA; USE DATABASE SALES DATA; GRANT OWNERSHIP ON
• ECREATE DATABASE SALES DATA; GRANT OWNERSHIP ON DATABASE SALES DATA TO

Explanation

Option B is the most secure and efficient. Granting OWNERSHIP on the database ensures DB_ADMIN can manage it. Using 'GRANT SELECT ON FUTURE TABLES' ensures that any new tables created in the `PUBLIC' schema will automatically have the 'SELECT privilege granted to the ANALYST' role. This eliminates the need to manually grant privileges each time a new table is created. Option A only grants access to existing tables. Options C and E are inefficient because they attempt to grant access to the schema itself, which is not the correct approach for controlling access to the tables within it. Option D requires using the database explicitly. Option A and E does not have future grants

No community discussion yet for this question.