Last week I visited the Oracle University Event “Expert Summit 2016” in Berlin, Germany. Besides the nice location in the Adlon Kempinski hotel, close to the Brandenburger Tor, I saw very interesting presentations from Jonathan Lewis (Database Troubleshooting and Tuning) and Pete Finnigan (Oracle Database Security Audit Training).
The training from Pete was about how to do a database security audit. On the first day we learned a lot about potential security holes (vulnerabilities) in the database to get access to data. In that context Pete provided a list of places where critical data can be located. You probably might say: “It’s in the database table”. Let’s see… So where is our critical data?
First of all, how is the data getting into the database. Usually
– the user enters data in the application interface.
– the data is turned into SQL statements on the Application Server (insert into credit_card …).
– the data is inserted into the database table perhaps also updating indexes.
After that users query the data and potentially modify it.
So is the data just in the table (e.g. in table CREDIT_CARDS) in memory and on disk then?
Here an incomplete list of places where our “critical data” is or potentially can be:
– SQL Statements are parsed and the SQL, binds and more are written to the shared memory, which is accessible through many views (e.g. v$sql).
– the data blocks required are loaded to the db cache
– the data is in the PGA if sorts are in memory
– the index data is also read into memory blocks
– the data is also held on disk in a datafile – table and index data
– Redo is generated – in memory and on disk
– Archivelogs are generated – also in memory and on disk
– Undo is generated in memory and on disk
– Temp tablespaces can also contain data
– Flashback contains the data – on disk and in memory
– Audit trails can contain data
– Log files generated by the database server can contain data
– Data can be in InMemory columns in the cache
– Trace files (event 10046, 10053, dumps, other events, etc) may contain data
– Backups (which are stored outside the database) contain data
– Datapump exports or old exp dumps may be available in a directory
– In case a standby database is used, the data is sent to the standby database through various processes
– Running with a filesystem and direct IO off the data is in the filesystem cache
– Data is in the storage cache
So the critical data can be at many places, where it can be dumped, copied or accessed through various mechanisms and tools (e.g. Logminer) by different people (depending on the privileges by the DBA, System Admin or a user, who has access to the DB server or can connect to the database).
If you think about securing your data in your database then it’s crucial to know how the database works and where your data can be to know the possibilities potential data thieves do have to get access to your data.