Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "ORA-01950 Error on a Sequence"] [Next entry: "What Privileges Can you Grant On PL/SQL?"]

ORA-01950 Error on a Sequence - Error on Primary Key Index

I posted yesterday a blog about an error on a sequence of ORA-01950 on tablespace USERS - ORA-01950 Error on a Sequence. This was attributed to the sequence by me because that's where the error in Oracle was pointing - The * character pointed at the use of sequence.nextval. The fix to the problem was simple; look at the default tablespace of the user (CRM) and see that the default tablespace was USERS and change it to CRM (in my case). This fixed the issue.

Gary via Linkedin and Glen via Twitter pointed out that the issue was my primary key index which did not have a tablespace specified and as such when the sequence was used to insert an ID into a table the primary key index was updated and because that was the first use - the tables were just created then an extent was needed and the ORA-10950 error was of course thrown.

The issue was not the sequence storage but the index storage for the primary key. The fix still worked of course by changing the CRM default tablespace to CRM from USERS. But, a better fix is to actually change the code to assign a tablespace to the primary key index. As i created by primary key index indirectly by specifying a primary key constraint. I needed to add the tablespace clause to the ALTER TABLE. To test this i first put the CRM users default tablespace back to CRM:

pxf$ sqlplus system/oracle1@//192.168.56.78:1523/xepdb1

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 1 10:10:38 2019

Copyright (c) 1982, 2012, Oracle. All rights reserved.


Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL> sho user
USER is "SYSTEM"
SQL> alter user crm default tablespace users;

User altered.

SQL>

Now connect as CRM and remove the code and re-install it to prove that the ORA-01950 error still occurs:

pxf$ sqlplus crm/crm@//192.168.56.78:1523/xepdb1

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 1 10:13:09 2019

Copyright (c) 1982, 2012, Oracle. All rights reserved.


Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL> @lic_delete

Table dropped.


Table dropped.


Table dropped.


Table dropped.


Table dropped.
... {output removed to save space}

Now install the code again and see the error:

SQL> @license

Table created.

... {output removed to save space}

Table altered.


Sequence created.

insert into crm_lic_products (id,product_name) values (crm_lic_products_seq.nextval,'PFCLScan')
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
... {output removed to save space}

So we have the error. Now change the license.sql script and add the tablespace clause for all primary keys - here is an example for the CRM_LIC_PRODUCTS table:

-- ------------------------------------------------------------------
-- products table - product names
-- ------------------------------------------------------------------

create table crm_lic_products (
id number not null,
product_name varchar2(100) not null
)
tablespace crm
/

-- primary key
alter table crm_lic_products add constraint crm_lic_products_pk primary key (id) using index tablespace crm;

create sequence crm_lic_products_seq
start with 1
increment by 1
nocache
nocycle
/

The tablespace is now set to CRM for this constraint which will create a primary key index in the CRM tablespace. So now install that and see if to works:

pxf$ sqlplus crm/crm@//192.168.56.78:1523/xepdb1

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 1 11:09:21 2019

Copyright (c) 1982, 2012, Oracle. All rights reserved.


Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL> @license

Table created.


Table altered.


Sequence created.

c

1 row created.


1 row created.

... {output removed to save space}

So it clearly works now and the issue is not the storage of the sequence but the storage of the result of the sequence i.e. in the primary key index.

The main point for me of the post yesterday and this post today is that we from an Oracle Security point of view can further control data by specifying quotas on tablespaces. This implies - and the performance guys will probably disagree from a monitoring and a lack of performance advantage - that we could create separate tablespaces for some sections of data - business domains - security domains - whatever is needed and then we can also control the access to add or change data through these tablespace and quota controls as well.