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.

PL/SQL That is not DEFINER or INVOKER rights - BUG?

I always understood that PL/SQL objects in the database that are not explicitly changed to INVOKER rights - CURRENT_USER or DEFINER. During testing of an issue with PFCLScan where a customer was using our project template to analyse PL/SQL and it failed we tried to replicate the same issue here. The issue turned out that we are listing lots of details around PL/SQL in one of our checks in one policy BUT some PL/SQL in the database had a NULL AUTHID; i.e. it is not DEFINER or INVOKER. So this caused an error as our checks code expected a value for each piece of PL/SQL or DEFINER or CURRENT_USER.

So whilst the fix to our policy and check was easy, NVL the AUTHID column to NULL and then the PFCLScan project runs correctly it lead me to investigate this. How many pieces of PL/SQL have a NULL AUTHID? and why?

I was testing in 11g as that is where we had some sample PL/SQL code that I could look at. So I did a quick check for the count of AUTHID:

SQL> select count(*),authid from dba_procedures group by authid;

COUNT(*) AUTHID
---------- ------------
10884 CURRENT_USER
53
17765 DEFINER

So 53 objects in my database have a NULL AUTHID. So what do they have in common. So it looks like they are all PACKAGE. We can get the details from DBA_PROCEDURES

SQL> select owner,object_name, procedure_name,object_type,authid from dba_procedures where authid is null order by owner;

OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------- ------------
APEX_030200 WWV_FLOW_TRANSLATION_UTILITIES PACKAGE
APEX_030200 WWV_FLOW_THEME_GLOBALS PACKAGE
APEX_030200 WWV_FLOW_FND_GLOBAL PACKAGE
APEX_030200 WWV_FLOW_GLOBAL PACKAGE
APEX_030200 WWV_FLOW_TREE_GLOBAL_VARS PACKAGE
APEX_030200 WWV_FLOW_FND_USER_PW_PREF PACKAGE
APEX_030200 WWV_FLOW_IMAGE_PREFIX PACKAGE
APEX_030200 WWV_FLOW_FILE_OBJECT_ID PACKAGE
CORE CNST PACKAGE
CORE TYP PACKAGE
CTXSYS DR_DEF PACKAGE

OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------- ------------
CTXSYS DRILIST PACKAGE
CTXSYS DRIG PACKAGE
CTXSYS DRIOBJ PACKAGE
CTXSYS CTX_ULEXER PACKAGE
MDSYS PRVTPC PACKAGE
MDSYS SDO_VERS PACKAGE
MDSYS SDOCURPKG PACKAGE
OLAPSYS CWM$EXCEPTIONS PACKAGE
OLAPSYS CWM2_OLAP_EXCEPTIONS PACKAGE
ORDSYS ORDIMAGEEXCEPTIONS PACKAGE
ORDSYS ORDDOCEXCEPTIONS PACKAGE

OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------- ------------
ORDSYS ORDIMAGESIEXCEPTIONS PACKAGE
ORDSYS ORDIMAGECONSTANTS PACKAGE
ORDSYS ORDVIDEOEXCEPTIONS PACKAGE
ORDSYS ORDAUDIOEXCEPTIONS PACKAGE
ORDSYS ORDSOURCEEXCEPTIONS PACKAGE
SYS DM_CL_CUR PACKAGE
SYS DM_GLM_CUR PACKAGE
SYS DM_NMF_CUR PACKAGE
SYS PBUTL PACKAGE
SYS DBMS_FILE_GROUP_DECL PACKAGE
SYS LCR$_XML_SCHEMA PACKAGE

OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------- ------------
SYS DM_SVM_CUR PACKAGE
SYS KUPCC PACKAGE
SYS DBMS_TYPES PACKAGE
SYS BLAST_CUR PACKAGE
SYS DM_MODB_CUR PACKAGE
SYS GENDATATYPEIDCONSTANTS PACKAGE
SYS DBMS_DB_VERSION PACKAGE
SYS DBMS_CRYPTO_TOOLKIT_TYPES PACKAGE
SYS OWA_CX PACKAGE
SYS DBMS_STREAMS_ADM_UTL_INT PACKAGE
SYS DBMS_LCR PACKAGE

OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------- ------------
SYS WWV_FLOW_KEY PACKAGE
SYS GENDATABASEINTERFACE PACKAGE
SYS DBMS_STREAMS_DECL PACKAGE
SYS GENMDMPROPERTYIDCONSTANTS PACKAGE
SYS GENMDMOBJECTIDCONSTANTS PACKAGE
SYS ODCICONST PACKAGE
SYS DBMS_REGISTRY_SERVER PACKAGE
SYS UTL_IDENT PACKAGE
SYS GENMDMCLASSCONSTANTS PACKAGE

53 rows selected.

SQL>

OK, we are getting somewhere. What if we look to see if all of these are PACKAGE without a BODY. I created a simple script to do this:

