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:

1. user B's transaction fail midway with an automatic rollback performed on Table1 (I'm assuming this is an example of an "abnormally terminated" process -- one of the conditions for a rollback; is it?)? If yes, how is data integrity handled? For example, does Oracle guarantee that the insertions won't be halted in the middle of a row?
2. user B's transaction complete before his privileges are taken away (meaning he can't make any more insertions after this)?

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:

1. The for loop falls through as soon as the revoke takes effect. So, as Dave said, privileges are checked each time a statement is executed.
2. For the insert part of the test, once the for loop fails midway, none of the insertions (not even the ones for which the for went through are visible in the grantee's session).

Answer 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.

Answer 2

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.