More on each of these products separately soon.
I do tend to post more to Facebook, Twitter and LinkedIn. If you are not already connected / following me on these platforms then please do send a request and I will be happy to connect to you.
OK, for this post I want to talk about sequences and an ORA-01950 error. I was creating a new sample application for use in demos of our products and also in training. This application uses an Oracle XE 18c database as the data store and I have defined a set of tables, sequences, views and some static data. I then use my tools bog_gen_all.sql and this can be run against the schema objects based on a naming convention; i.e. all tables start with CRM_LIC%. This script can then generate a complete php website that can be used to operate on the data held in the database. I use an Apache webserver, PHP and OCI8 to connect from the php to the database. This tool to generate the website is in this post "BOF: A Sample Application For Testing Oracle Security".
So, whilst creating the tables, sequences, views and some static data i ran into an issue. I have a schema called CRM who will own the tables, views, sequences and I also created a table space also called CRM that will hold this data. One example table / sequence definition is here:
...
-- ------------------------------------------------------------------
-- 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);
create sequence crm_lic_products_seq
start with 1
increment by 1
nocache
nocycle
/
...
As you can see I have defined a table and sequence and also later on in the script I insert some sample static data also using the sequence:
-- products
insert into crm_lic_products (id,product_name) values (crm_lic_products_seq.nextval,'PFCLScan');
...
As you can see the table uses the tablespace CRM and I can connect to the CRM user and run the full install script. A section of the output with the relevant error is shown here:
SQL> @license
Table created.
Table altered.
Sequence created.
...{output removed to save space}
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}
Hmm, so I am connected as CRM; the table is in the CRM tablespace and i am inserting data into CRMs table so why do I get an error that I don't have permissions on the USERS tablespace?. The error also indicates that the issue is the sequence as the * points at the start of the com_lic_products_seq.nextval. This would imply that the sequence is stored in the USERS tablespace. First, why USERS; this part is easy; because the CRM user still has a DEFAULT TABLESPACE of USERS:
pxf$ sqlplus system/oracle1@//192.168.56.78:1523/xepdb1
SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 27 16:27:50 2019
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
SQL> select username,default_tablespace from dba_users where username='CRM';
USERNAME
--------------------------------------------------------------------------------
DEFAULT_TABLESPACE
------------------------------
CRM
USERS
SQL>
If we change the DEFAULT TABLESPACE to CRM for the CRM user:
pxf$ sqlplus system/oracle1@//192.168.56.78:1523/xepdb1
SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 27 14:27:44 2019
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
SQL> alter user crm default tablespace crm;
User altered.
SQL>
And then connect as CRM and run the install script again its fixed:
pxf$ sqlplus crm/crm@//192.168.56.78:1523/xepdb1
SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 27 14:28:19 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 "CRM"
SQL> @license
Table created.
Table altered.
...{output removed to save space}
1 row created.
1 row created.
...{output removed to save space}
So its fixed; the default tablespace on the user CRM of USERS was causing the ORA-01950 error on the use of the crm_lic_products_seq.nextval and now it does not fail when the CRM database user has a default tablespace of CRM. BUT, none of this makes sense. I dug a little deeper and checked out sequences storage in the root container:
pxf$ sqlplus system/oracle1@//192.168.56.78:1523/xe
SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 27 14:24:40 2019
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
SQL> set long 1000000
SQL> select text from dba_views where view_name='DBA_SEQUENCES';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name,
s.minvalue, s.maxvalue, s.increment$,
decode (s.cycle#, 0, 'N', 1, 'Y'),
decode (s.order$, 0, 'N', 1, 'Y'),
s.cache, s.highwater,
decode(bitand(s.flags, 16), 16, 'Y', 'N'),
decode(bitand(s.flags, 2048), 2048, 'Y', 'N'),
decode(bitand(s.flags, 64), 64, 'Y', 'N'),
decode(bitand(s.flags, 512), 512, 'Y', 'N')
from sys.seq$ s, sys.obj$ o, sys.user$ u
where u.user# = o.owner#
and o.obj# = s.obj#
and (bitand(s.flags, 1024) = 0 or s.flags is null)
SQL>
And a check of the actual sequences values:
SQL> set serveroutput on
SQL> @print 'select * from all_sequences'
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from all_sequences','''','''''');
Executing Query [select * from all_sequences]
...{output removed to save space}
-------------------------------------------
SEQUENCE_OWNER : CRM
SEQUENCE_NAME : CRM_NOTES_SEQ
MIN_VALUE : 1
MAX_VALUE : 9999999999999999999999999999
INCREMENT_BY : 1
CYCLE_FLAG : N
ORDER_FLAG : N
CACHE_SIZE : 0
LAST_NUMBER : 2
SCALE_FLAG : N
EXTEND_FLAG : N
SESSION_FLAG : N
KEEP_VALUE : N
-------------------------------------------
...{output removed to save space}
And a check of the sequences tables storage:
SQL> select table_name,tablespace_name from dba_tables where table_name in ('SEQ$','OBJ$','USER$');
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
USER$
SYSTEM
OBJ$
SYSTEM
SEQ$
SYSTEM
SQL>
This shows that sequences are stored in the SYSTEM tablespace and NOT the database users default tablespace which was either USERS or CRM in my example case.
I also ran a 10046 trace at level 12 to get SQL and binds but there is nothing obvious as to how the tablespace USERS or indeed CRM after the fix are implicated in the use of sequence.nextval. I have been through the trace and there are no tables updated or inserted into that are in either of these two tablespaces. In terms of a sequence my understanding of how it works is that Oracle takes a lump of sequences numbers - defaulted to 20 and stores these in RAM and they are used until they need to be refreshed or written back to SEQ$. This should not involve the database users default tablespace as its not stored in a table that has a tablespace of USERS or CRM in my case and the sequences are in the SYSTEM tablespace in the SEQ$ table.
Tablespaces and quotas have an effect on security as do it seems sequences. If you do not have a quota on a tablespace you cannot get a new extent on a tablespace when its needed to be used or store data. Whats confusing is that somehow the sequence is stored or at least uses the default tablespace of the user (or schema) - it depends. My example was simpler as i connected as the schema and i didn't use any PL/SQL to access the table or data.
Oracle permission are complex and not obvious sometimes; but we can use quotas to our advantage by not only having grants to allow creation of objects such as code or tables but also we can use quotas to control who can actually write data.