SQL> get p.sql
1 set lines 220
2 col owner for a30
3 col object_name for a30
4 col object_type for a30
5 col authid for a13
6 select p.owner,p.object_name,p.object_type,p.authid
7 from dba_procedures p,
8 (select owner,object_name from dba_objects where object_type='PACKAGE'
9 minus
10 select owner,object_name from dba_objects where object_type='PACKAGE BODY') o
11 where p.owner=o.owner
12 and p.object_name=o.object_name
13 and ((p.subprogram_id=0 and p.object_type='PACKAGE')
14* or (p.subprogram_id=1 and p.object_type<>'PACKAGE'))

And the results are:

SQL> @p

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
APEX_030200 WWV_FLOW_FILE_OBJECT_ID PACKAGE
APEX_030200 WWV_FLOW_FND_GLOBAL PACKAGE
APEX_030200 WWV_FLOW_FND_USER_PW_PREF PACKAGE
APEX_030200 WWV_FLOW_GLOBAL PACKAGE
APEX_030200 WWV_FLOW_IMAGE_PREFIX PACKAGE
APEX_030200 WWV_FLOW_THEME_GLOBALS PACKAGE
APEX_030200 WWV_FLOW_TRANSLATION_UTILITIES PACKAGE
APEX_030200 WWV_FLOW_TREE_GLOBAL_VARS PACKAGE
CORE CNST PACKAGE
CORE TYP PACKAGE
CTXSYS CTX_CATSEARCH PACKAGE CURRENT_USER

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
CTXSYS CTX_CONTAINS PACKAGE CURRENT_USER
CTXSYS CTX_MATCHES PACKAGE CURRENT_USER
CTXSYS CTX_ULEXER PACKAGE
CTXSYS CTX_XPCONTAINS PACKAGE CURRENT_USER
CTXSYS DRIG PACKAGE
CTXSYS DRILIST PACKAGE
CTXSYS DRIOBJ PACKAGE
CTXSYS DRISCORE PACKAGE CURRENT_USER
CTXSYS DRISCORR PACKAGE DEFINER
CTXSYS DR_DEF PACKAGE
MDSYS SDOCURPKG PACKAGE

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
MDSYS SDO_GEOR_LIZARDTECH PACKAGE CURRENT_USER
MDSYS SDO_NET_MEM PACKAGE CURRENT_USER
MDSYS SDO_TOPO_MAP PACKAGE CURRENT_USER
OLAPSYS CWM$EXCEPTIONS PACKAGE
OLAPSYS CWM2_OLAP_EXCEPTIONS PACKAGE
ORDSYS ORDAUDIOEXCEPTIONS PACKAGE
ORDSYS ORDDOCEXCEPTIONS PACKAGE
ORDSYS ORDIMAGECONSTANTS PACKAGE
ORDSYS ORDIMAGEEXCEPTIONS PACKAGE
ORDSYS ORDIMAGESIEXCEPTIONS PACKAGE
ORDSYS ORDIMGSIG_PKG PACKAGE CURRENT_USER

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
ORDSYS ORDSOURCEEXCEPTIONS PACKAGE
ORDSYS ORDVIDEOEXCEPTIONS PACKAGE
SYS BLAST_CUR PACKAGE
SYS DBMS_CRYPTO_TOOLKIT_TYPES PACKAGE
SYS DBMS_DBFS_CONTENT_SPI PACKAGE DEFINER
SYS DBMS_DB_VERSION PACKAGE
SYS DBMS_FILE_GROUP_DECL PACKAGE
SYS DBMS_LCR PACKAGE
SYS DBMS_RESULT_CACHE_API PACKAGE DEFINER
SYS DBMS_STANDARD PACKAGE DEFINER
SYS DBMS_STREAMS_ADM_UTL_INT PACKAGE

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
SYS DBMS_STREAMS_DECL PACKAGE
SYS DBMS_TYPES PACKAGE
SYS DM_CL_CUR PACKAGE
SYS DM_GLM_CUR PACKAGE
SYS DM_MODB_CUR PACKAGE
SYS DM_NMF_CUR PACKAGE
SYS DM_SVM_CUR PACKAGE
SYS KUPCC PACKAGE
SYS LCR$_XML_SCHEMA PACKAGE
SYS ODCICONST PACKAGE
SYS OWA_CX PACKAGE

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
SYS PBUTL PACKAGE
SYS PLITBLM PACKAGE DEFINER
SYS SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE DEFINER
SYS UTL_IDENT PACKAGE
SYS UTL_MATCH PACKAGE DEFINER
SYS WWV_FLOW_KEY PACKAGE
XDB DBMS_XMLDOM_ICD PACKAGE CURRENT_USER
XDB XDB_PVTRIG_PKG PACKAGE CURRENT_USER

63 rows selected.

