as sysdba

[walter-weinmann]

How can I connect to SYS ?

[pesse]

You cannot at the moment. We'll need a new parameter for this.
Though I wouldn't recommend it, it might be a valuable feature.

[jgebal]

I think it is acutally a really bad idea to run tests from a SYSDBA account.
I would not add this functionality to cli.
If you need access to execute and procedure just use a user accout with proper role/privilege.

[walter-weinmann]

"Unfortunately, I'm working on software used by DBAs. For some code, access is restricted to the session user SYS. It is not my responsibility to handle this in any other way, but I don't want to exclude it from regression testing.

[pesse]

What about adding a parameter but outputting a warning that it's not recommended to run tests as sysdba, @jgebal?
I agree with you but I can also see some edge cases in which it is hard to go a different route and I don't want to exclude these people (who might suffer already anyway :) )

[jgebal]

I stand corrected @pesse
I've just realized that I gave exactly the same style of answer as Jeff Smith gave me, when I was asking for ability to drop multiple objects in SQLDeveloper navigator using Delete key. "We will not support that, as it's a bad idea (aka can be dangerous).

I do remember how disappointed I was though I find SQLDeveloper a great tool and Jeff is a great, very approachable guy. Always there to help.

So yes, I agree, utPLSQL-cli could have ability to run utPLSQL as sysdba.

It will require special permissions (unlocking) for SYS account, as by default it's blocked from executing code that runs with authid current_user.

This, and an additional warning on cli, should be more than enough to say - we do not encourage you to do this.

Do you think it should be cli or core responsibility to perform a check for SYS connection and raise a warning?

[pesse]

So you're human after all ;)
Responsibility should be cli because I have to setup the connection in a special way already.

[pesse]

I included the possibility to connect as SYSDBA, however every time I try to test it I get the following:

select ut.version() from dual;
ORA-06598: Nicht ausreichende INHERIT PRIVILEGES-Berechtigung
ORA-06512: in "UT3.UT", Zeile 1

@jgebal can you give me a hint what's causing this and can we circumvent it?

[pesse]

Played around a bit with grant inherit privileges on user sys to public, but didn't change anything.
I guess I'm doing it wrong 😁
@walter-weinmann on which Oracle version are you? Can you run select ut.version() from dual from your sysdba role?

[jgebal]

This is related to potential security risk when runnign packages that have AUTHID CURRENT_USER from SYS account.
Your code suddenly has DBA privs and can do whatever you want.

In general, you should never be using/running code that has AUTHID CURRENT_USER as SYS/SYSDBA unless you're 100% sure noone was messing with that code.

So you need to execute:
GRANT INHERIT PRIVILEGES ON USER SYS TO UT3;
ut again - probably bad idea and probably most DBAs would kill you for this :)

[jgebal]

See: https://docs.oracle.com/database/121/DBSEG/dr_ir.htm#DBSEG661