One area I have not covered in previous blogs about Oracle Security and AI is sovereignty of that AI and of course in particular of the data. This means that the control of the hardware (compute and GPU) and the software (the model software and weights) and of course the data that is passed to the models and used by the models is controlled and restricted. Providers try and control all of these aspects so that the data and compute are within the borders of the country.
Oracle offers sovereign AI in terms of its OCI cloud offerings in terms of public cloud and even using OCI cloud in the customers data center.
Why do we need sovereign AI? because if you just used public cloud AI then your data could be stored and used by the AI and in effect you are losing your data to the cloud based AI. So the idea of sovereign AI is to protect your data and company secrets.
You can use commercial sovereign solutions such as Oracle OCI but depending on your needs using a local LLM will offer the best solution as the LLM and hardware and all prompts and data passed to it remain in your server room and on your own network. This is the much better approach as no data leaves your premise. Yes, you do not get the latest Claude or Gemini or Chatgpt BUT a local LLM with RAG and all your documents and web search enabled is a good way to go.
In general there is no audit trail in the same sense as the Oracle database in AI or with AI usage.
But it is obvious; do not paste company data or secrets into a chat interface as this will end up in the cloud. So you need to know if any employee tries this.
Oracle security is all about knowing where the data is and who can access that data and how is that data protected. We also must combine audit trails in the Oracle database with the security and protection around the data that matters. We must not only audit access to the data but also changes to security such as adding or changing users, roles, profiles, privileges
In AI, if we use AI then we must also know what data is passed to AI and how; we also must know if the settings of the AI have changed and when and by who.
Using a local LLM with suitable RAG to fill the gaps between the frontier models and the open free models available for local hosting will allow you to host the AI locally and therefore prevent your data from leaving the premises and not ending up in a cloud based AI. If we cannot do this then use a sovereign AI solution.
Whether the solution is commercial or free most AI solutions have some level of audit controls so that changes to settings, logon/logoff, rag, prompts and results can be audited. For instance AnythingLLM out of the box audits (stores) conversations, workspace history, documents added, users, chats but does not provide an immutable audit trail for configuration changes, plugins or model changes. BUT, because it is open source this could be added.
Bottom line; consider sovereign AI to ensure that your data / code / PII / GDPR / Business logic is not leaked or exfiltrated. Also look at auditing all LLM use in your organisation (not cost of tokens) so that you know what changed and when and what was sent to the LLM and what was returned.
And remember that if you capture prompts in the audit trail then these potentially contain data that should be protected; so protect the AI audit trail.
#oracleace #sym_42 #ai #llm #sovereign #aisecurity
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.
Is AI Like Oracle Security?
My day to day focus is helping customers secure data in their Oracle databases. As you will have seen from a small number of blogs here recently I have been writing a little about AI and in particular local LLMs as I have set up numerous local LLMs on my mac book pro M5 64gb laptop
This AI for me is just for fun and testing and also learning more about AI and how it can help in day to day work with Oracle database and in particular with development in PL/SQL and also even more particularly can it help in securing data in an Oracle database.
As I have said in previous posts, my view is that these local LLMs and even the commercial cloud based LLMs have greater value when used as an expert assistant rather than something to replace someone.
We cannot just come to the office on Monday and say all the DBAs and PL/SQL developers are fired and replace them with agents and LLMs. If an LLM was to take over a role then the person setting up the LLM and agents would need to know the role completely before this could be attempted. Also there is a big risk of edge cases that the LLM is not trained on and guessing or hallucinating is not must use over pressure of a deadline or solving an issue. If the agents/LLM hallucinate then that is dangerous for production. It makes sense that the person who sets up agents and LLMs must be an expert in the area being replaced to make it successful. My experience so far of commercial and free open weights models is that they do not know enough about Oracle. Yes, a lot of this could be solved or made better by pre-loading manuals / documents / code and much more but again the expert is needed for that phase to guide it.
There is a lot of talk around the internet about the wonder of AI and how it is fantastic and useful BUT there is also a growing number of doubts and negative talk. I watched a video yesterday about the issue of energy and water sources. If millions of people lose their jobs to AI then the video posited that there is not enough energy available to supply data centers full of GPUs or enough extra power to cool the GPUs or even enough water to cool them and more. So, if the energy supplies and water are not available how then is every job going to AI.
I would also think that if a lot of jobs need to move to AI where are all the experts in AI, agents etc to help model jobs and make them automated? it is like the time and motion guys of the 60s, 70s, 80s etc that measured each job in a factory to see how long it took and how it could be improved. If jobs truly need to move to AI then the jobs must be understood including edge cases and then set up properly with sufficient data inputs.
I have also read recently that a wide ranging study by MIT media lab showed that 90 - 95% of current corporate AI initiatives fail to deliver a measurable return on investment. In other words the AI did not improve the business and simply cost money; so either there was a net effect or even overall money was lost.
What about the AI companies themselves. Can we (corporate industry) rely on them being around in the future. It ss well covered in the news that they are all investing trillions overall to hyperscale or to build massive data centers but whilst their revenues are enormous for the normal person in the street (tens of billions) its nothing compared to the investment being made and the fact that its well known data centers could be obsolete as soon as they are built. i.e. faster better chips come along then do these massive builds get get ROI as well?
If we want to consider replacing people with AI and agents then we need to ask some basic questions:
How much does the current task cost - materials / people / wages?
What are the protected run time costs to run the AI - subscriptions / tokens?
How much will it cost to build the AI needed - materials / consultants?
What are the on-going maintenance costs to update the AI / prompts / agents etc when needed?
How much to lay off the staff - redundancy, other costs?
Then you can calculate a possible ROI i.e. AI runtime must be less than (costs + current runtime costs for staff)
This should be 10x or even more to make it viable to consider.
This does not even consider the technical aspects of the AI vs the person. Will the AI perform better in terms of technical accuracy than the person over all uses cases or only 20% of all use cases or ? Will a real person still be needed to assess what the AI has done and fix problems.
The value in AI is using it to supplement a persons role where it becomes an expert productivity tool BUT adds value because the tasks speed up but you still have the human input to check and meter the AI use and accuracy.
OK, back to Oracle security; is AI similar or does it have some of the same issues as Oracle security? yes, of course it does. In Oracle security we are not securing Oracle the database; we are securing the data that is held and processed in that database. We of course use Oracle security features and more to secure the data BUT the work is focused on securing data not the database.
In Oracle security I look at how the data flows into a system and out and all the touch points; I also consider the who and how accesses the system and why, what reason do they touch data? When we have this overview we can assess the technical aspects of the database itself, the design and architecture and permissions and access.
AI, in some senses is similar. You purchase a subscription and start to use AI to help but how does it help your company? obviously it can help generally because of its built in training but to help specifically you need to make it aware of your needs and data; to do this your staff start sending questions as prompts and uploading all of your source code, business intelligence, output of reports to get AI to find insights, schedules of staff to plan rotas, accounting information and much much more. Before you know it all of your company secrets are uploaded to the AI. What does the AI do with that? Just do a simple test in an online chat screen and you can see over a number of chats that it remembers your previous chats and even mentions them in the current chat!
This behaviour could affect GDPR, PCI, HIPPA and many other regulations that cover data security. This is like i have told people for over 20 years, do not post source code and secrets to forums and online sites. It is the same idea with AI. Assume that if you ask an AI to fix a bug in your source code then you will give that source code to the AI. You cannot for sure say what that means; is your source code available to others, is the context window only for you or will the AI companies teach the next iteration to include all your and everyone else prompts and history? we do not know for sure.
This is very similar to Oracle security; know where your data is and control complete access to it. This applies to AI
I have heard of apps using AI to allow users to query and question anything in a database that runs the application; Yes great selling point but very bad for security of data.
Do not get me wrong, AI is a great tool and can be used for good in any business BUT think about the security of your data in terms of AI, think about whether AI will cost more than now, will it increase profit, ROI?
Do not just throw AI at everything because you can. I have spoken to a lot of people who say AI is slowing them down but it is now a company requirement so they have to use it. AI is as great tool but you must understand it and know how to use it but just use it because you have to.
The person setting up good AI needs to be an expert in AI and also in the business process or task being replaced or enhanced by AI or be able to ask the right questions to understand it.
Do not forget hallucinations!, as humans we instinctively know if something sounds right or not, an AI just confidently guesses and can be wrong. All the AI companies say they cannot fix this, it is inherent.
#oracleace #sym_42 #ai #llm #oracle #database #security
This AI for me is just for fun and testing and also learning more about AI and how it can help in day to day work with Oracle database and in particular with development in PL/SQL and also even more particularly can it help in securing data in an Oracle database.
As I have said in previous posts, my view is that these local LLMs and even the commercial cloud based LLMs have greater value when used as an expert assistant rather than something to replace someone.
We cannot just come to the office on Monday and say all the DBAs and PL/SQL developers are fired and replace them with agents and LLMs. If an LLM was to take over a role then the person setting up the LLM and agents would need to know the role completely before this could be attempted. Also there is a big risk of edge cases that the LLM is not trained on and guessing or hallucinating is not must use over pressure of a deadline or solving an issue. If the agents/LLM hallucinate then that is dangerous for production. It makes sense that the person who sets up agents and LLMs must be an expert in the area being replaced to make it successful. My experience so far of commercial and free open weights models is that they do not know enough about Oracle. Yes, a lot of this could be solved or made better by pre-loading manuals / documents / code and much more but again the expert is needed for that phase to guide it.
There is a lot of talk around the internet about the wonder of AI and how it is fantastic and useful BUT there is also a growing number of doubts and negative talk. I watched a video yesterday about the issue of energy and water sources. If millions of people lose their jobs to AI then the video posited that there is not enough energy available to supply data centers full of GPUs or enough extra power to cool the GPUs or even enough water to cool them and more. So, if the energy supplies and water are not available how then is every job going to AI.
I would also think that if a lot of jobs need to move to AI where are all the experts in AI, agents etc to help model jobs and make them automated? it is like the time and motion guys of the 60s, 70s, 80s etc that measured each job in a factory to see how long it took and how it could be improved. If jobs truly need to move to AI then the jobs must be understood including edge cases and then set up properly with sufficient data inputs.
I have also read recently that a wide ranging study by MIT media lab showed that 90 - 95% of current corporate AI initiatives fail to deliver a measurable return on investment. In other words the AI did not improve the business and simply cost money; so either there was a net effect or even overall money was lost.
What about the AI companies themselves. Can we (corporate industry) rely on them being around in the future. It ss well covered in the news that they are all investing trillions overall to hyperscale or to build massive data centers but whilst their revenues are enormous for the normal person in the street (tens of billions) its nothing compared to the investment being made and the fact that its well known data centers could be obsolete as soon as they are built. i.e. faster better chips come along then do these massive builds get get ROI as well?
If we want to consider replacing people with AI and agents then we need to ask some basic questions:
How much does the current task cost - materials / people / wages?
What are the protected run time costs to run the AI - subscriptions / tokens?
How much will it cost to build the AI needed - materials / consultants?
What are the on-going maintenance costs to update the AI / prompts / agents etc when needed?
How much to lay off the staff - redundancy, other costs?
Then you can calculate a possible ROI i.e. AI runtime must be less than (costs + current runtime costs for staff)
This should be 10x or even more to make it viable to consider.
This does not even consider the technical aspects of the AI vs the person. Will the AI perform better in terms of technical accuracy than the person over all uses cases or only 20% of all use cases or ? Will a real person still be needed to assess what the AI has done and fix problems.
The value in AI is using it to supplement a persons role where it becomes an expert productivity tool BUT adds value because the tasks speed up but you still have the human input to check and meter the AI use and accuracy.
OK, back to Oracle security; is AI similar or does it have some of the same issues as Oracle security? yes, of course it does. In Oracle security we are not securing Oracle the database; we are securing the data that is held and processed in that database. We of course use Oracle security features and more to secure the data BUT the work is focused on securing data not the database.
In Oracle security I look at how the data flows into a system and out and all the touch points; I also consider the who and how accesses the system and why, what reason do they touch data? When we have this overview we can assess the technical aspects of the database itself, the design and architecture and permissions and access.
AI, in some senses is similar. You purchase a subscription and start to use AI to help but how does it help your company? obviously it can help generally because of its built in training but to help specifically you need to make it aware of your needs and data; to do this your staff start sending questions as prompts and uploading all of your source code, business intelligence, output of reports to get AI to find insights, schedules of staff to plan rotas, accounting information and much much more. Before you know it all of your company secrets are uploaded to the AI. What does the AI do with that? Just do a simple test in an online chat screen and you can see over a number of chats that it remembers your previous chats and even mentions them in the current chat!
This behaviour could affect GDPR, PCI, HIPPA and many other regulations that cover data security. This is like i have told people for over 20 years, do not post source code and secrets to forums and online sites. It is the same idea with AI. Assume that if you ask an AI to fix a bug in your source code then you will give that source code to the AI. You cannot for sure say what that means; is your source code available to others, is the context window only for you or will the AI companies teach the next iteration to include all your and everyone else prompts and history? we do not know for sure.
This is very similar to Oracle security; know where your data is and control complete access to it. This applies to AI
I have heard of apps using AI to allow users to query and question anything in a database that runs the application; Yes great selling point but very bad for security of data.
Do not get me wrong, AI is a great tool and can be used for good in any business BUT think about the security of your data in terms of AI, think about whether AI will cost more than now, will it increase profit, ROI?
Do not just throw AI at everything because you can. I have spoken to a lot of people who say AI is slowing them down but it is now a company requirement so they have to use it. AI is as great tool but you must understand it and know how to use it but just use it because you have to.
The person setting up good AI needs to be an expert in AI and also in the business process or task being replaced or enhanced by AI or be able to ask the right questions to understand it.
Do not forget hallucinations!, as humans we instinctively know if something sounds right or not, an AI just confidently guesses and can be wrong. All the AI companies say they cannot fix this, it is inherent.
#oracleace #sym_42 #ai #llm #oracle #database #security
Can local LLM AI generate the top 100 most common passwords?
I am working on a simple password audit tool for Oracle Apex and I have written a simple password cracker in PL/SQL to test if a password is found or not; i.e. is the password weak or not. To allow me to operate this I needed a simple list of common weak passwords so I thought I would ask my local LLM to give me a list. I have lists already so this was a test of my local LLMs.
I first ran all the sample models via ollama, docker, open-webui. I asked each model the same prompt which was please give me the top 100 most common weak passwords for use in an Oracle security tool. Ollama, docker and open-webui are running on my mac book pro M5 with 64gb of ram.
I tested:
I of course tested chatpgt free website chat version as well and that did give the proper top 100 common weakest passwords and also advice on permutation ideas and also stated it got the list from various hacked password lists / websites
I decided to then run some of the models direct in ollama on my mac book so avoiding the open-webui interface:
The LLMs when run through open-webui all failed to give the top 10 weak passwords or timed out or did not work due to tools. A small number worked via ollama direct on the mac book but for instance Doplhin was just made up nonsense, so hallucinating. Gemma3:27b was the best but most likely out of date based on when the model originated. Open-webui blocked the requests but as some of the models worked or at least gave better background means open-webui can be modified to not pass in as much system prompt, knowledge or other pre-determined memory.
I do not know without further experiment whether the answer NO can be bypassed but at least Gemma3 can be used for this task locally without further work and the data was decent
For this particular task we did not need AI, it is much better to search for lists of weak passwords on the net and simply download a list BUT it was an interesting test.
The use of local LLMs has shown for me that some models are better for general questions, some better for code, some better for security and some have had their restraints removed such as Dolphin.
It would be useful in some cases to limit what open-webui does in terms of system prompt and memory but in other cases for instance to be an expert then it is useful to use skills files or other knowledge.
With all the recent news on AI sovereignty its useful to run local LLMs so that the commercial engines do not keep your history or prompts or data passed in to use.
#oracleace #sym_42 #oracle #security #ai #sovereignty #llm #local #dataprotection
I first ran all the sample models via ollama, docker, open-webui. I asked each model the same prompt which was please give me the top 100 most common weak passwords for use in an Oracle security tool. Ollama, docker and open-webui are running on my mac book pro M5 with 64gb of ram.
I tested:
qwen3-coder said no, sorry I cant do that, its not ethical
Deepseek-r1:32b said the same that its not ethical again
llama3.3:70b also said no BUT it took 19 minutes and lots of thinking to come up with the same answer
llama3.1:8b was much faster than its sibling but also said no
gemma3:27b failed in open-webui as it does not support tools
Dolphin3-latest also failed in open-webui because it does not
I of course tested chatpgt free website chat version as well and that did give the proper top 100 common weakest passwords and also advice on permutation ideas and also stated it got the list from various hacked password lists / websites
I decided to then run some of the models direct in ollama on my mac book so avoiding the open-webui interface:
I ran dolphin3:latest direct via ollama and now works BUT the list is not good; the first 19 passwords are clearly weak passwords but from 20 to 100 they are just names. The LLM has just decided to make up a list of weak passwords not actual weak passwords.
I ran qwen3-coder:30b from the command line in ollama and it still said it cant give me passwords and suggested other resources for password verify functions and rules.
I ran llama3.1:8b direct in ollama did not say no but gave me examples of weak passwords and rules not to use, i.e. patterns and advice on suing a password manager to generate 12 character random passwords.
I ran deepseek-r1:32b direct in ollama and it gave a lot of text about how it should not give me a list and advised me to use reputable cyber security sources and also have advice on 2FA and MFA and others BUT it still would not give me a list
I ran gemma3:27b direct in ollama and it worked best to give me a list of passwords and advice on permutations and also it stated the list is historic.
The LLMs when run through open-webui all failed to give the top 10 weak passwords or timed out or did not work due to tools. A small number worked via ollama direct on the mac book but for instance Doplhin was just made up nonsense, so hallucinating. Gemma3:27b was the best but most likely out of date based on when the model originated. Open-webui blocked the requests but as some of the models worked or at least gave better background means open-webui can be modified to not pass in as much system prompt, knowledge or other pre-determined memory.
I do not know without further experiment whether the answer NO can be bypassed but at least Gemma3 can be used for this task locally without further work and the data was decent
For this particular task we did not need AI, it is much better to search for lists of weak passwords on the net and simply download a list BUT it was an interesting test.
The use of local LLMs has shown for me that some models are better for general questions, some better for code, some better for security and some have had their restraints removed such as Dolphin.
It would be useful in some cases to limit what open-webui does in terms of system prompt and memory but in other cases for instance to be an expert then it is useful to use skills files or other knowledge.
With all the recent news on AI sovereignty its useful to run local LLMs so that the commercial engines do not keep your history or prompts or data passed in to use.
#oracleace #sym_42 #oracle #security #ai #sovereignty #llm #local #dataprotection
Cluster Objects in the Oracle Database
Sometimes in forensic analysis of an Oracle database it is necessary to understand how data is stored on disk. Actually we must understand that if we were to create our own database we might start with a file that holds the data, maybe storing the data just as text lines or as JSON or XML or whatever. Eventually for speed we might place the data in fixed blocks and to allow query of that data we might also store a description of that data storage also in the same datafile.
The Oracle database is incredibly complex and most likely millions of lines of C code but at the deepest depths its just a set of files on disk that holds our data. The Oracle software allows us to query and retrieve or update our data from those files. BUT, at the simplest level the data is just stored in files with incredibly complex software to retrieve and work with that data.
At a high level we store data in tables and maybe we use indexes to make finding certain records faster. These structures are built into meta data that allows access to the right blocks of data. In general one object is stored in each data block but sometimes Oracle wants to access two objects in a block to make the access more efficient. This is achieved with a cluster. First a cluster is created and then multiple tables can be added to the clustered storage all indexed by a key; an indexed column. This is not an index the Oracle traditional sense; it is a column that all rows of each table type possess so that the rows from different tables can be accessed faster / together
The SYS.USER$ table is in a cluster. The cluster is created in dcore.bsq which you can find on the database server:
There are 3 objects in this c_user# cluster; the i_user# index, user$ table itself and the tsq$ the tablespace quotas table.
We can check what clusters exist:
As we can see the c_user# cluster is an INDEX type. There are two types of cluster, an INDEX type which uses a B tree index or a HASH which uses a hash function which also must be provided. What is the purpose of a cluster? in a normal case a data block holds one object such as table. If there are regular joins between two tables such as USER$.USER# and TSQ$.USER# then creating a cluster allows rows from both tables to be stored in the same data block. This allows faster access to the linked data as only one block visit is required for both tables indexed on the USER# column.
We are interested in this as during forensics investigations we often focus on dictionary tables and in particular the bootstrap tables that are loaded from fixed positions. Oracle has to bootstrap itself into a running instance and most SQL involved recursive SQL that queries user$, obj$ and more. These tables have to be loaded first to allow any more SQL to run as it uses these tables first in recursive SQL. As these tables such as USER$ and OBJ$ are accessed in almost all SQL statements in the background then it makes sense to cluster them.
Can we create or own clusters?
NOTE: I have Database Vault enabled in this database hence the need to use c##acco to create a user
Now create a sample cluster:
Now check DBA_CLSUERS to see the entry:
We can see that the cluster is INDEX type and the index size is 512 as specified.
A table is still a table BUT we can group tables together in a cluster so that they are stored in the same data block for speed
What about the key we specified in the creation of the cluster and also used when we placed tables in a cluster?
This show the index column per table that are in the cluster.
The cluster key is not visible in DBA_CLUSTERING_KEYS or DBA_CLUSTERS, so how can we find the name of the key independent of the table definition?
We can use DBMS_METADATA.GET_DDL to get the cluster index column without respect to tables in the cluser:
Because the DDL can be re-constructed the index column of the cluster has to be stored somewhere in the data dictionary. The cluster index is not exposed in DBA_CLUSTERS and the DBA_CLU_COLUMNS so we have to get it from the base tables:
Understanding the internals of how some data is stored and in this case for clustered objects is important for forensics analysis of an Oracle database that may have been breached or in general to understand how features work in the database
#oracleace #sym_42 #oracle #forensics #database #security #internals #clusters
The Oracle database is incredibly complex and most likely millions of lines of C code but at the deepest depths its just a set of files on disk that holds our data. The Oracle software allows us to query and retrieve or update our data from those files. BUT, at the simplest level the data is just stored in files with incredibly complex software to retrieve and work with that data.
At a high level we store data in tables and maybe we use indexes to make finding certain records faster. These structures are built into meta data that allows access to the right blocks of data. In general one object is stored in each data block but sometimes Oracle wants to access two objects in a block to make the access more efficient. This is achieved with a cluster. First a cluster is created and then multiple tables can be added to the clustered storage all indexed by a key; an indexed column. This is not an index the Oracle traditional sense; it is a column that all rows of each table type possess so that the rows from different tables can be accessed faster / together
The SYS.USER$ table is in a cluster. The cluster is created in dcore.bsq which you can find on the database server:
...
create cluster c_user#(user# number)
size 372 /* cluster key ~ 20, sizeof(user$) ~ 227, 5 * sizeof(tsq$) ~ 125 */
/
create index i_user# on cluster c_user#
/
...
create table user$ /* user table */
( user# number not null, /* user identifier number */
name varchar2("M_IDEN") not null, /* name of user */
...
)
cluster c_user#(user#)
/
create table tsq$ /* tablespace quota table */
( ts# number not null, /* tablespace number */
user# number not null, /* user number */
grantor# number not null, /* grantor id */
blocks number not null, /* number of blocks charged to user */
maxblocks number, /* user's maximum number of blocks, NULL if none */
priv1 number not null, /* reserved for future privilege */
priv2 number not null, /* reserved for future privilege */
priv3 number not null) /* reserved for future privilege */
cluster c_user# (user#)
/
There are 3 objects in this c_user# cluster; the i_user# index, user$ table itself and the tsq$ the tablespace quotas table.
We can check what clusters exist:
SQL> set serveroutput on
SQL> @sc_print 'select * from dba_clusters'
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_clusters','''','''''');
Executing Query [select * from dba_clusters]
...
-------------------------------------------
OWNER : SYS
CLUSTER_NAME : C_USER#
TABLESPACE_NAME : SYSTEM
PCT_FREE : 10
PCT_USED : 40
KEY_SIZE : 372
INI_TRANS : 2
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
FREELISTS : 1
FREELIST_GROUPS : 1
AVG_BLOCKS_PER_KEY :
CLUSTER_TYPE : INDEX
FUNCTION :
HASHKEYS : 0
DEGREE : 1
INSTANCES : 1
CACHE : N
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
SINGLE_TABLE : N
DEPENDENCIES : DISABLED
...
As we can see the c_user# cluster is an INDEX type. There are two types of cluster, an INDEX type which uses a B tree index or a HASH which uses a hash function which also must be provided. What is the purpose of a cluster? in a normal case a data block holds one object such as table. If there are regular joins between two tables such as USER$.USER# and TSQ$.USER# then creating a cluster allows rows from both tables to be stored in the same data block. This allows faster access to the linked data as only one block visit is required for both tables indexed on the USER# column.
We are interested in this as during forensics investigations we often focus on dictionary tables and in particular the bootstrap tables that are loaded from fixed positions. Oracle has to bootstrap itself into a running instance and most SQL involved recursive SQL that queries user$, obj$ and more. These tables have to be loaded first to allow any more SQL to run as it uses these tables first in recursive SQL. As these tables such as USER$ and OBJ$ are accessed in almost all SQL statements in the background then it makes sense to cluster them.
Can we create or own clusters?
SQL> connect c##acco/oracle1@//192.168.56.33:1539/xepdb1
Connected.
SQL> create user cluster_user identified by cluster_user;
User created.
SQL> grant create session to cluster_user;
Grant succeeded.
SQL> connect sys/oracle1@//192.168.56.33:1539/xepdb1 as sysdba
Connected.
SQL> grant create table to cluster_user;
Grant succeeded.
SQL> grant create cluster to cluster_user;
Grant succeeded.
SQL> grant unlimited tablespace to cluster_user;
Grant succeeded.
SQL>
NOTE: I have Database Vault enabled in this database hence the need to use c##acco to create a user
Now create a sample cluster:
SQL> connect cluster_user/cluster_user@//192.168.56.33:1539/xepdb1
Connected.
SQL> create cluster c_pete(id number) size 512;
Cluster created.
SQL> create table test_1 (id number, name varchar2(100)) cluster c_pete(id);
Table created.
SQL> create table test_2 (id number, address varchar2(100)) cluster c_pete(id);
Table created.
SQL>
Now check DBA_CLSUERS to see the entry:
SQL> connect sys/oracle1@//192.168.56.33:1539/xepdb1 as sysdba
Connected.
SQL> set serveroutput on
SQL> @sc_print 'select * from dba_clusters where cluster_name=''''C_PETE'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_clusters where cluster_name=''C_PETE''','''','''''');
Executing Query [select * from dba_clusters where cluster_name='C_PETE']
OWNER : CLUSTER_USER
CLUSTER_NAME : C_PETE
TABLESPACE_NAME : USERS
PCT_FREE : 10
PCT_USED :
KEY_SIZE : 512
INI_TRANS : 2
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
FREELISTS :
FREELIST_GROUPS :
AVG_BLOCKS_PER_KEY :
CLUSTER_TYPE : INDEX
FUNCTION :
HASHKEYS : 0
DEGREE : 1
INSTANCES : 1
CACHE : N
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
SINGLE_TABLE : N
DEPENDENCIES : DISABLED
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
We can see that the cluster is INDEX type and the index size is 512 as specified.
A table is still a table BUT we can group tables together in a cluster so that they are stored in the same data block for speed
What about the key we specified in the creation of the cluster and also used when we placed tables in a cluster?
SQL> col cluster_name for a10
SQL> col clu_column_name for a10
SQL> col table_name for a10
SQL> col tab_column_name for a10
SQL> set lines 220
SQL> select cluster_name,clu_column_name,table_name,tab_column_name from dba_clu_columns where owner='CLUSTER_USER';
CLUSTER_NA CLU_COLUMN TABLE_NAME TAB_COLUMN
---------- ---------- ---------- ----------
C_PETE ID TEST_2 ID
C_PETE ID TEST_1 ID
SQL>
This show the index column per table that are in the cluster.
The cluster key is not visible in DBA_CLUSTERING_KEYS or DBA_CLUSTERS, so how can we find the name of the key independent of the table definition?
We can use DBMS_METADATA.GET_DDL to get the cluster index column without respect to tables in the cluser:
SQL> SELECT DBMS_METADATA.GET_DDL('CLUSTER','C_PETE','CLUSTER_USER') from dual;
DBMS_METADATA.GET_DDL('CLUSTER','C_PETE','CLUSTER_USER')
--------------------------------------------------------------------------------
CREATE CLUSTER "CLUSTER_USER"."C_PETE" (
"ID" NUMBER )
SIZE 512
PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARALLEL (DEGREE 1 INSTANCES 1)
SQL>
Because the DDL can be re-constructed the index column of the cluster has to be stored somewhere in the data dictionary. The cluster index is not exposed in DBA_CLUSTERS and the DBA_CLU_COLUMNS so we have to get it from the base tables:
SQL> col owner# for 9999
SQL> col cluster_name for a10
SQL> col col# for 99
SQL> col cluster_key_column for a10
SQL> col type# for 999
SQL> SELECT o.owner#,
2 o.name AS cluster_name,
3 c.col#,
4 c.name AS cluster_key_column,
5 c.type#
6 FROM sys.obj$ o
7 JOIN sys.col$ c
8 ON o.obj# = c.obj#
9 WHERE o.type# = 3 -- cluster object
10 AND o.name = 'C_PETE';
OWNER# CLUSTER_NA COL# CLUSTER_KE TYPE#
------ ---------- ---- ---------- -----
513 C_PETE 1 ID 2
SQL>
Understanding the internals of how some data is stored and in this case for clustered objects is important for forensics analysis of an Oracle database that may have been breached or in general to understand how features work in the database
#oracleace #sym_42 #oracle #forensics #database #security #internals #clusters
AI Comparison for Oracle Security Code Generation
I am running a Mac Book Pro M5 with 64gb of RAM and a 2tb SSD. I wanted to use local LLMs to generate Oracle security related PL/SQL to see how good these models are at generating PL/SQL code and also how they perform against each other in terms of elapsed time and also tokens per second. In AI we can measure the speed of the AI in elapsed time and also tokens per second. Tokens are the chunks of text that the LLM then processes. They can be words or parts of words.
I tested three models, qwen2.5-coder, qwen3-coder:30b and qwen3-coder-next.
I used the same prompt for each of the models. The prompt is:
I ran qwen2.5-coder and it generated this text at the start:
and it went on to generate a PL/SQL package header, package body and some example use cases of the resultant code. The header generated is:
The package body generated is:
The example usage code is:
No privileges are generated for DBMS_CRYPTO or UTL_ENCODE so we will do these manually at the end but first what did qwen3-coder:30b generate?
Here's a complete PL/SQL package for encryption/decryption with Base64 encoding/decoding. It generated the package header and body together unlike qwen2.5-coder:
Some sample usage code was generated:
As with qwen2.5-coder this qwen3-coder also did not generate the grants needed to run this code.
OK, lets put the 2.5 and 3 code generated into scripts and see if this will compile and run. In both cases I first granted CREATE PROCEDURE, EXECUTE ON DBMS_CRYPTO and EXECUTE ON UTL_ENCODE and created sample users.
I created qwen25.sql and qwen3.sql and added the exact package headers and bodies generated by each LLM model. I have had to add code to create users in each case, either qwen25 or qwen3 and granted privileges to allow the packages to be installed and used. The code for qwen25 for instance to create the user and make grants is:
I also added show err for each of the package header and body creations for each code generated by the LLM. I also did not include the demos of each package as we are interested initially as to whether the code compiles or not.
Now let us try and run qwen25.sql
The first error at line 21 is because the code shows:
And UTL_ENCODE.BASE64_ENCODE() only accepts one parameter, the data itself. Even checking Oracle 11 this function did not have an extra parameter. The next error is line 52:
The global constants are wrong; for instance DBMS_CRYPTO.AES256 should be DBMS_CRYPTO.ENCRYPT_AES256 and DBMS_CRYPTO.CBC_PKCS5 should probably be DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5.
The next two errors are for RAW_TO_STRING() for instance:
The function RAW_TO_STRING() does not exist. It should probably be RAW_TO_CHAR() that returns a VARCHAR2.
OK, so a few errors, let us now try the qwen3 generated code by running my qwen3.sql script:
Almost the same errors but less; the AES256 constant needs to be changed as well as its compatriots around PKCS5 and CBV the same as the 2.5 model. Also the same RAW_TO_STRING() function needs to be changed.
I am not going to fix them as part of this demo as I have learned enough on this case This was an experiment to see if we can use 3 different LLM models to generate PL/SQL code to encrypt and decrypt data in an Oracle database. A reminder of the prompt I used is here
I thought I was clear; but maybe not. I wanted to have an encrypt() and decrypt() function that privately converted the encrypted data to BASE64 and on decrypt accept BASE64 data and decode it first and then decrypt. In other words only two public functions; encrypt and decrypt that are public. There is a combined function BUT I would have left the BASE64 functions private.
So, we had three LLMs and one failed and fell over; this was qwen3-coder-next. This ran for 51 minutes before falling over completely in the open-webui interface and where it did not generate any code. So this is not included further here.
The qwen2.5-coder LLM took 3723 ms to run and complete generating the code; which is 3.73 seconds. The model was processing at around 24 tokens per second. Not shabby.
The qwen3-coder:30b LLM took 1023 ms which is 1.023 seconds and was processing 134 tokens per second.
In summary, qwen3-coder-next is not useful at least on my mac; it did do a lot of reasoning before it failed so is clearly a better LLM for deeper analysis BUT if it failed then this does not matter. Qwen3-coder:30b ran in time in one third of the time of qwen2.5-coder and in terms of tokens per second it was processing 5.58 tokens per second faster than the 2.5 model.
So, qwen3-coder:30b is clearly faster than qwen2.5-coder and its code that it generated is slightly less broken than the 2.5 model BUT both produced code that did not work. I could have fed the output from each back into the LLM and it may have helped but the purpose of this post was to show two things; 1) the speed of three models compared to each other to generate PL/SQL and the quality of the code.
As I have said in my last blog. I think these models are more useful if you already know what you are doing. They can be productivity assistants and get you some way along the process but the code in each case needed tweaking/fixing but certainly the qwen3-coder:30b is fast enough for daily use.
#oracleace #sym_42 #ai #llm #qwen3-coder #plsql #oracle #security #generate #code
I tested three models, qwen2.5-coder, qwen3-coder:30b and qwen3-coder-next.
I used the same prompt for each of the models. The prompt is:
please write a PL/SQL public package procedures and any supporting private procedures and functions where that public procedure can encrypt and decrypt data passed and base64 encode/decode the result
I ran qwen2.5-coder and it generated this text at the start:
Certainly! Below is a complete PL/SQL package named encrypt_decrypt_pkg that includes public procedures for encrypting and decrypting data, as well as base64 encoding and decoding. The package uses AES256 encryption with CBC mode and PKCS#5 padding.
and it went on to generate a PL/SQL package header, package body and some example use cases of the resultant code. The header generated is:
CREATE OR REPLACE PACKAGE encrypt_decrypt_pkg AS
-- Public procedure to encrypt data and base64 encode it
PROCEDURE encrypt_and_encode(
p_data IN VARCHAR2,
p_encrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
);
-- Public procedure to decrypt base64 decoded data
PROCEDURE decode_and_decrypt(
p_encrypted_data IN VARCHAR2,
p_decrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
);
-- Public procedure to base64 encode data
PROCEDURE base64_encode(
p_data IN VARCHAR2,
p_encoded_data OUT VARCHAR2
);
-- Public procedure to base64 decode data
PROCEDURE base64_decode(
p_encoded_data IN VARCHAR2,
p_decoded_data OUT VARCHAR2
);
END encrypt_decrypt_pkg;
/
The package body generated is:
CREATE OR REPLACE PACKAGE BODY encrypt_decrypt_pkg AS
-- Private function to generate a key for encryption
FUNCTION generate_key(p_key IN VARCHAR2 DEFAULT NULL) RETURN RAW IS
v_key RAW(32);
BEGIN
IF p_key IS NOT NULL THEN
-- Use provided key and hash it to 256 bits
v_key := DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(p_key, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH256);
ELSE
-- Generate a random key
v_key := DBMS_CRYPTO.RANDOMBYTES(32);
END IF;
RETURN v_key;
END generate_key;
-- Private function to base64 encode raw data
FUNCTION base64_encode_raw(p_data IN RAW) RETURN VARCHAR2 IS
v_encoded RAW(32767);
BEGIN
v_encoded := UTL_ENCODE.BASE64_ENCODE(p_data, TRUE);
RETURN UTL_RAW.CAST_TO_VARCHAR2(v_encoded);
END base64_encode_raw;
-- Private function to base64 decode string to raw data
FUNCTION base64_decode_raw(p_data IN VARCHAR2) RETURN RAW IS
v_decoded RAW(32767);
BEGIN
v_decoded := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(p_data));
RETURN v_decoded;
END base64_decode_raw;
-- Public procedure to encrypt data and base64 encode it
PROCEDURE encrypt_and_encode(
p_data IN VARCHAR2,
p_encrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
) IS
v_key RAW(32);
v_iv RAW(16); -- Initialization Vector for AES-256-CBC
v_encrypted_raw RAW(32767);
BEGIN
-- Generate encryption key
v_key := generate_key(p_key);
-- Generate a random initialization vector (IV)
v_iv := DBMS_CRYPTO.RANDOMBYTES(16);
-- Encrypt data using AES256 in CBC mode with PKCS#5 padding
v_encrypted_raw := DBMS_CRYPTO.ENCRYPT(
src => UTL_I18N.STRING_TO_RAW(p_data, 'AL32UTF8'),
typ => DBMS_CRYPTO.AES256 + DBMS_CRYPTO.CBC_PKCS5,
key => v_key,
iv => v_iv
);
-- Prepend the IV to the encrypted data and base64 encode it
p_encrypted_data := base64_encode_raw(v_iv || v_encrypted_raw);
END encrypt_and_encode;
-- Public procedure to decrypt base64 decoded data
PROCEDURE decode_and_decrypt(
p_encrypted_data IN VARCHAR2,
p_decrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
) IS
v_key RAW(32);
v_iv RAW(16); -- Initialization Vector for AES-256-CBC
v_combined_raw RAW(32767);
v_decrypted_raw RAW(32767);
BEGIN
-- Generate decryption key
v_key := generate_key(p_key);
-- Base64 decode the input data
v_combined_raw := base64_decode_raw(p_encrypted_data);
-- Extract the IV from the beginning of the combined data
v_iv := UTL_RAW.SUBSTR(v_combined_raw, 1, 16);
-- Extract the encrypted data after the IV
v_decrypted_raw := UTL_RAW.SUBSTR(v_combined_raw, 17);
-- Decrypt the data using AES256 in CBC mode with PKCS#5 padding
p_decrypted_data := UTL_I18N.RAW_TO_STRING(
DBMS_CRYPTO.DECRYPT(
src => v_decrypted_raw,
typ => DBMS_CRYPTO.AES256 + DBMS_CRYPTO.CBC_PKCS5,
key => v_key,
iv => v_iv
),
'AL32UTF8'
);
END decode_and_decrypt;
-- Public procedure to base64 encode data
PROCEDURE base64_encode(
p_data IN VARCHAR2,
p_encoded_data OUT VARCHAR2
) IS
v_raw_data RAW(32767);
BEGIN
-- Convert string to raw data
v_raw_data := UTL_I18N.STRING_TO_RAW(p_data, 'AL32UTF8');
-- Base64 encode the raw data
p_encoded_data := base64_encode_raw(v_raw_data);
END base64_encode;
-- Public procedure to base64 decode data
PROCEDURE base64_decode(
p_encoded_data IN VARCHAR2,
p_decoded_data OUT VARCHAR2
) IS
v_decoded_raw RAW(32767);
BEGIN
-- Base64 decode the input data
v_decoded_raw := base64_decode_raw(p_encoded_data);
-- Convert raw data back to string
p_decoded_data := UTL_I18N.RAW_TO_STRING(v_decoded_raw, 'AL32UTF8');
END base64_decode;
END encrypt_decrypt_pkg;
/
The example usage code is:
-- Example 1: Encrypt and encode data
DECLARE
v_encrypted VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.encrypt_and_encode(
p_data => 'Hello World',
p_encrypted_data => v_encrypted,
p_key => 'my_secret_key'
);
DBMS_OUTPUT.PUT_LINE('Encrypted and encoded: ' || v_encrypted);
END;
/
-- Example 2: Decode and decrypt data
DECLARE
v_decrypted VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.decode_and_decrypt(
p_encrypted_data => 'your_encrypted_string_here',
p_decrypted_data => v_decrypted,
p_key => 'my_secret_key'
);
DBMS_OUTPUT.PUT_LINE('Decrypted data: ' || v_decrypted);
END;
/
-- Example 3: Simple base64 encoding
DECLARE
v_encoded VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.base64_encode(
p_data => 'Hello World',
p_encoded_data => v_encoded
);
DBMS_OUTPUT.PUT_LINE('Base64 encoded: ' || v_encoded);
END;
/
-- Example 4: Simple base64 decoding
DECLARE
v_decoded VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.base64_decode(
p_encoded_data => 'SGVsbG8gV29ybGQ=',
p_decoded_data => v_decoded
);
DBMS_OUTPUT.PUT_LINE('Base64 decoded: ' || v_decoded);
END;
/
No privileges are generated for DBMS_CRYPTO or UTL_ENCODE so we will do these manually at the end but first what did qwen3-coder:30b generate?
Here's a complete PL/SQL package for encryption/decryption with Base64 encoding/decoding. It generated the package header and body together unlike qwen2.5-coder:
CREATE OR REPLACE PACKAGE encrypt_decrypt_pkg AS
-- Public procedure to encrypt data and base64 encode it
PROCEDURE encrypt_and_encode(
p_data IN VARCHAR2,
p_encrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
);
-- Public procedure to decrypt base64 decoded data
PROCEDURE decode_and_decrypt(
p_encrypted_data IN VARCHAR2,
p_decrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
);
-- Public procedure to base64 encode data
PROCEDURE base64_encode(
p_data IN VARCHAR2,
p_encoded_data OUT VARCHAR2
);
-- Public procedure to base64 decode data
PROCEDURE base64_decode(
p_encoded_data IN VARCHAR2,
p_decoded_data OUT VARCHAR2
);
END encrypt_decrypt_pkg;
/
CREATE OR REPLACE PACKAGE BODY encrypt_decrypt_pkg AS
-- Private function to generate a key for encryption
FUNCTION generate_key(p_key IN VARCHAR2 DEFAULT NULL) RETURN RAW IS
v_key RAW(32);
BEGIN
IF p_key IS NOT NULL THEN
-- Use provided key
v_key := DBMS_CRYPTO.HASH(p_key, DBMS_CRYPTO.HASH_SH256);
ELSE
-- Generate a random key
v_key := DBMS_CRYPTO.RANDOMBYTES(32);
END IF;
RETURN v_key;
END generate_key;
-- Private function to base64 encode raw data
FUNCTION base64_encode_raw(p_data IN RAW) RETURN VARCHAR2 IS
v_encoded VARCHAR2(32767);
BEGIN
v_encoded := UTL_ENCODE.BASE64_ENCODE(p_data);
RETURN v_encoded;
END base64_encode_raw;
-- Private function to base64 decode string to raw data
FUNCTION base64_decode_raw(p_data IN VARCHAR2) RETURN RAW IS
v_decoded RAW(32767);
BEGIN
v_decoded := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(p_data));
RETURN v_decoded;
END base64_decode_raw;
-- Public procedure to encrypt data and base64 encode it
PROCEDURE encrypt_and_encode(
p_data IN VARCHAR2,
p_encrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
) IS
v_key RAW(32);
v_encrypted_raw RAW(32767);
BEGIN
-- Generate encryption key
v_key := generate_key(p_key);
-- Encrypt data using AES256 in CBC mode with PKCS#5 padding
v_encrypted_raw := DBMS_CRYPTO.ENCRYPT(
src => UTL_I18N.STRING_TO_RAW(p_data, 'AL32UTF8'),
typ => DBMS_CRYPTO.AES256 + DBMS_CRYPTO.CBC + DBMS_CRYPTO.PKCS5,
key => v_key
);
-- Base64 encode the encrypted data
p_encrypted_data := base64_encode_raw(v_encrypted_raw);
END encrypt_and_encode;
-- Public procedure to decrypt base64 decoded data
PROCEDURE decode_and_decrypt(
p_encrypted_data IN VARCHAR2,
p_decrypted_data OUT VARCHAR2,
p_key IN VARCHAR2 DEFAULT NULL
) IS
v_key RAW(32);
v_decrypted_raw RAW(32767);
BEGIN
-- Generate decryption key
v_key := generate_key(p_key);
-- Base64 decode the input data
v_decrypted_raw := base64_decode_raw(p_encrypted_data);
-- Decrypt the data using AES256 in CBC mode with PKCS#5 padding
p_decrypted_data := UTL_I18N.RAW_TO_STRING(
DBMS_CRYPTO.DECRYPT(
src => v_decrypted_raw,
typ => DBMS_CRYPTO.AES256 + DBMS_CRYPTO.CBC + DBMS_CRYPTO.PKCS5,
key => v_key
),
'AL32UTF8'
);
END decode_and_decrypt;
-- Public procedure to base64 encode data
PROCEDURE base64_encode(
p_data IN VARCHAR2,
p_encoded_data OUT VARCHAR2
) IS
v_raw_data RAW(32767);
BEGIN
-- Convert string to raw data
v_raw_data := UTL_I18N.STRING_TO_RAW(p_data, 'AL32UTF8');
-- Base64 encode the raw data
p_encoded_data := base64_encode_raw(v_raw_data);
END base64_encode;
-- Public procedure to base64 decode data
PROCEDURE base64_decode(
p_encoded_data IN VARCHAR2,
p_decoded_data OUT VARCHAR2
) IS
v_decoded_raw RAW(32767);
BEGIN
-- Base64 decode the input data
v_decoded_raw := base64_decode_raw(p_encoded_data);
-- Convert raw data back to string
p_decoded_data := UTL_I18N.RAW_TO_STRING(v_decoded_raw, 'AL32UTF8');
END base64_decode;
END encrypt_decrypt_pkg;
/
Some sample usage code was generated:
-- Example 1: Encrypt and encode data
DECLARE
v_encrypted VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.encrypt_and_encode(
p_data => 'Hello World',
p_encrypted_data => v_encrypted,
p_key => 'my_secret_key'
);
DBMS_OUTPUT.PUT_LINE('Encrypted and encoded: ' || v_encrypted);
END;
/
-- Example 2: Decode and decrypt data
DECLARE
v_decrypted VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.decode_and_decrypt(
p_encrypted_data => 'your_encrypted_string_here',
p_decrypted_data => v_decrypted,
p_key => 'my_secret_key'
);
DBMS_OUTPUT.PUT_LINE('Decrypted data: ' || v_decrypted);
END;
/
-- Example 3: Simple base64 encoding
DECLARE
v_encoded VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.base64_encode(
p_data => 'Hello World',
p_encoded_data => v_encoded
);
DBMS_OUTPUT.PUT_LINE('Base64 encoded: ' || v_encoded);
END;
/
-- Example 4: Simple base64 decoding
DECLARE
v_decoded VARCHAR2(4000);
BEGIN
encrypt_decrypt_pkg.base64_decode(
p_encoded_data => 'SGVsbG8gV29ybGQ=',
p_decoded_data => v_decoded
);
DBMS_OUTPUT.PUT_LINE('Base64 decoded: ' || v_decoded);
END;
/
As with qwen2.5-coder this qwen3-coder also did not generate the grants needed to run this code.
OK, lets put the 2.5 and 3 code generated into scripts and see if this will compile and run. In both cases I first granted CREATE PROCEDURE, EXECUTE ON DBMS_CRYPTO and EXECUTE ON UTL_ENCODE and created sample users.
I created qwen25.sql and qwen3.sql and added the exact package headers and bodies generated by each LLM model. I have had to add code to create users in each case, either qwen25 or qwen3 and granted privileges to allow the packages to be installed and used. The code for qwen25 for instance to create the user and make grants is:
connect sys/oracle@//192.168.56.34:1521/freepdb1 as sysdba
drop user qwen25 cascade;
create user qwen25 identified by qwen25;
grant create session to qwen25;
grant create procedure to qwen25;
grant execute on dbms_crypto to qwen25;
grant execute on utl_encode to qwen25;
connect qwen25/qwen25@//192.168.56.34:1521/freepdb1
set serveroutput on
I also added show err for each of the package header and body creations for each code generated by the LLM. I also did not include the demos of each package as we are interested initially as to whether the code compiles or not.
Now let us try and run qwen25.sql
SQL> @qwen25
Connected.
User dropped.
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Connected.
Package created.
No errors.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY ENCRYPT_DECRYPT_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
21/9 PL/SQL: Statement ignored
21/22 PLS-00306: wrong number or types of arguments in call to
'BASE64_ENCODE'
50/9 PL/SQL: Statement ignored
52/32 PLS-00302: component 'AES256' must be declared
85/9 PL/SQL: Statement ignored
85/38 PLS-00302: component 'RAW_TO_STRING' must be declared
121/9 PL/SQL: Statement ignored
121/36 PLS-00302: component 'RAW_TO_STRING' must be declared
SQL>
The first error at line 21 is because the code shows:
v_encoded := UTL_ENCODE.BASE64_ENCODE(p_data, TRUE);
And UTL_ENCODE.BASE64_ENCODE() only accepts one parameter, the data itself. Even checking Oracle 11 this function did not have an extra parameter. The next error is line 52:
typ => DBMS_CRYPTO.AES256 + DBMS_CRYPTO.CBC_PKCS5,
The global constants are wrong; for instance DBMS_CRYPTO.AES256 should be DBMS_CRYPTO.ENCRYPT_AES256 and DBMS_CRYPTO.CBC_PKCS5 should probably be DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5.
The next two errors are for RAW_TO_STRING() for instance:
p_decoded_data := UTL_I18N.RAW_TO_STRING(v_decoded_raw, 'AL32UTF8');
The function RAW_TO_STRING() does not exist. It should probably be RAW_TO_CHAR() that returns a VARCHAR2.
OK, so a few errors, let us now try the qwen3 generated code by running my qwen3.sql script:
SQL> @qwen3
Connected.
drop user qwen3 cascade
*
ERROR at line 1:
ORA-01918: user 'QWEN3' does not exist
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Connected.
Package created.
No errors.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY ENCRYPT_DECRYPT_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
46/9 PL/SQL: Statement ignored
48/32 PLS-00302: component 'AES256' must be declared
72/9 PL/SQL: Statement ignored
72/38 PLS-00302: component 'RAW_TO_STRING' must be declared
107/9 PL/SQL: Statement ignored
107/36 PLS-00302: component 'RAW_TO_STRING' must be declared
SQL>
Almost the same errors but less; the AES256 constant needs to be changed as well as its compatriots around PKCS5 and CBV the same as the 2.5 model. Also the same RAW_TO_STRING() function needs to be changed.
I am not going to fix them as part of this demo as I have learned enough on this case This was an experiment to see if we can use 3 different LLM models to generate PL/SQL code to encrypt and decrypt data in an Oracle database. A reminder of the prompt I used is here
please write a PL/SQL public package procedures and any supporting private procedures and functions where that public procedure can encrypt and decrypt data passed and base64 encode/decode the result
I thought I was clear; but maybe not. I wanted to have an encrypt() and decrypt() function that privately converted the encrypted data to BASE64 and on decrypt accept BASE64 data and decode it first and then decrypt. In other words only two public functions; encrypt and decrypt that are public. There is a combined function BUT I would have left the BASE64 functions private.
So, we had three LLMs and one failed and fell over; this was qwen3-coder-next. This ran for 51 minutes before falling over completely in the open-webui interface and where it did not generate any code. So this is not included further here.
The qwen2.5-coder LLM took 3723 ms to run and complete generating the code; which is 3.73 seconds. The model was processing at around 24 tokens per second. Not shabby.
The qwen3-coder:30b LLM took 1023 ms which is 1.023 seconds and was processing 134 tokens per second.
In summary, qwen3-coder-next is not useful at least on my mac; it did do a lot of reasoning before it failed so is clearly a better LLM for deeper analysis BUT if it failed then this does not matter. Qwen3-coder:30b ran in time in one third of the time of qwen2.5-coder and in terms of tokens per second it was processing 5.58 tokens per second faster than the 2.5 model.
So, qwen3-coder:30b is clearly faster than qwen2.5-coder and its code that it generated is slightly less broken than the 2.5 model BUT both produced code that did not work. I could have fed the output from each back into the LLM and it may have helped but the purpose of this post was to show two things; 1) the speed of three models compared to each other to generate PL/SQL and the quality of the code.
As I have said in my last blog. I think these models are more useful if you already know what you are doing. They can be productivity assistants and get you some way along the process but the code in each case needed tweaking/fixing but certainly the qwen3-coder:30b is fast enough for daily use.
#oracleace #sym_42 #ai #llm #qwen3-coder #plsql #oracle #security #generate #code
AI
My day job is Oracle security and that covers a massive area of topics from performing security audits, helping customers secure data in their Oracle databases, forensics, building and designing audit trails and reports, secure coding in PL/SQL, encryption of data from TDE to custom native encryption in PL/SQL or using any manor of Oracle cost options such as Database Vault, Virtual Private database and even third party tools.
I love to code and I code in PL/SQL, C, C++, Lua and .net including VB and c++/cli as well as even developing in vba in Excel.
So, AI? - I still use documentation and search engines to find out a setting or command that I cannot remember when developing for Oracle or configuring and setting up something such as Database vault or audit trails but over the last few months I have been also testing and playing with AI; Chatgpt or google Gemini or Microsoft co-pilot. I want to understand what the AI says compared to what I find out myself on Google.
Standard models are good but for specific subjects such as Oracle they often come up with the wrong answers. I can think of some simple examples I have tried for fun. I asked one of the free AIs how to truncate the unified audit trail in Oracle and it said truncate table unified_audit_trail. hmmm. The AI says it with confidence. Instead of saying; i am not sure or I do not know it tells me to truncate the table. I asked another question on changing a database password to the previous one and it showed me using ALTER USER IDENTIFIED BY VALUES but it did not tell how to get the old password first and also used the very old syntax before spare4 was used. I asked it for fun (because I know already) how to connect with a proxy. It just made up the syntax which was wrong. I also asked is Database Vault available with 21c XE and it said no. I knew it was as I had the license pdf open. I corrected the AI and it said - You are right - Oracle Database Vault is included in Oracle 21c XE. My earlier statement was incorrect. I have many many more examples of incorrect answers gathered over a while.
if you know what you are looking for and know what roughly the answer should be then the AI is good because you can recognise if the answer is probably right or not; is it better than the docs or Google; difficult to say; probably not. I know there are better models that you have to pay for but I am from Yorkshire and we are as they say tight. I also know you can augment the AI in three ways to make it better. You can create a well crafted system prompt and add input data via RAG and also use skills.md files to target the right answers.
My fear is the inexperienced person completely relying on the free chat interfaces and then implementing what it tells them in real systems.
We can use AI such as chatgpt as a web based chat window but of course the power of AI is to be able to use things like agents and automate what you want to do; so instead of getting a sample command in a chat window and the using it yourself you can set up AI to run the command on your real systems.
If you do not really know what you are doing in the first place then you should be wary of AI and not simply use the answers.
There has been a lot of noise and chatter about AI for a long time now with some suggesting the industry is a bubble bigger than the dot com bubble and some talking about the huge costs of teaching models that looks unlikely to be recovered by paying customers. There is also the increases in price and shortages of the GPU hardware and the huge energy costs of the data centers.
In the last few days there have been some prominent news articles discussing AI sovereignty and focusing on building AI internally to retain all of the data / prompts passed to the AI. This is exactly paralleled with my Oracle security day job - protecting the your data. As I said in Oracle security in my trainings and here; it is not the job of Oracle to protect your data; it is your job to project your data using the standard features of the Oracle database and even cost options from Oracle or third party solutions. The same applies in AI. If you want to use AI you must protect your data that is fed into the AI .
Is a free on line tool better than a home run LLM (Large Language Model)? this depends on a lot of things; the hardware that you use, the sources of data that you can add to it via RAG or the context window and of course the model you download and use.
My experience so far with the free tools is that you get blocked quickly particularly if you upload files. Load 2 and you are fine, load three and it says come back tomorrow in 24/25 hours and try again.
I have been playing with a local LLM recently as well to test how good it is. I am feeding it skills files and sources via RAG and testing is in early stages but looks great so far. I m using a Mac Book Pro M5 with 64gb of RAM and a 2 TB SSD. There are so many models to use so I will be trying quite a few. I have tested qwen2.5 coder so far. My experience of setting up the local LLM was great, downloading the models takes the biggest time but setting up to run with a chat interface was simple.
More on this soon as I will report back here on my experience with a local LLM. Having a local LLM that does not have limits and is almost as fast as chatgpt and the answers are at a similar level so far is good.
#oracleace #sym_42 #ai #llm #localllm #oracle #security
I love to code and I code in PL/SQL, C, C++, Lua and .net including VB and c++/cli as well as even developing in vba in Excel.
So, AI? - I still use documentation and search engines to find out a setting or command that I cannot remember when developing for Oracle or configuring and setting up something such as Database vault or audit trails but over the last few months I have been also testing and playing with AI; Chatgpt or google Gemini or Microsoft co-pilot. I want to understand what the AI says compared to what I find out myself on Google.
Standard models are good but for specific subjects such as Oracle they often come up with the wrong answers. I can think of some simple examples I have tried for fun. I asked one of the free AIs how to truncate the unified audit trail in Oracle and it said truncate table unified_audit_trail. hmmm. The AI says it with confidence. Instead of saying; i am not sure or I do not know it tells me to truncate the table. I asked another question on changing a database password to the previous one and it showed me using ALTER USER IDENTIFIED BY VALUES but it did not tell how to get the old password first and also used the very old syntax before spare4 was used. I asked it for fun (because I know already) how to connect with a proxy. It just made up the syntax which was wrong. I also asked is Database Vault available with 21c XE and it said no. I knew it was as I had the license pdf open. I corrected the AI and it said - You are right - Oracle Database Vault is included in Oracle 21c XE. My earlier statement was incorrect. I have many many more examples of incorrect answers gathered over a while.
if you know what you are looking for and know what roughly the answer should be then the AI is good because you can recognise if the answer is probably right or not; is it better than the docs or Google; difficult to say; probably not. I know there are better models that you have to pay for but I am from Yorkshire and we are as they say tight. I also know you can augment the AI in three ways to make it better. You can create a well crafted system prompt and add input data via RAG and also use skills.md files to target the right answers.
My fear is the inexperienced person completely relying on the free chat interfaces and then implementing what it tells them in real systems.
We can use AI such as chatgpt as a web based chat window but of course the power of AI is to be able to use things like agents and automate what you want to do; so instead of getting a sample command in a chat window and the using it yourself you can set up AI to run the command on your real systems.
If you do not really know what you are doing in the first place then you should be wary of AI and not simply use the answers.
There has been a lot of noise and chatter about AI for a long time now with some suggesting the industry is a bubble bigger than the dot com bubble and some talking about the huge costs of teaching models that looks unlikely to be recovered by paying customers. There is also the increases in price and shortages of the GPU hardware and the huge energy costs of the data centers.
In the last few days there have been some prominent news articles discussing AI sovereignty and focusing on building AI internally to retain all of the data / prompts passed to the AI. This is exactly paralleled with my Oracle security day job - protecting the your data. As I said in Oracle security in my trainings and here; it is not the job of Oracle to protect your data; it is your job to project your data using the standard features of the Oracle database and even cost options from Oracle or third party solutions. The same applies in AI. If you want to use AI you must protect your data that is fed into the AI .
Is a free on line tool better than a home run LLM (Large Language Model)? this depends on a lot of things; the hardware that you use, the sources of data that you can add to it via RAG or the context window and of course the model you download and use.
My experience so far with the free tools is that you get blocked quickly particularly if you upload files. Load 2 and you are fine, load three and it says come back tomorrow in 24/25 hours and try again.
I have been playing with a local LLM recently as well to test how good it is. I am feeding it skills files and sources via RAG and testing is in early stages but looks great so far. I m using a Mac Book Pro M5 with 64gb of RAM and a 2 TB SSD. There are so many models to use so I will be trying quite a few. I have tested qwen2.5 coder so far. My experience of setting up the local LLM was great, downloading the models takes the biggest time but setting up to run with a chat interface was simple.
More on this soon as I will report back here on my experience with a local LLM. Having a local LLM that does not have limits and is almost as fast as chatgpt and the answers are at a similar level so far is good.
#oracleace #sym_42 #ai #llm #localllm #oracle #security
Forensic Analysis for records in Oracle with no Timestamp
If we have an Oracle database table with no timestamp can we still get a date/time stamp when something happened?
For instance if a table has a date/time column then yes of course we can know when the records changed assuming the standard "last updated by" and "last updated when" columns like we see in applications such as EBS.
If we have auditing enabled on the table we are interested in and it captures the right events then yes, we can see when a record changed or was added or deleted in that table.
If the database is in archivelog mode and the archive logs are available then we can use the redo/archive logs and log miner to retrieve changes from the database that affect the table we are interested in.
BUT, inevitably there will be no column, audit or redo available so is there any other way we can find out what happened?
A good example would be system grants in the database. So for instance
So, we can see grants in the DBA_SYS_PRIVS view and this has the following structure:
As we can see there is no date/timestamp for system privilege grants. I would like to be able to map them if possible as part of an Oracle database security audit opr as part of a forensic investigation of the database.
What is the base tables:
Quite a complex union of 3 SQLs mapping the user names for grantee and also linking to the system_privilege_map and mainly sys.sysauth$. All the privilege records are basically stored in sysauth$. What is the structure of this table:
No timestamp is available on this table so we do not have any native columns. The Oracle database uses SCNs (System Change Number) and whilst these are not a timestamp they are useful as they order records. There is a table in the database that is managed by SMON_SCN_TIME table that maintains a short window of mappings between SCN numbers and timestamps. So if the SCN is recent then if we have an SCN we can map to a timestamp. Sounds like it could be useful but there is a window of accuracy with the mapping of 3 seconds so the timestamp can be out by 3 seconds.
So, can we get the SCN from the SYSAUTH$ table for all records / rows?
A few interesting points pop up. 1) There are 1332 rows in this table so you would assume currently there are 1332 system privilege grants:
No, 1089 grants not 1332. We could analyse the difference but that is moving off topic for this discussion. 2) the last row sequence# is 3841 so the assumption here is that there are a very large number of rows missing if we assume one sequence per row and its incremented every time a row is added. Also a quick look shows that there are a lot of gaps in the sequence numbers so that supports the theory of deletions or more correctly revokes of grants.
The biggest issue for our investigation is that the SCN is stored at the block level not the row level. We can see from the output that there are few actual SCNs per row:
As we can see this table currently uses 4 blocks in my database and from the numbers assigned to each SCN we can see the relative similar number of records for the full blocks as 368 - 382 and clearly the last block with just 203 records is not full yet. We can also see the block numbers in this SQL:
This proves the SCN is per block and each block has between 203 (latest and filling up) and 382 rows
Lets see the timestamp per block
The SCN to timestamp mapping is stated to have a precision of 3 seconds, and up to 120 hours of mapping if automatic undo management is used.- SCN to Timestamp Mapping.
The ORA-08181 message means that the mapping is not stored anymore as the database holds limited mapping
The mapping is in the table sys.smon_scn_time
We can check the max time range in the table with:
The range in my database is not realistic as this is a 26ai OVA and I installed it two weeks ago so the date range is in reality smaller
So, can we at least get a time for each block
The other 3 older blocks give ORA-08181 error that the SCN to time map is too old.
We can also get the min and max timestamps and SCNs and also work out on average how many SCNs per second:
So 1.45 SCN per second are generated.
The previous blocks are not in the same scn range but we could calculate fir fun
1 - 4390900 - 995672 = 3395228 SCN / 1.45 = 2,341,536 seconds =
2 - 4390900 - 617063 = 3,773,837/1.45 = 2,602,646.2 =
3 - 4390900 - 501880 = 3889020/1.45=2682082 =
This is clearly wrong as the min time in the SCN map was October 2025 BUT this is a pre-built 26ai OVA and it was not run until 01-JUN-2026 by me. BUT the calculations show how you may get a very rough ball park for a system that is not an OVA that has been stopped and not used until someone starts to install and use it
We can get the last change at a block level - i.e. a date time for the last grant of a SYSTEM privilege but we cannot get timestamps for each grant done since I started to use the database unless we have an audit trail
BUT, often when an attack occurs we want to try and understand what happened and when and part of that is system grants
We can also repeat the calculation not from current SCN map but also for the older blocks with the last SCN map:
This is probably more realistic
What is database created date?
And the date time of SYS
And all users
We can also check data files:
And the filenames:
In summary, we cannot get a timestamp from the database for a row in a table that does not itself have a timestamp column unless we also have audit or we can use redo logs. We can use SCN but only at the block level and we could also do ID analysis to see what rows are missing but not what is in those rows. We could use off line data block analysis but unless a row was held in the ITL (Interested Transaction List) in the data block we would not get much futher.
SCNs are useful and can create an outline / boundary for instance if the last record of the 3rd block has an SCN and we can estimate a timestamp for the closed block we can estimate the time the block has existed and whether it falls into the range of the attack
#oracleace #sym_42 #oracle #forensics #database #security #scn #hacking #databreach
For instance if a table has a date/time column then yes of course we can know when the records changed assuming the standard "last updated by" and "last updated when" columns like we see in applications such as EBS.
If we have auditing enabled on the table we are interested in and it captures the right events then yes, we can see when a record changed or was added or deleted in that table.
If the database is in archivelog mode and the archive logs are available then we can use the redo/archive logs and log miner to retrieve changes from the database that affect the table we are interested in.
BUT, inevitably there will be no column, audit or redo available so is there any other way we can find out what happened?
A good example would be system grants in the database. So for instance
SQL> grant create session to sec_auditor;
So, we can see grants in the DBA_SYS_PRIVS view and this has the following structure:
C:\>sqlplus sys/oracle@//192.168.56.34:1521/freepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 7 09:28:16 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0
SQL> desc dba_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(128)
PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
COMMON VARCHAR2(3)
INHERITED VARCHAR2(3)
SQL>
As we can see there is no date/timestamp for system privilege grants. I would like to be able to map them if possible as part of an Oracle database security audit opr as part of a forensic investigation of the database.
What is the base tables:
SQL> alter session set container=cdb$root;
Session altered.
SQL>
SQL> set long 1000000
SQL> select text from dba_views where view_name='DBA_SYS_PRIVS';
TEXT
--------------------------------------------------------------------------------
select u.name,spm.name,decode(min(mod(option$, 2)),1,'YES','NO'),
'NO', 'NO'
from sys.system_privilege_map spm, sys.sysauth$ sa, user$ u
where sa.grantee#=u.user# and sa.privilege#=spm.privilege
and bitand(nvl(option$, 0), 4) = 0
group by u.name,spm.name
union all
/* Commonly granted Privileges */
select u.name,spm.name,decode(min(bitand(option$, 16)),16,'YES','NO'),
'YES', decode(SYS_CONTEXT('USERENV', 'CON_ID'), 1, 'NO', 'YES')
from sys.system_privilege_map spm, sys.sysauth$ sa, user$ u
TEXT
--------------------------------------------------------------------------------
where sa.grantee#=u.user# and sa.privilege#=spm.privilege
and bitand(option$,8) = 8
group by u.name,spm.name
union all
/* Federationally granted Privileges */
select u.name,spm.name,decode(min(bitand(option$, 128)),128,'YES','NO'),
'YES',
decode(SYS_CONTEXT('USERENV', 'IS_APPLICATION_PDB'), 'YES', 'YES', 'NO')
from sys.system_privilege_map spm, sys.sysauth$ sa, user$ u
where sa.grantee#=u.user# and sa.privilege#=spm.privilege
and bitand(option$,64) = 64
TEXT
--------------------------------------------------------------------------------
group by u.name,spm.name
SQL>
Quite a complex union of 3 SQLs mapping the user names for grantee and also linking to the system_privilege_map and mainly sys.sysauth$. All the privilege records are basically stored in sysauth$. What is the structure of this table:
SQL> desc sysauth$
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE# NOT NULL NUMBER
PRIVILEGE# NOT NULL NUMBER
SEQUENCE# NOT NULL NUMBER
OPTION$ NUMBER
SQL>
No timestamp is available on this table so we do not have any native columns. The Oracle database uses SCNs (System Change Number) and whilst these are not a timestamp they are useful as they order records. There is a table in the database that is managed by SMON_SCN_TIME table that maintains a short window of mappings between SCN numbers and timestamps. So if the SCN is recent then if we have an SCN we can map to a timestamp. Sounds like it could be useful but there is a window of accuracy with the mapping of 3 seconds so the timestamp can be out by 3 seconds.
So, can we get the SCN from the SYSAUTH$ table for all records / rows?
SQL> select ora_rowscn,grantee#,privilege#,sequence#,option$ from sysauth$ order by sequence#;
ORA_ROWSCN GRANTEE# PRIVILEGE# SEQUENCE# OPTION$
---------- ---------- ---------- ---------- ----------
501880 0 -352 1 12
501880 0 2 2 28
501880 2 -5 3 12
501880 0 3 4 28
501880 3 -40 5 12
501880 3 -60 6 12
501880 3 -80 7 12
501880 3 -90 8 12
501880 3 -105 9 12
501880 3 -140 10 12
501880 3 -151 11 12
ORA_ROWSCN GRANTEE# PRIVILEGE# SEQUENCE# OPTION$
---------- ---------- ---------- ---------- ----------
501880 3 -172 12 12
501880 3 -180 13 12
...
4605568 237 -265 3826
4605568 237 -80 3827
4605568 237 -44 3828
4605568 237 -71 3829
4605568 237 -73 3830
4605568 241 -165 3835
4605568 243 241 3836
4605568 244 242 3837
4605568 243 240 3838
4605568 244 240 3839
4605568 237 239 3840
ORA_ROWSCN GRANTEE# PRIVILEGE# SEQUENCE# OPTION$
---------- ---------- ---------- ---------- ----------
4605568 237 240 3841
1332 rows selected.
SQL>
A few interesting points pop up. 1) There are 1332 rows in this table so you would assume currently there are 1332 system privilege grants:
SQL> select count(*) from dba_sys_privs;
COUNT(*)
----------
1089
SQL>
No, 1089 grants not 1332. We could analyse the difference but that is moving off topic for this discussion. 2) the last row sequence# is 3841 so the assumption here is that there are a very large number of rows missing if we assume one sequence per row and its incremented every time a row is added. Also a quick look shows that there are a lot of gaps in the sequence numbers so that supports the theory of deletions or more correctly revokes of grants.
The biggest issue for our investigation is that the SCN is stored at the block level not the row level. We can see from the output that there are few actual SCNs per row:
SQL> select count(*),ora_rowscn from sysauth$ group by ora_rowscn;
COUNT(*) ORA_ROWSCN
---------- ----------
379 501880
382 617063
368 995672
203 4605568
SQL>
As we can see this table currently uses 4 blocks in my database and from the numbers assigned to each SCN we can see the relative similar number of records for the full blocks as 368 - 382 and clearly the last block with just 203 records is not full yet. We can also see the block numbers in this SQL:
SQL> select count(*),dbms_rowid.rowid_relative_fno(rowid) AS file_no,dbms_rowid.rowid_block_number(rowid) AS block_no from sysauth$ group by dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid);
COUNT(*) FILE_NO BLOCK_NO
---------- ---------- ----------
382 0 1490
379 0 1489
368 0 1491
203 0 1492
SQL>
This proves the SCN is per block and each block has between 203 (latest and filling up) and 382 rows
Lets see the timestamp per block
SQL> select distinct scn_to_timestamp(ora_rowscn) from sysauth$;
select distinct scn_to_timestamp(ora_rowscn) from sysauth$
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
SQL>
The SCN to timestamp mapping is stated to have a precision of 3 seconds, and up to 120 hours of mapping if automatic undo management is used.- SCN to Timestamp Mapping.
The ORA-08181 message means that the mapping is not stored anymore as the database holds limited mapping
The mapping is in the table sys.smon_scn_time
SQL> desc smon_scn_time
Name Null? Type
----------------------------------------- -------- ----------------------------
THREAD NUMBER
TIME_MP NUMBER
TIME_DP DATE
SCN_WRP NUMBER
SCN_BAS NUMBER
NUM_MAPPINGS NUMBER
TIM_SCN_MAP RAW(1200)
SCN NUMBER
ORIG_THREAD NUMBER
SQL>
We can check the max time range in the table with:
SQL> select to_char(min(time_dp),'DD-MON-YYYY HH24:MI:SS'),to_char(max(time_dp),'DD-MON-YYYY HH24:MI:SS') from smon_scn_time;
TO_CHAR(MIN(TIME_DP),'DD-MON- TO_CHAR(MAX(TIME_DP),'DD-MON-
----------------------------- -----------------------------
20-OCT-2025 15:18:02 07-JUN-2026 08:54:39
SQL>
The range in my database is not realistic as this is a 26ai OVA and I installed it two weeks ago so the date range is in reality smaller
So, can we at least get a time for each block
SQL> select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1492;
TIM ROD
---------------------------------------- -----
05-JUN-26 02.44.52.000000000 PM 1492
SQL>
The other 3 older blocks give ORA-08181 error that the SCN to time map is too old.
SQL> select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1491;
select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1491
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
SQL> select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1490;
select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1490
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
SQL> select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1489;
select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1489
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
SQL>
We can also get the min and max timestamps and SCNs and also work out on average how many SCNs per second:
SQL> select to_char(min(time_dp),'DD-MON-YYYY HH24:MI:SS'),to_char(max(time_dp),'DD-MON-YYYY HH24:MI:SS'),min(scn),max(scn),max(scn)-min(scn) diff,(max(time_dp)-min(time_dp))*86400 sec, ((max(time_dp)-min(time_dp))*86400)/(max(scn)-min(scn)) from smon_scn_time where to_char(time_dp,'MM-YYYY')='06-2026';
TO_CHAR(MIN(TIME_DP),'DD-MON- TO_CHAR(MAX(TIME_DP),'DD-MON- MIN(SCN)
----------------------------- ----------------------------- ----------
MAX(SCN) DIFF SEC
---------- ---------- ----------
((MAX(TIME_DP)-MIN(TIME_DP))*86400)/(MAX(SCN)-MIN(SCN))
-------------------------------------------------------
01-JUN-2026 06:12:37 07-JUN-2026 09:24:41 4390900
4755799 364899 529924
1.45224843
SQL>
So 1.45 SCN per second are generated.
The previous blocks are not in the same scn range but we could calculate fir fun
1 - 4390900 - 995672 = 3395228 SCN / 1.45 = 2,341,536 seconds =
SQL> SELECT to_char(TO_DATE('01-JUN-2026 06:12:37','DD-MON-YYYY HH24:MI:SS')-(2341536/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('01-
--------------------
05-MAY-2026 03:47:01
SQL>
2 - 4390900 - 617063 = 3,773,837/1.45 = 2,602,646.2 =
SQL> SELECT to_char(TO_DATE('01-JUN-2026 06:12:37','DD-MON-YYYY HH24:MI:SS')-(2602646/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('01-
--------------------
02-MAY-2026 03:15:11
SQL>
3 - 4390900 - 501880 = 3889020/1.45=2682082 =
SQL> SELECT to_char(TO_DATE('01-JUN-2026 06:12:37','DD-MON-YYYY HH24:MI:SS')-(2682082/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('01-
--------------------
01-MAY-2026 05:11:15
SQL>
This is clearly wrong as the min time in the SCN map was October 2025 BUT this is a pre-built 26ai OVA and it was not run until 01-JUN-2026 by me. BUT the calculations show how you may get a very rough ball park for a system that is not an OVA that has been stopped and not used until someone starts to install and use it
We can get the last change at a block level - i.e. a date time for the last grant of a SYSTEM privilege but we cannot get timestamps for each grant done since I started to use the database unless we have an audit trail
BUT, often when an attack occurs we want to try and understand what happened and when and part of that is system grants
We can also repeat the calculation not from current SCN map but also for the older blocks with the last SCN map:
SQL> SELECT to_char(TO_DATE('20-OCT-2025 15:18:02','DD-MON-YYYY HH24:MI:SS')-(2341536/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('20-
--------------------
23-SEP-2025 12:52:26
SQL> SELECT to_char(TO_DATE('20-OCT-2025 15:18:02','DD-MON-YYYY HH24:MI:SS')-(2602646/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('20-
--------------------
20-SEP-2025 12:20:36
SQL> SELECT to_char(TO_DATE('20-OCT-2025 15:18:02','DD-MON-YYYY HH24:MI:SS')-(2682082/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('20-
--------------------
19-SEP-2025 14:16:40
SQL>
This is probably more realistic
What is database created date?
SQL> select to_char(created,'DD-MON-YYYY HH24:MI:SS') from v$database;
TO_CHAR(CREATED,'DD-MON-YYYYH
-----------------------------
20-OCT-2025 15:04:47
SQL>
And the date time of SYS
SQL> select to_char(ctime,'DD-MON-YYYY HH24:MI:SS'),ptime,exptime,ltime,spare6 from user$ where name='SYS';
TO_CHAR(CTIME,'DD-MON-YYYYHH2 PTIME EXPTIME LTIME SPARE6
----------------------------- --------- --------- --------- ---------
08-OCT-2025 22:00:58
SQL>
And all users
SQL> set lines 220
SQL> col name for a30
SQL> col ctime for a20
SQL> col ptime for a20
SQL> col exptime for a20
SQL> col ltime for a20
SQL> col spare6 for a20
SQL> select name,to_char(ctime,'DD-MON-YYYY HH24:MI:SS') ctime,to_char(ptime,'DD-MON-YYYY HH24:MI:SS') ptime,to_char(exptime,'DD-MON-YYYY HH24:MI:SS') exptime,to_char(ltime,'DD-MON-YYYY HH24:MI:SS') ltime,to_char(spare6,'DD-MON-YYYY HH24:MI:SS') spare6 from user$;
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
SYS 08-OCT-2025 22:00:58
PUBLIC 08-OCT-2025 22:00:58
CONNECT 08-OCT-2025 22:00:58
RESOURCE 08-OCT-2025 22:00:58
DBA 08-OCT-2025 22:00:58
PDB_DBA 08-OCT-2025 22:00:58
AUDIT_ADMIN 08-OCT-2025 22:00:58
AUDIT_VIEWER 08-OCT-2025 22:00:58
AUDSYS 08-OCT-2025 22:00:59 20-OCT-2025 15:13:12
SYSTEM 08-OCT-2025 22:00:59 20-OCT-2025 15:54:39
SELECT_CATALOG_ROLE 08-OCT-2025 22:00:59
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
EXECUTE_CATALOG_ROLE 08-OCT-2025 22:00:59
CAPTURE_ADMIN 08-OCT-2025 22:00:59
SYSBACKUP 08-OCT-2025 22:00:59 20-OCT-2025 15:13:12
SYSDG 08-OCT-2025 22:00:59 20-OCT-2025 15:13:12
SYSKM 08-OCT-2025 22:00:59 20-OCT-2025 15:13:12
SYSRAC 08-OCT-2025 22:00:59
OUTLN 08-OCT-2025 22:01:06 20-OCT-2025 15:13:12
EXP_FULL_DATABASE 08-OCT-2025 22:01:22
IMP_FULL_DATABASE 08-OCT-2025 22:01:22
AVTUNE_PKG_ROLE 08-OCT-2025 22:01:31
DATAPUMP_CLOUD_EXP 08-OCT-2025 22:33:17
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
VECSYS 08-OCT-2025 22:01:32 20-OCT-2025 15:13:12
BAASSYS 08-OCT-2025 22:01:33 20-OCT-2025 15:13:12
CDB_DBA 08-OCT-2025 22:31:55
APPLICATION_TRACE_VIEWER 08-OCT-2025 22:32:18
ACCHK_READ 08-OCT-2025 22:32:18
LOGSTDBY_ADMINISTRATOR 08-OCT-2025 22:32:50
DBFS_ROLE 08-OCT-2025 22:32:56
GSMUSER_ROLE 08-OCT-2025 22:32:57
GSMROOTUSER_ROLE 08-OCT-2025 22:32:57
GSMADMIN_INTERNAL 08-OCT-2025 22:32:57 20-OCT-2025 15:13:12
GSMUSER 08-OCT-2025 22:32:57 20-OCT-2025 15:13:12
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
GGSHAREDCAP 08-OCT-2025 22:32:59 20-OCT-2025 15:13:12
DIP 08-OCT-2025 22:33:07 08-OCT-2025 22:33:07
SAGA_ADM_ROLE 08-OCT-2025 22:33:12
SAGA_PARTICIPANT_ROLE 08-OCT-2025 22:33:12
SAGA_CONNECT_ROLE 08-OCT-2025 22:33:12
AQ_ADMINISTRATOR_ROLE 08-OCT-2025 22:33:14
AQ_USER_ROLE 08-OCT-2025 22:33:14
DATAPUMP_EXP_FULL_DATABASE 08-OCT-2025 22:33:17
DATAPUMP_IMP_FULL_DATABASE 08-OCT-2025 22:33:17
XS$NULL 08-OCT-2025 22:33:29 08-OCT-2025 22:33:29
DATAPUMP_CLOUD_IMP 08-OCT-2025 22:33:17
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
ADM_PARALLEL_EXECUTE_TASK 08-OCT-2025 22:33:27
PROVISIONER 08-OCT-2025 22:33:29
XS_SESSION_ADMIN 08-OCT-2025 22:33:29
XS_NAMESPACE_ADMIN 08-OCT-2025 22:33:29
XS_CACHE_ADMIN 08-OCT-2025 22:33:29
XS_CONNECT 08-OCT-2025 22:33:29
RECOVERY_CATALOG_USER 08-OCT-2025 22:36:16
REMOTE_SCHEDULER_AGENT 08-OCT-2025 22:33:34 20-OCT-2025 15:13:12
DBSFWUSER 08-OCT-2025 22:33:36 20-OCT-2025 15:13:12
SQL_FIREWALL_ADMIN 08-OCT-2025 22:33:38
SQL_FIREWALL_VIEWER 08-OCT-2025 22:33:38
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
OSAK_ADMIN_ROLE 08-OCT-2025 22:33:39
GATHER_SYSTEM_STATISTICS 08-OCT-2025 22:34:30
OPTIMIZER_PROCESSING_RATE 08-OCT-2025 22:34:30
DBMS_MDX_INTERNAL 08-OCT-2025 22:34:46
BDSQL_ADMIN 08-OCT-2025 22:35:37
BDSQL_USER 08-OCT-2025 22:35:37
RECOVERY_CATALOG_OWNER 08-OCT-2025 22:36:16
RECOVERY_CATALOG_OWNER_VPD 08-OCT-2025 22:36:16
OEM_MONITOR 08-OCT-2025 22:49:11
SYSUMF_ROLE 08-OCT-2025 22:44:53
SYS$UMF 08-OCT-2025 22:44:53 20-OCT-2025 15:13:12
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
MAINTPLAN_APP 08-OCT-2025 22:44:53
NOTIFICATIONS_USER 08-OCT-2025 22:44:55
NOTIFICATIONS_ADMIN 08-OCT-2025 22:44:55
SCHEDULER_ADMIN 08-OCT-2025 22:46:26
PPLB_ROLE 08-OCT-2025 22:46:37
DGPDB_ROLE 08-OCT-2025 22:46:37
DGPDB_INT 08-OCT-2025 22:46:37 20-OCT-2025 15:13:12
HS_ADMIN_SELECT_ROLE 08-OCT-2025 22:46:53
HS_ADMIN_EXECUTE_ROLE 08-OCT-2025 22:46:53
HS_ADMIN_ROLE 08-OCT-2025 22:46:53
GLOBAL_AQ_USER_ROLE 08-OCT-2025 22:46:56
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
OGG_CAPTURE 08-OCT-2025 22:49:10
OGG_APPLY 08-OCT-2025 22:49:10
OGG_APPLY_PROCREP 08-OCT-2025 22:49:10
OGG_SHARED_CAPTURE 08-OCT-2025 22:49:10
XSTREAM_CAPTURE 08-OCT-2025 22:49:10
XSTREAM_APPLY 08-OCT-2025 22:49:10
OEM_ADVISOR 08-OCT-2025 22:49:11
PGX_SESSION_GET_PUBLISHED_GRAP 08-OCT-2025 22:49:31
H
DBSNMP 08-OCT-2025 22:49:12 20-OCT-2025 15:13:12
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
APPQOSSYS 08-OCT-2025 22:49:12 20-OCT-2025 15:13:12
GSMCATUSER_ROLE 08-OCT-2025 22:49:14
GSMADMIN_ROLE 08-OCT-2025 22:49:14
GSM_POOLADMIN_ROLE 08-OCT-2025 22:49:14
GDS_CATALOG_SELECT 08-OCT-2025 22:49:14
SHARDED_SCHEMA_OWNER 08-OCT-2025 22:49:14
GSMCATUSER 08-OCT-2025 22:49:14 20-OCT-2025 15:13:12
GGSYS 08-OCT-2025 22:49:23 20-OCT-2025 15:13:12
GGSYS_ROLE 08-OCT-2025 22:49:23
DB_DEVELOPER_ROLE 08-OCT-2025 22:49:30
GRAPH_DEVELOPER 08-OCT-2025 22:49:31
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
GRAPH_ADMINISTRATOR 08-OCT-2025 22:49:31
GRAPH_USER 08-OCT-2025 22:49:31
PGX_SESSION_CREATE 08-OCT-2025 22:49:31
PGX_SERVER_GET_INFO 08-OCT-2025 22:49:31
PGX_SERVER_MANAGE 08-OCT-2025 22:49:31
PGX_SESSION_READ_MODEL 08-OCT-2025 22:49:31
PGX_SESSION_MODIFY_MODEL 08-OCT-2025 22:49:31
PGX_SESSION_NEW_GRAPH 08-OCT-2025 22:49:31
OJVMSYS 08-OCT-2025 23:00:15 20-OCT-2025 15:13:12
PGX_SESSION_COMPILE_ALGORITHM 08-OCT-2025 22:49:31
PGX_SESSION_ADD_PUBLISHED_GRAP 08-OCT-2025 22:49:31
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
H
PGX_SESSION_SET_IDLE_TIMEOUT 08-OCT-2025 22:49:31
XDB 08-OCT-2025 22:50:49 20-OCT-2025 15:13:12
ANONYMOUS 08-OCT-2025 22:50:49 20-OCT-2025 15:13:12
XDBADMIN 08-OCT-2025 22:50:49
XDB_SET_INVOKER 08-OCT-2025 22:51:06
AUTHENTICATEDUSER 08-OCT-2025 22:51:08
XDB_WEBSERVICES 08-OCT-2025 22:51:08
XDB_WEBSERVICES_WITH_PUBLIC 08-OCT-2025 22:51:08
XDB_WEBSERVICES_OVER_HTTP 08-OCT-2025 22:51:08
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
SODA_APP 08-OCT-2025 22:56:28
WMSYS 08-OCT-2025 22:58:20 20-OCT-2025 15:13:12
WM_ADMIN_ROLE 08-OCT-2025 22:58:24
JAVAUSERPRIV 08-OCT-2025 23:00:11
JAVAIDPRIV 08-OCT-2025 23:00:11
JAVASYSPRIV 08-OCT-2025 23:00:11
JAVADEBUGPRIV 08-OCT-2025 23:00:11
EJBCLIENT 08-OCT-2025 23:00:11
JMXSERVER 08-OCT-2025 23:00:11
DV_SECANALYST 08-OCT-2025 23:12:52
DVSYS 08-OCT-2025 23:12:46 20-OCT-2025 15:13:12
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
JAVA_ADMIN 08-OCT-2025 23:00:27
CTXSYS 08-OCT-2025 23:03:06 20-OCT-2025 15:13:12
CTXAPP 08-OCT-2025 23:03:08
OLAP_XS_ADMIN 08-OCT-2025 23:04:38
OLAPSYS 08-OCT-2025 23:04:44 20-OCT-2025 15:13:12
OLAP_DBA 08-OCT-2025 23:04:45
OLAP_USER 08-OCT-2025 23:04:45
MDSYS 08-OCT-2025 23:08:39 20-OCT-2025 15:13:12
MDDATA 08-OCT-2025 23:08:39 20-OCT-2025 15:13:12
RDFCTX_ADMIN 08-OCT-2025 23:11:11
LBACSYS 08-OCT-2025 23:12:07 20-OCT-2025 15:13:12
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
LBAC_DBA 08-OCT-2025 23:12:07
DVF 08-OCT-2025 23:12:46 20-OCT-2025 15:13:12
DV_MONITOR 08-OCT-2025 23:12:52
DV_ADMIN 08-OCT-2025 23:12:52
DV_OWNER 08-OCT-2025 23:12:52
DV_ACCTMGR 08-OCT-2025 23:12:52
DV_PATCH_ADMIN 08-OCT-2025 23:12:52
PDBADMIN 20-OCT-2025 15:13:10 20-OCT-2025 15:13:10 18-APR-2026 15:13:10
DV_STREAMS_ADMIN 08-OCT-2025 23:12:52
DV_GOLDENGATE_ADMIN 08-OCT-2025 23:12:52
DV_XSTREAM_ADMIN 08-OCT-2025 23:12:52
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
DV_GOLDENGATE_REDO_ACCESS 08-OCT-2025 23:12:52
DV_AUDIT_CLEANUP 08-OCT-2025 23:12:52
DV_DATAPUMP_NETWORK_LINK 08-OCT-2025 23:12:52
DV_POLICY_OWNER 08-OCT-2025 23:12:52
HR 20-OCT-2025 15:41:48 20-OCT-2025 15:41:48 20-OCT-2025 15:54:42
APEX_240200 20-OCT-2025 15:14:39 20-OCT-2025 15:14:39
FLOWS_FILES 20-OCT-2025 15:14:39 20-OCT-2025 15:14:39
APEX_PUBLIC_USER 20-OCT-2025 15:14:39 20-OCT-2025 15:39:31
APEX_PUBLIC_ROUTER 20-OCT-2025 15:14:40
APEX_ADMINISTRATOR_READ_ROLE 20-OCT-2025 15:14:56
APEX_ADMINISTRATOR_ROLE 20-OCT-2025 15:14:56
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
APEX_GRANTS_FOR_NEW_USERS_ROLE 20-OCT-2025 15:14:56
ORDS_PUBLIC_USER 20-OCT-2025 15:40:05 20-OCT-2025 15:40:05 01-JUN-2026 07:45:12
ORDS_METADATA 20-OCT-2025 15:40:06
ORDS_ADMINISTRATOR_ROLE 20-OCT-2025 15:40:46
ORDS_RUNTIME_ROLE 20-OCT-2025 15:40:46
_NEXT_USER 08-OCT-2025 22:00:58
SH 20-OCT-2025 15:41:50 20-OCT-2025 15:41:50 20-OCT-2025 15:54:40
CO 20-OCT-2025 15:41:52 20-OCT-2025 15:41:52 20-OCT-2025 15:54:42
HRREST 20-OCT-2025 15:48:33 20-OCT-2025 15:48:33 20-OCT-2025 15:48:39
AV 20-OCT-2025 15:48:35 20-OCT-2025 15:48:35 20-OCT-2025 15:48:35
AOS_GGADMIN 05-JUN-2026 14:22:00 05-JUN-2026 14:22:00
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
AOS_DATA 05-JUN-2026 14:43:36 05-JUN-2026 14:43:36 05-JUN-2026 14:43:40 05-JUN-2026 14:43:37
AOS_CODE 05-JUN-2026 14:43:36 05-JUN-2026 14:43:36 05-JUN-2026 14:44:54
AOS_BUILD_DATA 05-JUN-2026 14:43:36
AOS_BUILD_CODE 05-JUN-2026 14:43:36
AOS_CONNECT 05-JUN-2026 14:43:36
AOS_RUNTIME_ADMIN 05-JUN-2026 14:43:40
AOS_RUNTIME_VIEWER 05-JUN-2026 14:43:40
AOS_ADMIN 05-JUN-2026 14:43:40 05-JUN-2026 14:43:40 05-JUN-2026 14:45:30
AOS_VIEWER 05-JUN-2026 14:43:40 05-JUN-2026 14:43:40
181 rows selected.
SQL>
We can also check data files:
SQL> col dt for a20
SQL> col file_name for a50
SQL> set lines 220
SQL> select to_char(creation_time,'DD-MON-YYYY HH24:MI:SS') dt,file_name from v$datafile,dba_data_files where file#=file_id;
DT FILE_NAME
-------------------- --------------------------------------------------
20-OCT-2025 15:13:06 /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf
20-OCT-2025 15:13:54 /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf
20-OCT-2025 15:13:06 /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf
20-OCT-2025 15:13:07 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf
SQL>
And the filenames:
SQL> col dt for 9999999999
SQL> col file_name for a50
SQL> set lines 220
SQL> select checkpoint_change# dt,file_name from v$datafile_header,dba_data_files where file#=file_id;
DT FILE_NAME
----------- --------------------------------------------------
4736767 /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf
4736767 /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf
4736767 /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf
4736767 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf
SQL>
In summary, we cannot get a timestamp from the database for a row in a table that does not itself have a timestamp column unless we also have audit or we can use redo logs. We can use SCN but only at the block level and we could also do ID analysis to see what rows are missing but not what is in those rows. We could use off line data block analysis but unless a row was held in the ITL (Interested Transaction List) in the data block we would not get much futher.
SCNs are useful and can create an outline / boundary for instance if the last record of the 3rd block has an SCN and we can estimate a timestamp for the closed block we can estimate the time the block has existed and whether it falls into the range of the attack
#oracleace #sym_42 #oracle #forensics #database #security #scn #hacking #databreach
July 2026
- AI - 07/06/2026 by 07/06/2026
- AI Comparison for Oracle Security Code Generation - 07/07/2026 by 07/07/2026
- Cluster Objects in the Oracle Database - 07/13/2026 by 07/13/2026
- Can local LLM AI generate the top 100 most common passwords? - 07/23/2026 by 07/23/2026
- Is AI Like Oracle Security? - 07/28/2026 by 07/28/2026
- Sovereign AI - 07/29/2026 by 07/29/2026
Archives
Syndication
Powered by gm-rss 2.0.0