Hmm, we had 53 PL/SQL packages that do not have DEFINER or CURRENT_USER set for AUTHID but we have 63 objects here that are PACKAGE without a PACKAGE BODY but of course as there are 63 and not 53 then 10 of them do have a DEFINER or CURRENT_USER set for the AUTHID. Nothing popped out immediately but I thought I would check 19c next as all of the above was in 11.2.0.4. Lets see the same issue in my 19c database by running my p.sql script again from above:

C:\_aa\PB\bin>sqlplus system/oracle1@//192.168.56.77:1521/orclpdb.localdomain

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 24 14:45:08 2020

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>
SQL> @p

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
CTXSYS CTX_CATSEARCH PACKAGE CURRENT_USER
CTXSYS CTX_CONTAINS PACKAGE CURRENT_USER
CTXSYS CTX_XPCONTAINS PACKAGE CURRENT_USER
CTXSYS DRIOBJ PACKAGE
CTXSYS DRISCORE PACKAGE CURRENT_USER
CTXSYS DRISCORR PACKAGE DEFINER
CTXSYS DR_DEF PACKAGE
MDSYS SDOCURPKG PACKAGE
MDSYS SDO_JAVA_STP PACKAGE CURRENT_USER
MDSYS SDO_RDF_JAVA_STP PACKAGE CURRENT_USER
ORDSYS ORDAUDIOEXCEPTIONS PACKAGE

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
ORDSYS ORDIMAGEEXCEPTIONS PACKAGE
SYS DBMS_REGISTRY_SERVER PACKAGE
SYS DBMS_STANDARD PACKAGE DEFINER
SYS DBMS_STREAMS_DECL PACKAGE
SYS DBMS_TYPES PACKAGE
SYS DBMS_WRR_STATE PACKAGE
SYS OWA_CX PACKAGE
SYS PBUTL PACKAGE
SYS SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE DEFINER
SYS UTL_IDENT PACKAGE
XDB XDB_PVTRIG_PKG PACKAGE CURRENT_USER

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
CTXSYS CTX_MATCHES PACKAGE CURRENT_USER
CTXSYS CTX_ULEXER PACKAGE
CTXSYS DRIG PACKAGE
CTXSYS DRILIST PACKAGE
LBACSYS LBAC_ERRORS PACKAGE
MDSYS SDO_NET_MEM PACKAGE CURRENT_USER
MDSYS SDO_SEM_CONST PACKAGE
MDSYS SDO_TOPO_MAP PACKAGE CURRENT_USER
MDSYS SEM_RDFSA_CONST PACKAGE
MDSYS SEM_RDFSA_CONST_INTERNAL PACKAGE
ORDSYS ORDDOCEXCEPTIONS PACKAGE

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
ORDSYS ORDIMAGECONSTANTS PACKAGE
ORDSYS ORDIMAGESIEXCEPTIONS PACKAGE
ORDSYS ORDSOURCEEXCEPTIONS PACKAGE
ORDSYS ORDVIDEOEXCEPTIONS PACKAGE
SYS DBMS_CRYPTO_TOOLKIT_TYPES PACKAGE
SYS DBMS_DBFS_CONTENT_SPI PACKAGE DEFINER
SYS DBMS_DB_VERSION PACKAGE
SYS DBMS_FILE_GROUP_DECL PACKAGE
SYS DBMS_LCR PACKAGE
SYS DBMS_RESULT_CACHE_API PACKAGE DEFINER
SYS DBMS_SERVICE_CONST PACKAGE

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYS DBMS_STREAMS_ADM_UTL_INT PACKAGE
SYS KUPCC PACKAGE
SYS ODCICONST PACKAGE
SYS ORA_DM_REFCUR_PKG PACKAGE
SYS PLITBLM PACKAGE DEFINER
SYS UTL_MATCH PACKAGE DEFINER
XDB DBMS_XMLDOM_ICD PACKAGE CURRENT_USER

51 rows selected.

SQL>

Check for the number of NULL AUTHID columns:

SQL> select count(*) from dba_procedures where authid is null;

COUNT(*)
----------
41

SQL>

Lets not see if we can replicate the issue. First create a package header with a procedure definition in it:

SQL> create or replace package test is
2 procedure testit;
3 end test;
4 /

Package created.

SQL> select owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM';

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE DEFINER
SYSTEM TEST PACKAGE DEFINER


That's not it, obvious really as a header with a procedure definition does not make sense if the body without the procedure implementation. So the next check is to create package header without any procedure and only global variables:

SQL> create or replace package test as
2 gc_var constant number:=1;
3 end test;
4 /

Package created.

SQL> select owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM';

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE

SQL>

OK, that proves it. If you create a PL/SQL package header and only have globals defined in it and no procedures or functions that need implementing then Oracle creates the PACKAGE HEADER and does not set DEFINER or CURRENT_USER in DBA_PROCEDURES. So we can replicate the issue and we know in what circumstances it occurs BUT is it a bug or not? Not sure? if you have a package header and it doesn't have executable code then does it make sense for it to be DEFINER or CURRENT_USER. It sort of makes sense that neither is defined BUT if we forced an error:

