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.

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!

PFCLATK - Audit Trail Toolkit - Checksums

We have a toolkit called PFCLATK that is used in customer engagements to assist our customers to create comprehensive and useful audit trails for their databases. The toolkit is used in consulting engagements at the moment but will be adding into PFCLScan our vulnerability scanner for Oracle databases in the next release.

In these audit trail design and implement engagements we sit down with a customer and help them design useful audit trails for the Oracle database engine (i.e. capture actual Oracle database abuse) and also data access and controls. We initially create a table of all of the events that we would like to capture and classify them at a severity level, need for reporting, alerting and escalation. From this we can decide how fast the customer must react to an event becoming true. i.e. if we decide to react in real time or semi real time then an alert is raised straight away or if we decide to react once per day a report or an alert is raised or a report produced. Of course we then map the design document - the events table into the rules, factors and settings into the policies of the PFCLATK toolkit and deploy rapidly.

This means that we will sell licenses for PFCLATK inside of PFCLScan. Currently we only use it as a consulting tool but from version 3.0 of PFCLScan PFCLATK will be part of that product. This means that we will have two major interfaces added into PFCLScan for PFCLATK. The first is a dashboard to show realtime monitoring of Oracle databases and indications of the alerts and issues located and a second admin interface to allow the deployment of the toolkit and also to allow maintenance and configuration of all of the toolkits settings.

The power of adding PFCLATK into PFCLScan is that we can now combine activity monitoring and also vulnerability and data security analysis. This will mean that as audit trail events fire and are captured in the audit part say for 2 of your databases out of 60 databases you will be immediately able to go to the vulnerability section and just click and perform a quick vulnerability audit of all of the databases to see if they gave an issue in the area captured in 2 databases or perform complete security audits.

Conversely after a vulnerability scan you will be able to click and immediately deploy and audit trail policy set for the issues located during the vulnerability scans. This gives a strong synergy between looking for security holes in the Oracle database configuration and immediately enabling audit trails to capture abuse of those holes and also from the other side detecting attacks or anomalies and then running scans to see how the rest of the estate fares with the same issues.

The audit toolkit PFCLATK also includes centralised logging and auditing. You can deploy the toolkit to all target databases and then also deploy to a single or multiple central database that automatically collects the audit trails from the target databases into the central database to allow centralised management and reporting.

Anyway that's a quick intro to PFCLATK and now to the topic of the post. Part of the centralised audit is the the PUL mechanism that runs automagically to to PUL (yes, one L) the data from each target database to the central database. The process at a high level does these steps for each of the audit tables in the target database:

  1. Get a count of records in the previous hour

  2. checksum the records using a SHA1 checksum of the previous hour

  3. copy the records from the target to the central database using the DBID as a unique ID as we will store records from multiple databases

  4. checksum the same group of records in the central database

  5. if the checksums match then delete the records from the target database


Because the toolkit deploys the same code to the target and the central database and because the PUL runs from the central database we at first ran the checksum in the central database for the remote records in the audit tables and also the local records in the same central database. We recently because aware of a performance issue and this was tracked down to the fact that the audit records were checksummed across a database link. The checksum PL/SQL function is in the central database and the SQL used to feed into the checksum is run across a link. A bit of testing reveals that if we instead run the checksum in the target database and the SQL is also local to the target database the performance is improved. This is purely because the check done with remote SQL via a link means that all of the records that feed into the checksum are returned over the link. When we instead call the checksum function remotely then only the checksum itself is returned over the link. Two test cases are here.

1 - checksum as now where the checksum is in the central database and the data checksummed is returned with SQL across a link:

