Recently, there was a production incident where a developer locked a critical record in a table while likely playing around SQL*Developer (there was no need for a developer to lock this row anyway) and essentially brought production environment into a stall.

SELECT privilege on a table granted directly, via a role, or via system privilege "SELECT ANY TABLE" seems to grant something that we do not want to happen: SELECT for UPDATE privilege.

As schema1: grant select on schema1.t1 to schema2;
As schema2: select col1 from schema1.t1 for update;

Though schema2 cannot actually do an update (I have verified that via a pl/sql block), schema2 can effectively lock all the rows in the table. I have only replicated the problem

At this point, one can query gv$transaction and gv$locked_objects and verify that the rows are indeed locked.

I suppose that some has run into this situation and has a solution. To either rely on someone not to run the "select for update", monitor for locks from specific user connections and killing those sessions are workarounds. Can you please recommend a proper solution ? Thank you!