SQL> create or replace package test as
2 gc_var number(10);
3 end test;
4 /

Package created.

SQL>

SQL> select owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM';

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE

SQL>
SQL> begin
2 test.gc_var:='test test test';
3 end;
4 /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2


SQL>

Maybe it could be an issue so maybe the package header even with no function or procedure should have the ability to define DEFINER or CURRENT_USER. Lets see if we can set the AUTHID:

SQL> create or replace package test authid definer as
2 gc_var number(10);
3 end test;
4 /

Package created.

SQL> select owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM';

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE

SQL> create or replace package test authid current_user as
2 gc_var number(10);
3 end test;
4 /

Package created.

SQL> select owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM';

OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE

SQL>

So, we cannot force the AUTHID for PL/SQL package header when there is no procedure or function in the package.

Installing Oracle 19c on Linux

I needed to create a new 19c install yesterday for a test of some customer software and whilst I love Oracle products I have to say that installing the software and database has never been issue free and simple over many many years. I can remember installing Oracle 7.0 and all versions since. Those earlier versions are simple compared to 19c with multitenant architecture but the troubles still occur.

I first downloaded the V???.zip image for 19c from Oracles edelivery site and trying to unzip it I got invalid zip file format errors. So, unperturbed I downloaded it again and same error. Hmm, so I looked at the downloads available from OTN and took the rpm image and thought I would catch up some time and instead do an rpm install of the software and then build a database with the scripts provided.

yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm

So, downloaded the rpm and installed this pretty quickly but the database build with the provided script:

/etc/init.d/oracledb_ORCLCDB-19c configure

Failed with a message that the LISTENER process was already in use - which it wasn't an netstat -aun check proved this. There was no obvious explanations on line so decided to now download the third version of 19c, the LINUX file system zip that allows all files to be unzipped into the pre-prepared ORACLE_HOME:

unzip -oq /path/to/software/LINUX.X64_193000_db_home.zip

Yes, you guessed it; this unzip also failed. I downloaded it a second time and tried again. Nope, it really failed with a CRC checksum failure for the file dcore.bsq; hmmm, so zip failed and dcore.bsq is empty when inspected. What to do?

I decided to revert the vm back to the base Linux and reinstall the rpm version software again as that part worked; save the dcore.bsq file and then revert the VM again and unzip the LINUX install and replace the dcore.bsq file and then continue and install 19c.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>

I installed 19c some time ago and didn't have issues so not sure why this install was troublesome?

Oracle Security Training Manuals for Sale

We have one set of Manuals for the recent training we held here in York and one from 2018. These can be bought as individual books as follows:

Printed manuals for How to Perform a Security Audit of an Oracle Database class October 2019
This manual is from the York class in October 2019 and can be bought for £75 + postage and packing + VAT if applicable.

Printed manuals for hardening and locking Oracle class October 2019
This manual is from the York class Locking and hardening Oracle in October 2019 and can be bought for £38 + postage and packing + VAT if applicable

Printed manuals for hardening and locking Oracle class October 2019
This manual is from the York class Designing practical audit trails for Oracle in October 2019 and can be bought for £38 + postage and packing + VAT if applicable

Printed manuals for How to Perform a Security Audit of an Oracle Database class July 2018
This manual is from a private class taught in July 2018 and can be bought for £75 + postage and packing + VAT if applicable.

Please contact me via the website or you can contact me via Twitter, LinkedIn or Facebook where this is also posted.

SELECT ANY DICTIONARY - What Privileges Does it Have - SELECT_CATALOG_ROLE

There has been a few blog posts over the years discussing what is the difference between SELECT ANY DICTIONARY and the SELECT_CATALOG_ROLE. Hemant posted in 2014 about the difference between SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE. This post was a high level look at the two rights. Arup Nanda did a more in-depth review of the differences between the two back in 2011 in a post called Difference between Select Any Dictionary and Select_Catalog_Role.

Arups post was more in depth and illustrated that both allow access to dictionary objects but in a different way. SELECT ANY DICTIONARY is a system privilege and SELECT_CATALOG_ROLE is a database role that contains various grants on SYS objects - more on those in a minute.

Arup made a point that the main difference is that SELECT_CATALOG_ROLE is a role and therefore its not useful to grant this when code accesses one of the tables or views granted to that role. This is because the PL/SQL compiler cannot access the VIEW or table - Arup used V$SESSION and v$DATABASE - actually V_$SESSION and V_$DATABASE as these are the actual views not the synonyms. So to compile PL/SQL code that uses a SYS view that could be exposed by SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY to the PL/SQL code owner then the role doesn't work. Actually from 12c its possible to grant roles to code but more on that in a later blog post and also se the conclusion later in this post