declare
lv_aud$_sql varchar2(32767);
lv_aud$_ch varchar2(200);
lg_date_fmt VARCHAR2(32767) := 'DD-MON-YYYY HH24:MI:SS.FF';
begin
lv_aud$_sql:='select sessionid,entryid,statement,timestamp#,userid,userhost,terminal,action#,'
||'returncode,obj$creator,obj$name,auth$privileges,auth$grantee,new$owner,'
||'new$name,ses$actions,ses$tid,logoff$lread,logoff$pread,logoff$lwrite,logoff$dead,'
||'logoff$time,comment$text,clientid,spare1,spare2,obj$label,ses$label,priv$used,'
||'sessioncpu,ntimestamp#,proxy$sid,user$guid,instance#,process#,xid,auditid,'
||'scn,dbid,sqlbind,sqltext,obj$edition from aud$@atk_12 '
||'where cast(from_tz(ntimestamp#,''00:00'') at local as date) between to_date(''05-JUN-2019 15:00:00'', ''DD-MM-YYYY HH24:MI:SS'') '
||'and to_date(''05-JUN-2019 15:59:59'', ''DD-MM-YYYY HH24:MI:SS'') order by sessionid,entryid asc';
dbms_output.put_line('['||to_char(SYSTIMESTAMP,lg_date_fmt)||'] Before the checksum');
lv_aud$_ch:=pfclatk.checksum(lv_aud$_sql);
dbms_output.put_line('['||to_char(SYSTIMESTAMP,lg_date_fmt)||'] Checksum local checksum() sql across the link: '||lv_aud$_ch);
end;
/

2 - The checksum is now in the remote database and the SQL is local to the remote database:

declare
lv_aud$_sql varchar2(32767);
lv_aud$_ch varchar2(200);
lg_date_fmt VARCHAR2(32767) := 'DD-MON-YYYY HH24:MI:SS.FF';
begin
lv_aud$_sql:='select sessionid,entryid,statement,timestamp#,userid,userhost,terminal,action#,'
||'returncode,obj$creator,obj$name,auth$privileges,auth$grantee,new$owner,'
||'new$name,ses$actions,ses$tid,logoff$lread,logoff$pread,logoff$lwrite,logoff$dead,'
||'logoff$time,comment$text,clientid,spare1,spare2,obj$label,ses$label,priv$used,'
||'sessioncpu,ntimestamp#,proxy$sid,user$guid,instance#,process#,xid,auditid,'
||'scn,dbid,sqlbind,sqltext,obj$edition from aud$ '
||'where cast(from_tz(ntimestamp#,''00:00'') at local as date) between to_date(''05-JUN-2019 15:00:00'', ''DD-MM-YYYY HH24:MI:SS'') '
||'and to_date(''05-JUN-2019 15:59:59'', ''DD-MM-YYYY HH24:MI:SS'') order by sessionid,entryid asc';
dbms_output.put_line('['||to_char(SYSTIMESTAMP,lg_date_fmt)||'] Before the checksum');
lv_aud$_ch:=pfclatk.checksum@atk_12(lv_aud$_sql);
dbms_output.put_line('['||to_char(SYSTIMESTAMP,lg_date_fmt)||'] Checksum remote checksum() across the link local sql in the ATK: '||lv_aud$_ch);
end;
/

The difference is subtle as the first example has the link in the SQL and the second on the PL/SQL API. The difference as i said is in the second one only the checksum is transmitted across the wire. The performance is better (albeit i dont have many audit records in my database - so its subtle but better):
1) - run the remote SQL

SQL> set serveroutput on
SQL> @run_chk_remote
[06-JUN-2019 09:10:52.356646000] Before the checksum
[06-JUN-2019 09:10:52.681866000] Checksum local checksum() sql across the link: 1628EC464B590F82FE98A7588492FC0D54145695

PL/SQL procedure successfully completed.

SQL>

2 - remote PL/SQL

SQL> @run_chk_rem_chk.sql
[06-JUN-2019 09:12:00.175714000] Before the checksum
[06-JUN-2019 09:12:00.343161000] Checksum remote checksum() across the link local sql in the ATK: 1628EC464B590F82FE98A7588492FC0D54145695

PL/SQL procedure successfully completed.

SQL>

so for the remote SQL then its 0.33 of a second to process and for the remote checksum its 0.17 of a second. With much bigger data sets then the savings are much bigger. We still need to process all records on the remote checksum and remote SQL (local to the checksum) and feed them into the checksum but we don't feed the data across the wire anymore

Lesson learned; be aware of how the data flows so that we reduce the work. The toolkit has been improved to do the checksum remotely now for each target database.

