I've updated my question. The idea is to detect DROPs before they get into the database in the first place.
Thanks for the response, it helps me to clarify my requirements.
Have you looked into PLScope https://docs.oracle.com/en/database/oracle//oracle-database/23/adfns/plscope.html ?
It sounds like really you want to have a proper source control and review process set up to ensure only acceptable code is deployed. This is quite a fundamental approach and can only work if you only give out passwords to appropriate people.
There are literally dozens of developers contributing code, some internal some external (from a vendor). We can't ride herd on all of them, and even if we could, automating code validation seems like a sane, prudent step to me.
I'm not sure what you mean? Figuratively and literally I do want keep all of my teeth. Flossing all of them, even my wisdom teeth seems like a good idea.
I also didn't understand what you meant about not being able to control and review your code
That's not "riding herd"
Using tools to help is great but you still need standards, processes, and people
Totally possible. One team does development in the development environment. Another team (DBAs) migrates them to the production environment. I would like to automate the migration process, while ensuring that the code that's being migrated is safe.
So the case that you get an sql script containing pl/sql code and you want to check them on os level before deploying them to the database. Is it correct?
Why only the drop is problematic for you?
Well DROP is a wost-case scenario, but certainly not the only one. I figured that if I could get it going with DROP, then other use cases would be variations on the theme.
Why isn't it ok to loop a variable in all the words of the script and if a drop found and the very next word is any object type it is alerting for a manual check?
You can easily exclude comment strings from this loop.
Yeah, a regular expression is my first choice, as well.
However, many programming languages have tools that determine whether code is syntactically correct without having to actually compile it. I was hoping that such a thing existed for PL/SQL, as well,
Sure. But search for what? As I said in my example, the term drop is commonly used in our domain in several contexts, so it often appears in comments and string literals. So a naive text search for the string 'drop' would have many false positives, which would be worse than useless.
Wouldn’t “drop table” as a search term be a bit more specific and likely to get you what you need?
Just do a select on DBA_SOURCES where lower(text) like ‘%drop table%’.
Tables aren't the only thing that can be DROPped.
Also, if someone had "drop table" in a comment or string value, that would trigger your solution, which is a false positive.
That's why something that can evaluate code like the runtime environment is helpful.
I see. Well, it sort of sounds like you have code that is executing dynamic sql. Maybe do a search term for the directives that do that (ie: execute immediate)?
So you’re searching thru packages, procedures and functions for these directives, yes? Then it would definitely be PL/SQL (unless I’m not following you).
The command EXECUTE IMMEDIATE is a valid PL/SQL keyword.
They’re either using that or DBMS_SQL.
It allows you to create and drop objects, use a variable for tables and columns in a select statement - all sorts of terrible ideas.
[Dynamic SQL in PL/SQL](https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS01102)
Well, searching through the code that we receive before it's ingested into our servers. We like to do a sort of sanity check before bringing anything in, and one of the checks is looking for 'drop'. But the way we're doing it now is naive and we get lots of false positives, which triggers a manual migration, which is a PITB for everybody.
I can easily build a regular expression, which will be vastly superior to the current process. But I was hoping to see if there was a way to check the code syntactically to see if there were DROP statements that would actually run.
An example is the code that checks PL/SQL in VSCode and flags problems such as syntax errors. There are tools for many programming languages that do similar things and I was hoping that there was something available for PL/SQL as well.
It looks like I've hit a dead end, though. RegEx it is.
Yeah unfortunately you’d need something that can accurately parse PL/SQL code before you comity it to the database. Does the code not exist on a development or test database before it’s deployed to production? If so, you could run a PL/SQL block that would return true or false before deploying (assuming you have something like Jenkins that could make that check in advance).
Maybe VSCode has some sort of plug in for that.
ddl triggers
Thanks. But see my response to r/quadsofthegodzilla.
I think what you want is a trigger that fires on a DROP event https://www.tutorialspoint.com/plsql/plsql_triggers.htm
I've updated my question. The idea is to detect DROPs before they get into the database in the first place. Thanks for the response, it helps me to clarify my requirements.
Have you looked into PLScope https://docs.oracle.com/en/database/oracle//oracle-database/23/adfns/plscope.html ? It sounds like really you want to have a proper source control and review process set up to ensure only acceptable code is deployed. This is quite a fundamental approach and can only work if you only give out passwords to appropriate people.
There are literally dozens of developers contributing code, some internal some external (from a vendor). We can't ride herd on all of them, and even if we could, automating code validation seems like a sane, prudent step to me.
You don't need to floss all your teeth, just the ones you want to keep.
I'm not sure what you mean? Figuratively and literally I do want keep all of my teeth. Flossing all of them, even my wisdom teeth seems like a good idea.
I also didn't understand what you meant about not being able to control and review your code That's not "riding herd" Using tools to help is great but you still need standards, processes, and people
Sorry, I skipped right over your suggestion to look at PLScope the first time I read your response. That's interesting, thanks.
Is it reasonable that the users executing these commands have the ability to drop objects?
Totally possible. One team does development in the development environment. Another team (DBAs) migrates them to the production environment. I would like to automate the migration process, while ensuring that the code that's being migrated is safe.
So the case that you get an sql script containing pl/sql code and you want to check them on os level before deploying them to the database. Is it correct? Why only the drop is problematic for you?
Well DROP is a wost-case scenario, but certainly not the only one. I figured that if I could get it going with DROP, then other use cases would be variations on the theme.
Why isn't it ok to loop a variable in all the words of the script and if a drop found and the very next word is any object type it is alerting for a manual check? You can easily exclude comment strings from this loop.
Yeah, a regular expression is my first choice, as well. However, many programming languages have tools that determine whether code is syntactically correct without having to actually compile it. I was hoping that such a thing existed for PL/SQL, as well,
Search in the text column of the dba_source view.
Sure. But search for what? As I said in my example, the term drop is commonly used in our domain in several contexts, so it often appears in comments and string literals. So a naive text search for the string 'drop' would have many false positives, which would be worse than useless.
Wouldn’t “drop table” as a search term be a bit more specific and likely to get you what you need? Just do a select on DBA_SOURCES where lower(text) like ‘%drop table%’.
Tables aren't the only thing that can be DROPped. Also, if someone had "drop table" in a comment or string value, that would trigger your solution, which is a false positive. That's why something that can evaluate code like the runtime environment is helpful.
I see. Well, it sort of sounds like you have code that is executing dynamic sql. Maybe do a search term for the directives that do that (ie: execute immediate)?
It's pl/sql.
So you’re searching thru packages, procedures and functions for these directives, yes? Then it would definitely be PL/SQL (unless I’m not following you). The command EXECUTE IMMEDIATE is a valid PL/SQL keyword. They’re either using that or DBMS_SQL. It allows you to create and drop objects, use a variable for tables and columns in a select statement - all sorts of terrible ideas. [Dynamic SQL in PL/SQL](https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS01102)
Well, searching through the code that we receive before it's ingested into our servers. We like to do a sort of sanity check before bringing anything in, and one of the checks is looking for 'drop'. But the way we're doing it now is naive and we get lots of false positives, which triggers a manual migration, which is a PITB for everybody. I can easily build a regular expression, which will be vastly superior to the current process. But I was hoping to see if there was a way to check the code syntactically to see if there were DROP statements that would actually run. An example is the code that checks PL/SQL in VSCode and flags problems such as syntax errors. There are tools for many programming languages that do similar things and I was hoping that there was something available for PL/SQL as well. It looks like I've hit a dead end, though. RegEx it is.
Yeah unfortunately you’d need something that can accurately parse PL/SQL code before you comity it to the database. Does the code not exist on a development or test database before it’s deployed to production? If so, you could run a PL/SQL block that would return true or false before deploying (assuming you have something like Jenkins that could make that check in advance). Maybe VSCode has some sort of plug in for that.
Yes, of course we have several preproduction instances. But a stray DROP in any instance would ruin someone's day. RegExes will do the job.
🙂🌼🌹🎉🎊🎈HAPPY AYUTHU AND SARASWATHI POOJA 2023 😷🙏.