SELECT_CATALOG_ROLE is much older than SELECT ANY DICTIONARY as the latter was added to resolve access to the data dictionary tables and views when Oracle closed them by changing O7_DICTIONARY_ACCESSIBILITY many years ago. This parameter stopped system %ANY% privileges such as SELECT ANY TABLE affecting SYS objects and the data dictionary.

Mike Dietrich also posted about SELECT ANY DICTIONARY in 2015 in a post titled New Behaviour in Oracle Database 12c and 11.2.0.4: SELECT ANY DICTIONARY with reduced privilege set to discuss that in 12c Oracle restricted SELECT ANY DICTIONARY to not include access to tables such USER$, XS$VERIFIERS, ENC$ etc. Mike confirmed that LINK$ was excluded from SELECT ANY DICTIONARY back in release 10.1 and new exclusions have been added through the years. Also Mike confirmed that you cannot grant SELECT ANY DICTIONARY to a role:

SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> create roles pete;

Role created.

SQL> grant select any dictionary to pete;

Grant succeeded.

SQL> grant pete to test;

Grant succeeded.

SQL> connect test/test@//192.168.56.78:1523/xepdb1

SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------------------------------------------------------
PETE

1 row selected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE MATERIALIZED VIEW
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
CREATE DIMENSION
SELECT ANY DICTIONARY
CREATE JOB

15 rows selected.

SQL>
SQL> select count(*) from v$session;

COUNT(*)
----------
51

1 row selected.

SQL>
SQL> select count(*) from v$database;

COUNT(*)
----------
1

1 row selected.

SQL>

Just to prove that a user without SELECT ANY DICTIONARY cannot see V$SESSION and V$DATABASE:

SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> create user test1 identified by test1;

User created.

SQL> grant create session to test1;

Grant succeeded.

SQL> connect test1/test1@//192.168.56.78:1523/xepdb1
Connected.
SQL> select count(*) from v$database;
select count(*) from v$database
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from v$session;
select count(*) from v$session
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

OK, so Mikes statement that SELECT ANY DICTIONARY cannot be granted via a role des not seem correct at least in 18cXE. Mike also stated that SELECT ANY DICTIONARY cannot be granted via GRANT ALL PRIVILEGES. I assume that he meant GRANT ANY PRIVILEGE; lets test that for completeness:

SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> create user test3 identified by test3;

User created.

SQL> grant create session,grant any privilege to test3;

Grant succeeded.

SQL> connect test3/test3@//192.168.56.78:1523/xepdb1
Connected.
SQL> select * from session_roles;

no rows selected

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
GRANT ANY PRIVILEGE

2 rows selected.

SQL>

SQL> grant select any dictionary to test;

Grant succeeded.

SQL>

This would also seem to be incorrect at least in 18c XE. Lets go back to 11.2.0.4 and test if its the same:

SQL> connect sys/oracle1@//192.168.1.85:1521/bfora.localdomain as sysdba
Connected.
SQL> create role pete;
create role pete
*
ERROR at line 1:
ORA-01921: role name 'PETE' conflicts with another user or role name


SQL> create role pete_role;

Role created.

SQL> grant select any dictionary to pete_role;

Grant succeeded.

SQL> create user test1 identified by test1;

User created.

SQL> grant create session, pete_role to test1;

Grant succeeded.

SQL> connect test1/test1@//192.168.1.85:1521/bfora.localdomain
Connected.
SQL> select * from session_roles;

ROLE
------------------------------
FACADM_SUPPORT
FACADM_ALL
PETE_ROLE

3 rows selected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY DICTIONARY

2 rows selected.

SQL> select count(*) from v$session;

COUNT(*)
----------
22

1 row selected.

SQL> select count(*) from v$database;

COUNT(*)
----------
1

1 row selected.

SQL>

So we can grant SELECT ANY DICTIONARY via a role in 11.2.0.4. What about GRANT ANY PRIVILEGE:

SQL> connect sys/oracle1@//192.168.1.85:1521/bfora.localdomain as sysdba
Connected.
SQL> create user test4 identified by test4;

User created.

SQL> grant create session,grant any privilege to test4;

Grant succeeded.

SQL> connect test4/test4@//192.168.1.85:1521/bfora.localdomain
Connected.
SQL> select * from session_roles;

ROLE
------------------------------
FACADM_SUPPORT
FACADM_ALL

2 rows selected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
GRANT ANY PRIVILEGE

2 rows selected.

SQL> grant select any dictionary to test1;

Grant succeeded.

SQL>

So, yes, this seems to be historic behaviour. I am uncertain what Mike meant by the statement in his blog that this privilege cannot be granted via a role and cannot be granted with GRANT ANY PRIVILEGE. His statement is
"Please be very careful with granting this privilege. Furthermore, you need to be aware that it can’t be granted either through a role, nor is it included in the GRANT ALL PRIVILEGES"
. This doesn't seem to be true.