If anyone is interested in us helping design and implement an audit trail for them very quickly then please let me know via our contact details.

3200 Clever hackers are in my PC; wow!!

Hackers are clever people; they must be to hack other people and take over their private data and steal identities and money. I have to draw the limit at the number of hackers who claim to be in my PC, since October 2018 this is now over 3200 of them. They claim to have videos of me watching websites i didn't visit. Even my main PC doesn't even have a webcam and my laptop usually has the webcam covered with a little blocker window so they must be fantastic hackers to be able to video with no webcam or with a cover over it. They claim to have videos of the victim and that they have installed backdoors in my PC and have access to all social media and email accounts and if you don't pay them large amounts of money via bitcoin they will send out the video to your contacts.

I have collected 3200 of these scam emails since October 2018 till now, mid May 2019 - thats 8 months so an average of 14 hackers a day in my PC. It's madness; these top hackers must actually struggle to not fall over each other when they exploit my PC and each of them has left a backdoor so there must be now 3200 backdoors in my PC; The emails are a joke and some are so badly written that the victim must struggle to actually understand what's beings said.

In the beginning most of these emails included a password. These people claim that they know my email password and in fact none these passwords were ever for my email and are in fact passwords used in various websites that must have been hacked over the years and published on various sites that list peoples passwords. None of the passwords shown are current for me and are actually from many years ago; some from 20 years ago.

The first emails were a little more convincing actually sent to pete at petefinnigan dot com but after a short while the attackers could not be bothered to even send a password that i may recognise. Also they started to send emails to random strings at my domain such asenssw at petefinnigan dot com. Some sent the sender address as the password and claimed that this shows how fantastic a hacker they are. Some simply sent from random most lily spoofed addresses. In the beginning they tried to be more professional and send from my own email address (spoofed of course) and include the password to show me how fantastic they are and how they must have my password to my email - actually it was a password from a website from 20 years ago not my email. I now even receive these same emails in German, Chinese and Russian.

Some have written that they have looked into the bit coin addresses and people do pay these emails. don't pay they are not real; they do not have videos of you. Based on fear; some people pay them. it is a probably a lucrative business generating thousands (millions) of emails and sending them out; a scatter gun approach.

The most interesting ones for me was a password that was more recent quoted in one of these emails - 7 years ago. I used that password only once; all my passwords are unique anyway. I knew i used it to buy one item on a website in the UK and never visited that website again. This password only showed up 4 times whilst others showed up hundreds and hundreds of times. The PC that this password was used on once has been long since gone years ago. It was never used on any current PC and can only ever have come from an attack of that companies website. I contacted them and let them know; they said they were looking into it and asked their web host to check but would not admit (or could find) a breach. It has to come from there as the PC that was used by me went 6 years ago and the password was used once.

This is an area of interest to me. I wrote a book published by Apress on Oracle Incident response and forensics last year and i have been involved in many breaches of Oracle databases in the aftermath and also in helping secure those databases. For me its always interesting to find evidence of a breach in an Oracle database and how the attacker got in, who were they connected as, what did they see and do, what could they have done with the rights they had with more skills and most importantly what was the range of the attack. When did it start and when did it end. what is usually interesting is that usually the attacks lasted much longer than the client thought; they may have believed that the breach stared a week or so ago but it started a year ago. Its also interesting to see patterns and evidence of multiple attackers in an Oracle database that has been breached; i have seen this many times and it has parallels with these ridiculous emails - if we were to believe them then i had 3200 attackers in my PC.. but in real life in Oracle databases its often possible to see that there indeed has been multiple attackers - we can see the style and the patterns to establish this.

I have also been involved in performing security audits of Oracle databases where the client was to aware they had been breached but i found evidence of breaches in the near and sometimes distant past.

So, 3200 hackers are not in my PC i would never have any resources left to use and there are not 3200 backdoors as each attacker would fall over each other to install 3200 backdoors in one PC; madness; but there are parallels to Oracle database breaches, evidence that points to the source of the leak - i.e. this site that i used once with one password. GDPR comes into play as well here!