utPLSQL V2-3-1 not creating synonym and ut_i_spool_temp.sql not found

[hichem19alg]

Describe the bug
Hello everyone,

I am under Oracle 10g so I have to use utPLSQL V2-3-1 but I have some ussues.
When I try to install the framework through SQLPlus I get a message saying that ut_i_spool_temp.sql cannot be found, and when I look in the install folder I can't find a file with that name.

It seems illogical to me because the name of the file (ut_i_spool_temp.sql) is mentioned in other files like ut_i_synonyms.sql.

The second problem, and I think it relates to the first, is when creating a test package, Oracle tells me that "UT.expect" cannot be found, The synonymous section of my UT3 schema is completely empty, and therefore the type section.

I appreciate your efforts to provide us with this great framework and hope you can help me solve this problem.

Thanks and Best regards
Provide version info
utPLSQL V2-3-1
Oracle 10g
SQL Develpoer 20.4

[pesse]

Hey @hichem19alg
utPLSQL V2-3-1 is 5 years old and not maintained as a separate project.
I'm really sorry, but we won't be able to help you with that, unless you upgrade to at least Oracle 11 so you can use the most recent version of the framework 😶

[hichem19alg]

@pesse Thanks for replying.

Yes I understand no worries.

I also have another bug to report, this one is in the latest version.

The "Grant create any context to & ut3_usert" command is missing in the "create_utplsql_owner.sql" file. I think it's mandatory to grant context creation to UT3 schema.

Thank you

[pesse]

Why do you think it is mandatory for UT3 to create any context? The framework doesn't use Oracle CONTEXT at all, so why should we add it?
It will also make it a lot harder to have the framework installed on a database, because DBA usually are pretty restrictive with granting "ANY" privileges - for good reason.

[hichem19alg]

@pesse
I don't know if is it mandatory for the framework to work but a least it is mandatory for installation.

Look to "install.sql" file, in the following block of code :

@@check_sys_grants.sql "'CREATE TYPE','CREATE VIEW','CREATE SYNONYM','CREATE SEQUENCE','CREATE PROCEDURE','CREATE TABLE', 'CREATE CONTEXT'"

It will run "check_sys_grants.sql" file with all thous parameters including "CREATE CONTEXT".
If we run it without giving CREATE CONTEXT permission it will raise an error and stop the installation procedure.

To be honest I'm not professional at OracleDB and maybe it was my fault, but I tried to launch installation procedure and it stopped at that point, and when I asked my DBA to grant CREATE CONTEXT it worked for me.

[jgebal]

@pesse
We use context since some version to make meta-data available for use within test and it's setup.
You can for example find out in the beforetest procedure what is the test procedure name that will be called after before test is finished.

[jgebal]

It's documented here:
https://github.com/utPLSQL/utPLSQL/blob/develop/docs/userguide/annotations.md#sys_context

[jgebal]

@hichem19alg
The installation process is tested automatically as part of each change. I didn't check details but it seems like your install was not done using SYS (DBA) account.
I'm not at home at the moment so can't look into code for next two weeks.

[jgebal]

The lack of GRANT CREATE ANY CONTEXT to UT3 is a deliberate decision.
The create ANY context is a very strong privilege and therefore UT3 user could be mis-used to get access to this privilege.
The CREATE ANY CONTEXT privilege is only needed for installation of UTPLSQL, not at runtime and so I've decided not to add it as persistent privilege for that user.

I think that with the above we can clearly see that documentation is not correct and should be updated to reflect that.
User installing utPLSQL must have elevated privileges including CREZTE ANY CONTEXT and those privileges are only needed for duration of installation.

I think that I need to review all privileges again to see which of permanent privileges that utPLSQL has are actually needed at runtime and minimize the grants that are given at installation time to make the schema less prone for exploitation of grants.