I don't want to repeat what Arup did in his post, i want to focus on what actually is included in SELECT ANY DICTIONARY. Mike states in his post that you cannot query anywhere in the database to find out exactly what is granted (or limited) by this privilege. First lets see how many grants SELECT_CATALOG_ROLE actually has:

SQL> connect system/oracle1@//192.168.56.78:1523/xepdb1
Connected.
SQL>
SQL> select count(*),privilege
2 from dba_tab_privs
3 where grantee='SELECT_CATALOG_ROLE'
4 group by privilege;

COUNT(*) PRIVILEGE
---------- ----------------------------------------
2 EXECUTE
4521 SELECT
2 READ
14 FLASHBACK

SQL>

So, SELECT_CATALOG_ROLE is not pure SELECT as it also has some FLASHBACK and also 2 READ but importantly 2 EXECUTE grants. The total grants are 4539 in Oracle 18c XE.

The next step is how many SYS objects of TABLE or VIEW are there. I know you also SELECT from a SEQUENCE but lets focus on TABLE and VIEW:

SQL> create user sad identified by sad;

User created.

SQL>
SQL> grant select any dictionary to sad;

Grant succeeded.

SQL>
SQL> grant create session to sad;

Grant succeeded.

SQL>

SQL> connect sad/sad@//192.168.56.78:1523/xepdb1
Connected.
SQL>
SQL> select count(*),object_type from dba_objects where owner='SYS' and object_type in ('VIEW','TABLE') group by object_type;

COUNT(*) OBJECT_TYPE
---------- -----------------------
1510 TABLE
6629 VIEW

SQL>

So there are 8139 TABLE and VIEW owned by SYS. How many are already granted to PUBLIC. If they are we cannot test if they are affected by SELECT ANY DICTIONARY as many users with just CREATE SESSION can access them; so lest ignore these as well:

SQL> get c
1 select count(*) from (
2 select distinct owner owner,
3 table_name object_name,
4 type object_type
5 from dba_tab_privs
6 where owner ='SYS'
7 and grantee='PUBLIC'
8* and type in ('TABLE','VIEW'))
SQL> @c

COUNT(*)
----------
1754

1 row selected.

SQL>

So we have 8139 - 1754 = 6385 potential TABLE and VIEW that could potentially be accessed via SELECT ANY DICTIONARY. We know from Oracle on the page "SELECT ANY DICTIONARY - privilege no longer accesses some SYS data dictionary tables". This page states that SELECT ANY DICTIONARY does not give access to DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Only SYSDBA can access these tables now. OK, that's the documentation but how many of the 6385 potential TABLE and VIEW can we access with this privilege. We can find this out from the database easily, example also 18c:

SQL> @sad
...{output removed for space}
------------------------------------
Number of tables = [1465]
Number of views = [4920]
Number of exclusions = [157]

PL/SQL procedure successfully completed.

SQL>

So there are 1465 TABLE and 4920 VIEW that can be potentially accessed that are not granted to PUBLIC. This is 6385 potential objects as above. But we can now see a list of the 157 objects that cannot be accessed by anyone granted SELECT ANY DICTIONARY. So SELECT ANY DICTIONARY grants access in 18c XE to 6228 TABLE and VIEW objects. Most of the main tables are not accessible are in the documentation but the actual list also includes IOT and also a lot of underscore views. There are limited additional more normal real tables/views that are not listed such as OPATCH_XML_INV.

What is interesting is if we check whats granted to SELECT_CATALOG_ROLE we can use my find_all_privs.sql script to do that. This shows above that there are 4539 grants to SELECT_CATALOG_ROLE so this is a lot less than SELECT ANY DICTIONARY at 6228. Interestingly if we check the find_all_privs output we can see that a number of the views not allowed in SELECT ANY DICTIONARY are allowed in SELECT_CATALOG_ROLE. Some are here:

...{output removed for space saving}
TABLE PRIV => SELECT object => XDB.XDB_INDEX_DDL_CACHE grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_APPLY_CONSTRAINT_COLUMNS grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_APPLY_OBJECT_CONSTRAINTS grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_GGXSTREAM_INBOUND grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_GGXSTREAM_OUTBOUND grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_ACTIONS grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_COMPONENT grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_COMPONENT_EVENT grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_COMPONENT_LINK grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_COMPONENT_PROP grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_COMPONENT_STAT grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_FINDINGS grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_RECOMMENDATIONS grantable => NO
TABLE PRIV => SELECT object => SYS._DBA_STREAMS_TP_COMPONENT_PROP grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_APPLY_COORDINATOR grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_APPLY_READER grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_APPLY_SERVER grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_CAPTURE grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_MESSAGE_TRACKING grantable => NO
TABLE PRIV => SELECT object => SYS._GV$SXGG_TRANSACTION grantable => NO
TABLE PRIV => SELECT object => SYS._INMEMORY_AIMTASKDETAILS grantable => NO
TABLE PRIV => SELECT object => SYS._INMEMORY_AIMTASKS grantable => NO
TABLE PRIV => SELECT object => SYS._SYS_AIM_SEG_HISTOGRAM grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_APPLY_COORDINATOR grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_APPLY_READER grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_APPLY_SERVER grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_CAPTURE grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_MESSAGE_TRACKING grantable => NO
TABLE PRIV => SELECT object => SYS._V$SXGG_TRANSACTION grantable => NO

