Home » RDBMS Server » Server Administration » audit use of dynamic sql (DB 19.x)
audit use of dynamic sql [message #684007] Thu, 18 March 2021 07:58 Go to next message
John Watson
Messages: 8622
Registered: January 2010
Location: Global Village
Senior Member
Is there a way to capture use of dynamic SQL? That would be EXECUTE IMMEDIATE, OPEN CURSOR...FOR... , DBMS_SQL, DBMS_SYS_SQL, probably other ways as well.

Thank you for any insight.
Re: audit use of dynamic sql [message #684009 is a reply to message #684007] Thu, 18 March 2021 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 67996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Audit captures all statements static as well as dynamic ones.
You mean you want only the dynamic ones?
In this case, knowing that dynamic statement are always parse, you can query v$sql, v$sqlarea or v$sqlstats with parse_calls=execution.
As far as I know there is "dynamic or static" flag on SQL statements.

Re: audit use of dynamic sql [message #684011 is a reply to message #684009] Thu, 18 March 2021 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 67996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
In this case, knowing that dynamic statement are always parse,

Correction: this is correct for EXECUTE IMMEDIATE but not for DBMS_SQL.

Re: audit use of dynamic sql [message #684016 is a reply to message #684011] Fri, 19 March 2021 14:49 Go to previous message
John Watson
Messages: 8622
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying. Just to explain the question, this is about code reviews to detect code potentially vulnerable to SQL injection: It would be very helpful to have some means of detecting all dynamic SQL.
Previous Topic: SYS Account locked
Next Topic: Database resources
Goto Forum:
  


Current Time: Tue Oct 19 01:13:27 CDT 2021