Does revoking privileges in Oracle affect ongoing transaction that make use of those privileges?

Question

I'm curious about this. What happens if the DBA (or a grantor) revokes a user's (grantee's) privileges while that user (grantee) has an ongoing transaction that needs those privileges (obviously, when the user (grantee) issued his transaction, he had those privileges).

A trivial scenario to make this more concrete: user A grants user B privileges to insert data into a table (say Table1) in his (user A's) schema. User B goes ahead and issues a transaction that makes a lot of insertions. While the insertions are going on, user A revokes B's insert privileges. Does:

conditions for a rollback

Logically, I'd guess it'd be 2 but I can't find any information to confirm this in Oracle docs.

Thanks.

EDIT: Adding some PL/SQL code I wrote to test this (I just cobbled this together for testing this scenario. I don't know PL/SQL so please correct me if I'm wrong).

/* Start by testing select privileges */
set serveroutput on;
declare v_emp HR.tempemp%rowtype;
begin
  dbms_output.enable(300000);
  for count_var in 1..200000 loop -- loop a large number of times, and BEFORE the loop exits,
                                  -- revoke the permissions manually from the grantor's session
    select * into v_emp
    from HR.tempemp
    where employee_id = 100;
    dbms_output.put_line(count_var||' '||v_emp.employee_id); -- print count_var so we know which iteration we're in
  end loop;
end;

/* Now test insert privileges */
set serveroutput on;
begin
  dbms_output.enable(300000);
  for count_var in 1..20000000 loop -- loop a large number of times, and BEFORE the loop exits,
                                    -- revoke the permissions manually from the grantor's session
    insert into HR.tempemp
    values(100);
    dbms_output.put_line(count_var); -- print count_var so we know which iteration we're in
  end loop;
end;

Observations:

for

none

not even the ones for which the

for

went through

You talk about transactions, but in your comment on Erkan's answer it sounds like you mean statements. — Dave Costa, Apr 06 '11 at 12:21

score 1 · Answer 1 · answered Apr 06 '11 at 11:16

1

I just tested this with the following scenario, here is what happened:

1-User A creates a table.

2-User A grants user B to insert.

3-User B inserts a row, but does not COMMIT.

4-User A revokes from user B.

5-User B inserts a row, but fails.

5-User B commits successfully.

answered Apr 06 '11 at 11:16

Erkan Haspulat

12,032
6
39
45

yeah this one's okay. I'm wondering about what happens with inserts going on _when_ the revoke takes effect. A single insert will happen just too fast to realize this condition. Also, what about statements that don't need commits -- like a select statement: will a select statement in process at the time the revoke takes effect be terminated halfway through? – ars-longa-vita-brevis Apr 06 '11 at 11:27
A DDL statement puts a lock on the entire table, so a revoke from User A needs to wait for the INSERT to complete in order to place the lock on the table. And about the select statement, a select statement will not wait for anything, it will just look at a certain state of the database-in our case, this state is even before the insert statement. – Erkan Haspulat Apr 06 '11 at 13:48
1

how about just a select statement (no insert statement before or after). I wrote some PL/SQL test code today with a long-running `for` loop that selects a row each time it goes through the loop (no inserts involved). What I noticed is that when the revoke is issued from the grantor's session _while_ the `for` is running, the loop fails as soon as it 'sees' the revoke. As Dave says below, the privilege is checked every time a statement is executed. I'll add my code to the OP so you can check it and tell me if I'm doing anything wrong. – ars-longa-vita-brevis Apr 07 '11 at 10:25
actually, based on my test, a REVOKE does not wait for an INSERT to complete. I don't see any reason a REVOKE would need to lock the table -- it's not actually modifying the table object in any way, just information in the data dictionary. – Dave Costa Apr 07 '11 at 12:29

score 1 · Answer 2 · answered Apr 06 '11 at 13:19

This is pretty easy to test: Create empty table in schema A. Grant insert privilege to schema B. In schema B, start a long-running INSERT statement. While it's running, revoke the insert privilege.

If you do this, you will see that the insert continues running and completes successfully. Then, if you immediately try to execute it again you will get ORA-01031: insufficient privileges. So it seems clear that Oracle checks the privileges once for each statement execution. I glanced through some documentation and didn't see anything that stated this outright, but it seems like the most logical approach and the experiment supports it.

You asked:

"does Oracle guarantee that the insertions won't be halted in the middle of a row?"

As shown above, this isn't really relevant in the case of revocation of privilege; but it seems worth explaining more generally how Oracle behaves if an error occurs in the middle of processing a statement. There is no possibility, excluding bugs in Oracle, that a partial row would be inserted and left behind when an error occurred. If any error happens in the middle of processing a single SQL statement, then the changes made so far by that statement (not transaction) are rolled back internally by Oracle. For instance, if you are inserting many rows and the data segment needs to be extended but has no space available, the work done so far by the current statement would be rolled back and then an error would be returned to the code that executed that statement. This is not an "abnormally terminated process" as discussed in the other thread you referenced; the process continues running and determines how to deal with the error -- it has the option to rollback the entire transaction but it is not obliged to do so.

can I ask what code you used to make the "long running" insert? I've just today written some PL/SQL test code with a long-running `for` loop that inserts a row each time it goes through the loop. When the revoke is issued from the grantor's session, the `for` loop falls through. What's different from your "experiment" is that _none_ of the inserts (not even the ones for which the `for` went through) can be seen. I'll add my code to the OP so you can check it and tell me if I'm doing anything wrong. — ars-longa-vita-brevis, Apr 07 '11 at 10:29
I've been re-reading your answer and I think my code is precisely an example of what you meant by: "If any error happens in the middle of processing a single SQL statement, then the changes made so far by that statement (not transaction) are rolled back internally by Oracle." Is that so? Now I'm even more curious to see your long-running insert code! :) — ars-longa-vita-brevis, Apr 07 '11 at 10:59
I was doing a single statement, basically `INSERT INTO empty_table SELECT rownum FROM really_big_table`, that took 5-10 minutes to run (I had an index on the column being inserted into which may have significantly slowed down the inserts.) — Dave Costa, Apr 07 '11 at 12:40
In your test, it appears that the statement-level rollback is occuring at the level of the PL/SQL block. If you were to write out those inserts as independent statements and execute them one-by-one, the ones that successfully complete would not be automatically rolled back when a later one encountered an error (again, unless your SQLPlus or other client is configured to automatically roll back a transaction on an error). — Dave Costa, Apr 07 '11 at 12:45
agreed. This scenario is what Erkan was describing, I think. By the way, is the 'rolling-back statements at the PL/SQL block level' thing an implementation-specific behavior? — ars-longa-vita-brevis, Apr 10 '11 at 13:15

Does revoking privileges in Oracle affect ongoing transaction that make use of those privileges?

2 Answers2