As you can see there are a lot of the SYS._% views are listed here; not all of them that exist but quite a few. So we have cross over between things blocked by SELECT ANY DICTIONARY (some SYS._% views ) and SELECT_CATALOG_ROLE.

Conclusion?

Well, SELECT_CATALOG_ROLE allows access to some things Oracle deemed not allowed by SELECT ANY DICTIONARY so we need to be careful of granting this role on these grounds. BUT, the overwhelming issue for me is that SELECT_CATALOG_ROLE gives access to 4539 objects and SELECT ANY DICTIONARY gives access to 6228 objects (both numbers in 18c XE)

I am not sure why Oracle do not publish the full list of exclusions in SELECT ANY DICTIONARY but they do publish all of the main tables. We can easily find out anyway. For me, i want to know what does SELECT ANY DICTIONARY really mean. I want to know what i am actually granting if I give out that privilege; well it means access to 6228 tables and views in 18cXE

Both of these rights should not be used; they are a sledgehammer to crack a peanut. If someone needs access to V$SESSION or V$DATABASE and there is a legitimate reason to have that access then grant access on the individual views not SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE.

What Privileges Can you Grant On PL/SQL?

Oracle has a lot of privileges and models; privileges can be granted to users, roles and also since 12c roles can be granted to PL/SQL code (I will not discuss this aspect here as i will bog separately about grants of roles to PL/SQL).

I wanted to know what privileges can be granted to PL/SQL so first in 18c XE I can make a simple query and see what's already granted to PL/SQL in a clean XE database:

SQL> select distinct privilege
2 from dba_tab_privs
3 where type in ('PROCEDURE','FUNCTION','TYPE','PACKAGE','PACKAGE BODY','TYPE BODY');

PRIVILEGE
----------------------------------------
EXECUTE

SQL>

Now connect to my sample schema CRM and grant all privileges to the user TEST:

SQL> connect crm/crm@//192.168.56.78:1523/xepdb1
Connected.
SQL> grant all on crm_proc to test;

Grant succeeded.

SQL>

Now connect as SYSTEM and have a look at the privileges actually granted:

SQL> connect system/oracle1@//192.168.56.78:1523/xepdb1
Connected.

