Table HR.EMPLOYEES contains a row where the EMPLOYEE_ID is 109.
User ALICE has no privileges to access HR.EMPLOYEES.
User ALICE starts a session.
User HR starts a session and successfully executes these statements:
GRANT DELETE ON employees TO alice;
UPDATE employees SET salary = 24000 WHERE employee_id = 109;
In her existing session ALICE then executes:
DELETE FROM hr.employees WHERE employee_id = 109;
What is the result?
User ALICE has been granted DELETE privileges on the HR.EMPLOYEES table, but to delete a specific row, she also needs the SELECT privilege to identify the row to delete. Without the SELECT privilege, the DELETE command will immediately return an error as ALICE cannot access the necessary data to complete the operation.
I'm seeing a lot of debate here about the security, but what about data lock? nothing indicates that the HR committed the update, thus; shouldn't the row be locked?
D is correct. ALICE needs SELECT privileges also
It depends on parameter. Both C and D might be correct. I would vote D because i think it's the default setting on Oracle 19. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/SQL92_SECURITY.html#GUID-E41087C2-250E-4201-908B-79E659B22A4B
C gives ORA-41900
If you are finding C works, check to see if 'Alice' has the select any table privilege. That was an issue for me.
This depends on SQL92_SECURITY parameter and the default varies with database version. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/SQL92_SECURITY.html#GUID-E41087C2-250E-4201-908B-79E659B22A4B
Tested, C is correct, i updated row as sys user and deleted as test user, it was stuck in scrip runner, as soon as I commited in sys session delete did happen in test user session
https://www.examtopics.com/discussions/oracle/view/22160-exam-1z0-071-topic-2-question-65-discussion/
But in the link that you provide, half of them choose C as correct answer