Oracle allows inserting into table even after revoking quota

Question

I am studying creating users and granting roles and I have come across this scenario where I grant unlimited quota to a user, insert into the table and then revoke the quota. I expected that after setting quota to 0, the user would not be able to insert into the table. However, the user is still able to insert. I wonder if this is expected, if there is another command to really prevent the user from inserting again. I am using Oracle 19c and user's current priviledges are create session and create table.

sqlplus system\oracle

SQL> CREATE TABLESPACE libgeneral datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/libgeneral.dbf' SIZE 20M AUTOEXTEND ON NEXT 40M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

sqlplus library\libuser

SQL> create table t1 ( idx number ) tablespace libgeneral;

Table created.

SQL> insert into t1 values (1);
insert into t1 values (1)
            *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'LIBGENERAL'

sqlplus system\oracle

SQL> alter user library quota unlimited on libgeneral;

User altered.

sqlplus library\libuser

SQL> insert into t1 values (1);

1 row created.

sqlplus system\oracle

SQL> alter user library quota 0 on libgeneral;

User altered.

sqlplus library\libuser

SQL> insert into t1 values (1);

1 row created.

Answer 1

When you inserted the first row in the empty table, Oracle allocated an extent, which typically contains 8 blocks with default settings for the first extent of a table. 8 blocks (typical size of single block is 8 KB) can store a lot more than 1 row, so even though you revoked the quota, that space is already allocated, and you can write into it, which is expected behavior.

Try inserting a lot more rows and eventually you will get the same error message about the quota.

Answer 2

'quota' specifies how much space may be used of a given tablespace. Space is allocated in extents - a logically contiguous series of blocks. When you create a table, you get one extent right off the bat. When you start inserting, you are using space that was previously allocated for the extent. Only when that extent is full and more space is needed will oracle attempt to allocate another extent, and only then will 'quota' come back into play.

Note: I think it was with 12c, perhaps with 11g, that the concept of 'deferred' allocation came into play. With that, a table is created with zero extents. At that point the table is really nothing but an entry in the data dictionary. The first (initial) extent is then not allocated at table creation, but when the first INSERT issued, thus requiring actual space allocated to the table.

Bottom line, your user is still able to insert rows because those rows are using space already acquired for the extent.

Answer 3

if there is another command to really prevent the user from inserting again.

The others have discussed why you are seeing what you are seeing. I'm focusing on this question.

First

The owner of a table will always have full control over that table.

This can be a security risk.

To prevent that, applications should never connect as the owner of the table.

You should start your practice of ROLES and GRANTS at this stage.

Steps to try

1. create a data owner schema (all data goes here)
2. Create ROLE based on purpose
   • READ ONLY role
   • normal user role
   • manager role
3. Create users for those roles
4. Assign the ROLE to the appropriate user.

I believe the Oracle Documentation goes through this in an example. {Link TBD}