SQL> set serveroutput on
SQL> @print 'select * from dba_tab_privs where table_name=''''CRM_PROC'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_tab_privs where table_name=''CRM_PROC''','''','''''');
Executing Query [select * from dba_tab_privs where table_name='CRM_PROC']
GRANTEE : TEST
OWNER : CRM
TABLE_NAME : CRM_PROC
GRANTOR : CRM
PRIVILEGE : EXECUTE
GRANTABLE : NO
HIERARCHY : NO
COMMON : NO
TYPE : PROCEDURE
INHERITED : NO
-------------------------------------------
GRANTEE : TEST
OWNER : CRM
TABLE_NAME : CRM_PROC
GRANTOR : CRM
PRIVILEGE : DEBUG
GRANTABLE : NO
HIERARCHY : NO
COMMON : NO
TYPE : PROCEDURE
INHERITED : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Just DEBUG and EXECUTE are granted; not much then!. Lets see the system privileges that affect PL/SQL:

SQL> select name from system_privilege_map where name like '%PROCED%';

NAME
----------------------------------------
DEBUG ANY PROCEDURE
EXECUTE ANY PROCEDURE
DROP ANY PROCEDURE
ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
CREATE PROCEDURE

6 rows selected.

SQL>

As you can see from the SYSTEM_PRIVILEGE_MAP there are 6 system privileges that relate to PROCEDURE (PL/SQL) but only one that is a direct grant to a single user that affects his own rights in relation to PL/SQL; thats CREATE PROCEDURE; obviously this only affects new PL/SQL - i.e. you can create new ones or do CREATE OR REPLACE and overwrite a new one.

Of course there are multiple %ANY% privileges that do not exist at the single use level; There are DROP, ALTER, CREATE, EXECUTE and DEBUG ANY rights. This means that the grantee of these rights can affect other users code (except SYS as its protected by O7_DICTIONARY_ACCESSIBILITY - deprecated in 18c and desupported in 19c).

so we have ALTER ANY PROCEDURE not no ALTER PROCEDURE privilege. So we cannot grant ALTER PROCEDURE to TEST to use on CRMs procedures.

SQL> connect crm/crm@//192.168.56.78:1523/xepdb1
Connected.
SQL> grant alter on crm_proc to test
2 /
grant alter on crm_proc to test
*
ERROR at line 1:
ORA-02225: only EXECUTE and DEBUG privileges are valid for procedures


SQL>

This confirms that there are only two possible rights for a piece of PL/SQL, DEBUG and EXECUTE but CRM can ALTER his own PL/SQL:

SQL> sho user
USER is "CRM"
SQL> alter procedure crm_proc compile;

Procedure altered.

SQL>

This is the so called "Object owner principle". An objects owner can issue all sorts of commands on his own objects where those commands are valid for the object type. So for a piece of PL/SQL the owner can issue ALTER PROCEDURE.... or DROP PROCEDURE.... or AUDIT PROCEDURE... so an example could be:

SQL> audit execute on crm_proc;

Audit succeeded.

SQL>

Thee abilities do not show up in a list of grants to CRM_PROC and others cannot do this to CRMs objects unless they have an %ANY% right. So this is a statement not a privilege - In standard Oracle audit we have STATEMENT privileges and SYSTEM PRIVILEGES, seen in the views DBA_STMT_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS.

The object privileges in the database can be seen here:

SQL> select * from table_privilege_map;

PRIVILEGE NAME
---------- ----------------------------------------
0 ALTER
1 AUDIT
2 COMMENT
3 DELETE
4 GRANT
5 INDEX
6 INSERT
7 LOCK
8 RENAME
9 SELECT
10 UPDATE

PRIVILEGE NAME
---------- ----------------------------------------
11 REFERENCES
12 EXECUTE
16 CREATE
17 READ
18 WRITE
20 ENQUEUE
21 DEQUEUE
22 UNDER
23 ON COMMIT REFRESH
24 QUERY REWRITE
26 DEBUG

PRIVILEGE NAME
---------- ----------------------------------------
27 FLASHBACK
28 MERGE VIEW
29 USE
30 FLASHBACK ARCHIVE

26 rows selected.

SQL>

Lots of possible object privileges but only 2 that relate to PL/SQL as privileges that can be granted; DEBUG and EXECUTE. The one system privilege that relates to a single schema and PL/SQL is the CREATE PROCEDURE and to prove that its needed not just to CREATE a procedure but also for CREATE OR REPLACE - i.e. reinstall a procedure we will revoke CREATE PROCEDURE from the CRM user and try and recreate his CRM_PROC PL/SQL:

SQL> connect crm/crm@//192.168.56.78:1523/xepdb1
Connected.
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
CONNECT
RESOURCE
SODA_APP

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE CLUSTER
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

PRIVILEGE
----------------------------------------
SET CONTAINER

12 rows selected.

SQL> connect system/oracle1@//192.168.56.78:1523/xepdb1
Connected.
SQL> revoke resource from crm;

Revoke succeeded.

SQL> connect crm/crm@//192.168.56.78:1523/xepdb1
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE VIEW
SET CONTAINER

SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
CONNECT

SQL>

we cannot do a replace:
SQL> create or replace procedure crm_proc(lv_text in varchar2) is
2 begin
3 dbms_output.put_line(lv_text);
4 end;
5 /
create or replace procedure crm_proc(lv_text in varchar2) is
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

So even though a user (CRM here) has an object CRM_PROC he cannot CREATE OR REPLACE it again as he no longer has CREATE PROCEDURE. So only two actual privileges that can be granted by the owner of a piece of PL/SQL code to another user but 5 SYSTEM %ANY% privileges that would allow others to CREATE, ALTER, DROP, DEBUG and EXECUTE. The owner can of course do all of these except CREATE and also other less obvious grants are things like AUDIT:

SQL> select name from system_privilege_map where name like '%AUDIT%';

NAME
----------------------------------------
AUDIT ANY
AUDIT SYSTEM

SQL>

Audit SYSTEM allows someone to issue audit commands on privileges. AUDIT ANY allows the grantee to issue audit commands on others objects. From an attacker perspective then he could also issue NOAUDIT and turn off audit settings on objects that are already set.

More soon on grants to 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.

ORA-01950 Error on a Sequence

UPDATE: I have updated information for this post and rather than make this one much longer i created a new post - please see ORA-01950 Error on a Sequence - Error on Primary Key Index

Wow, its been a while since i last blogged here. I keep promising to blog more about Oracle Security but paying work gets in the way..:-(. I do have a very big list of things that I would like to blog about around Oracle security in general and also around our products PFCLScan, PFCLObfuscate, PFCLATK, PFCLCode and PFCLCookie. We released version 3.0 of PFCLScan a couple of weeks ago and we are already working hard on the next major release 4.0 which will include PFCLCode our PL/SQL source code security analyser. This product will also be available separately or with PFCLScan.

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.