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.

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.

PFCLScan - Version 3.0

PFCLScan Version 3.0 Dashboard and Single Page report

We are very excited to announce that we are currently working to have version 3.0 of PFCLScan our flagship database security scanner for the Oracle database. We will be ready for sale in September and this development is going really well. I just wanted to give a quick update on progress and a first discussion on the new updates / changes to be added for version 3.0.

The first major change that will not affect many people - in the sense that they will not really notice it - is that we re-wrote major parts of the Windows interface to use a more up to date screen components; this is around the Ribbon menus, the dockable windows and the results grids. This major re-write was stability and commitment to support of the interface. PFCLScan was working fine but some customers using Windows 10 builds 1709 and 1803 experienced some issues that could not be fixed as the components we used unfortunately had support stopped. So we committed quickly to a major re-write and this part is now complete.

Another major change to the interface / tool is the addition of plugins. Again most users will not notice this as its used in the background BUT we needed it to enable other features to be added more easily and we hope that most users will benefit from the plugins we plan to add. Actually a first version of Pugins is already there in version 2.0 but most people will not have noticed and in version 2.0 it was not flexible to allow new plugins to be run. The test database connection and the test server connection in the respective "manage database connections" and "manage server connections" screens were actually a first version of plugins. We have now re-written the plugin architecture and enabled it properly. So the two connection plugins for database and server are now proper plugins and could be dynamically replaced - why would you..:-) but you could.

A plugin in PFCLScan is actually just a normal PFCLScan project; so end users can create a new project to do something and add it to the plugins screen and then run it or they could substitute an existing plugin for theirs to customise what is run at the plugin time. We have added a number of "hook-points" to the interface and these are accessible via the plugins screen to configure. Each "Hook-point" represents a place in the interface where plugins can run. We provide standard plugins for each hook point BUT the end user can create their own if they wish and the interface will run them for you.

This creates a powerful interface as its possible to extend the scanner without changing it. Some of the hook points we already added are:

  • Test database connection

  • Test server connection

  • Before a scan

  • After a scan

  • After Error



We have provided plugins for these places and you can view them or change them; it's up to you. For instance the before a scan hook point is used to run a plugin that tests that the Oracle database user to be used for the scan has the correct permissions to run the scan. This is great as we get customers raising a ticket to say a scan errored and actually it was because the database user that they used was missing a permission. The permissions are covered in the manual of course.

Going forwards We plan to add many more plugins around many areas. A flavour is here:

  • Compare databases security - we can do this now but a plugin will be better

  • Compare scans - we can do this now but a plugin will be better

  • The new dashboard will be populated by a plugin

  • Convert a project to a plugin template

  • Allow us to implement more hands on tools - Forensics timeline, PL/SQL Code analysis, Audit Trail toolkit interface....

  • Find things, projects, policies, reports....

  • Many more


We have also added a new dashboard to the scanner to review all of the scans of databases that have taken place and all of the databases that have been scanned. This is a one stop shop to see the current average security state of all databases across the enterprise or a single database. The security is also been down into 8 distinct security categories and you can view the security progress in each category across the who database or for individual databases. You can also view the number of errors broken down across the whole database or categories and by severity from critical to medium.

The second major improvement to help you manage the security of all databases or individual databases is a new single page score report for a single database. This is a major tool for management to see at a very high level the security of a single database and how that security is broken down across 8 distinct categories of security issues in your databases. The report is also clever as it can suggest where the best improvements to overall database security can be gained by targeting your fixes - All of this in one page!!

The single page score report and the dashboard of course show the same details for each database.

Of course PFCLScan supports creating projects that allow you to scan multiple databases in one scan / project and as such there is also multi database version of the single page database security score report.

We have also been adding hundreds of new Oracle security checks to our scanner policies. We are also adding new reports and some new project types - more details to follow

Due to customer feedback; and whilst our reporting language is very simple and easy to create and write your own reports even if you are not a programmer
We will also add the ability to choose some simple customisations on the main database security report.

Version 3.0 will be released in September so if you have a valid license you will get a copy; Version 4.0 is also being developed and the roadmap for version 4.0 will include integration of our audit trail toolkit for activity monitoring of database engine use; This is called PFCLATK and is discussed here. We will also include SQL Server and Postgress and mySQL, MariaDB etc in version 4.0. This will be released in January 2020.

We have also been looking for new resellers for PFCLScan and some are signed up and will be added to the reseller page soon BUT we are interested to talk to any company who would like to represent PFCLScan in their region. Please contact me to discuss details and to become a reseller for us. One of the big advantages for you to reselling a database vulnerability scanner if you are a service company is that you can easily upsell services such as help with policy, custom reports and of course help with correcting the issues located. Its a win-win for us and also for resellers.

Our license fees for Engagement, Pro and Enterprise licenses have been held for a number of years at their current levels and we will be adding a reasonable price increase in September to coincide with the release of Version 3.0. So buy now to get the lower license fees and if you buy a Pro or Enterprise license now you will get version 3.0 and version 4.0 updated versions when they are released.

Contact us now to book your online demo via webex or to purchase a license.

Thanks for listening!