I arrived at work one Monday morning to find an email from our automated job that checks for long transactions in the database:
Transaction for user xxxxxx (sid 678, program SQL Developer) has been open for 44460 seconds
Hmmm… so that would be someone holding a lock in the production system for 12 hours. Thankfully the table where the lock was being held wasn’t frequently subject to DML or this might have caused trouble. I have previously noted how simply having SELECT access allows a user to lock data and that was exactly the scenario that had occurred; a support person had logged into the database, locked a row in a table that they only had SELECT access on without realising what they had done… and then left their session active for a day.
After killing the offending session and sending out a polite email to all support staff requesting that they disconnect from a production database after they have finished whatever needed doing I set about making some changes…
Oracle 18.104.22.168 introduces a new privilege to address this specific problem; READ. According to the documentation READ privilege permits an account to:
Query the table with the SELECT statement. Does not allow SELECT … FOR UPDATE.
Note: This privilege is available starting with Oracle Database 12c Release 1 (22.214.171.124).
Let’s not take Oracle’s word for it; let’s test it using the scenario from the previous post:
CREATE USER data_owner IDENTIFIED BY data_owner DEFAULT TABLESPACE users QUOTA UNLIMITED ON users / CREATE USER data_reader IDENTIFIED BY data_reader / GRANT CREATE SESSION TO data_owner / GRANT CREATE TABLE TO data_owner / GRANT CREATE SESSION TO data_reader / CREATE TABLE data_owner.lock_test (id NUMBER(10)) / INSERT INTO data_owner.lock_test VALUES (1) / COMMIT /
Instead of granting SELECT on the DATA_OWNER.LOCK_TEST table to DATA_READER we grant READ:
GRANT READ ON data_owner.lock_test TO data_reader /
Now to test if we can lock the data in the DATA_OWNER account, we run the following from the DATA_READER account:
DECLARE CURSOR cur IS SELECT * FROM data_owner.lock_test FOR UPDATE; BEGIN OPEN cur; END; /
The above results in:
SQL>DECLARE 2 CURSOR cur 3 IS 4 SELECT * 5 FROM data_owner.lock_test 6 FOR UPDATE; 7 BEGIN 8 OPEN cur; 9 END; 10 / FROM data_owner.lock_test * ERROR at line 5: ORA-06550: line 5, column 22: PL/SQL: ORA-01031: insufficient privileges ORA-06550: line 4, column 4: PL/SQL: SQL Statement ignored
Exactly what we wanted! Nice one Oracle. READ privilege can be applied to tables, views and materialized views.
For me, some simple changes to the roles assigned to the support users and the unwanted locking problem is resolved.