OraFAQ Forum: SQL & PL/SQL » Prevent user from executing a script until they will have read and understood it

Home » SQL & PL/SQL » SQL & PL/SQL » Prevent user from executing a script until they will have read and understood it (18)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Prevent user from executing a script until they will have read and understood it [message #689651]

Fri, 08 March 2024 13:13

Darth Waiter
Messages: 78
Registered: October 2020

Member

Hi all,
I want to give users a script that they will need to run on their own but they first must take certain actions.
Of which they will learn by reading the script or at least the comment header in it. If they choose to ignore me it is their decision to make, but I have to use due diligence.
In MS SQL I achieve that by using 'set noexec on' in the first line. There is no such thing in Oracle, hence the question:

How can I prevent the script from running until the user will have read it and removed that line or two that break the script?
The script contains nothing but DDL, so correct me if I am wrong but I am under impression that I cannot wrap it in begin/end with 'whenever sqlerror exit' and 'raise PRORGRAM_ERROR' because that makes it PL/SQL, whereas my DDLs are SQL.
I need something along these lines but correct and working:

whenever sqlerror exit;
begin
raise PROGRAM_ERROR;
-- Take a backup and remove the above line before you run this script in production!!!
alter table "myschema"."mytable" modify ("mycolumn" char(10 char));
end;

So until the user reads and removes that line the script should fail. It occurred to me that I can leave it like that, but then I will have to tell them to remove more than one line.
As to who is the target audience of the script, they will most likely will be DBAs, but in this day and age they often reside physically 1/2 world away, behind layers of vendors/contractors/subcontractors, and their qualification/competency is not guaranteed, so I need to stay on the safe side and really prevent the script from making any changes until it is read and understood.

Thanks!

[Updated on: Fri, 08 March 2024 13:18]

Report message to a moderator

Re: Prevent user from executing a script until they will have read and understood it [message #689652 is a reply to message #689651]

Fri, 08 March 2024 13:42

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You cannot, whatever you do, be sure the user will read the script.
If they know they have to remove "raise PROGRAM_ERROR;" (or anything else), I bet, by experience, many will just do "s/raise PROGRAM_ERROR;//g" and run the script.

Quote:

so I need to stay on the safe side and really prevent the script from making any changes until it is read and understood.

Report message to a moderator

Re: Prevent user from executing a script until they will have read and understood it [message #689653 is a reply to message #689652]

Fri, 08 March 2024 17:36

Darth Waiter
Messages: 78
Registered: October 2020

Member

The best part is that I do not have to be sure. As long as the warning and any code lines that prevent them from successfully running the script w/o reading it or removing the 'bad' line, all my bases are covered. So I only need a technical solution, not a philosophical one. Layers will deal with the rest.
MS SQL makes that task very easy: 'set noexec on' followed with 'raiserror('Boo!', 18, 10) with log, nowait' makes damn sure that they will get nowhere w/o removing those lines.
Needs something like that for Oracle.

Report message to a moderator

Re: Prevent user from executing a script until they will have read and understood it [message #689654 is a reply to message #689653]

Sat, 09 March 2024 01:07

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Something like:

set errorlogging on;
-- Start of "Remove after reading" part
whenever sqlerror exit;
begin
  raise_application_error(-20000, 'Read and understand the script then remove these lines');
end;
/
-- End of "Remove after reading" part

Report message to a moderator

Re: Prevent user from executing a script until they will have read and understood it [message #689655 is a reply to message #689654]

Sat, 09 March 2024 07:37

Darth Waiter
Messages: 78
Registered: October 2020

Member

set errorlogging on;
whenever sqlerror exit;
begin
  raise_application_error(-20000, 'Read and understand the script then remove these lines');
end;

create table t(a int);
alter table t modify a char(1);
drop table t;

Quote:

Error starting at line : 4 in command -
begin
raise_application_error(-20000, 'Read and understand the script then remove these lines');
end;

create table t(a int);
alter table t modify a char(1);
drop table t;
Error report -
ORA-06550: line 5, column 1:
PLS-00103: Encountered the symbol "CREATE"
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Does it look like the script tries to execute the 'create table' despite the part to be removed?
It's less than ideal because the intentionally introduced error kind of becomes lost among the further error messages.
But if that is the best that I can get then it works.
Thanks in advance!

[Updated on: Sat, 09 March 2024 07:39]

Report message to a moderator