2

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

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.

4

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

2

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}

  • thank you for pointing this out, i did think about who should be the one creating the table (system user, schema user, etc). I understand one user can query other user's table by links which can be simplified using synonyms, thus there can be a owner user and other users according to the application needs. now you made clear that is is the way to go. thanks – Guilherme Queiroz Vasconcelos Nov 23 '20 at 12:03
  • i will check the documentation, but on step#2 you mention three roles: read only, normal and manager. The first one is self explanatory but what is the main difference of normal user role and manager role? ddl permissions? – Guilherme Queiroz Vasconcelos Nov 23 '20 at 12:06
  • @GuilhermeQueirozVasconcelos - Never ever log in as SYS (unless you have to). DDL Operations are usually done by accounts with the DBA, PDB_DBA, or APPLICATION_DEVELOPER (custom) Role. Applications normally do not perform DDL. The difference between Manager accounts and Normal User accounts is (usually) DML capabilities; not DDL. eg: Manager accounts can DELETE while a Normal User accounts can not. This "separation of duty" becomes more apparent when you apply things like Row Level Security (RLS) - a sub-feature of Virtual Private Database and Real Application Security. – Michael Kutz Nov 23 '20 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.