SQL & PL/SQL

Hello,

I have a big problem with that: I just cannot use execute immediate to do what I want.

The goal is that another user than sysdba needs to be able to open/close the wallet (we are sysdba and our client doesn't want us to be able to open the wallet, and we don't want the client to have sysdba), so I take advantage of the fact that procedures are executed by default with the privileges of the user which writes it. So, I try and create a PL/SQL procedure as sys and grant execute rights to the client user.

To open/close a wallet, the command is:

alter system set encryption wallet open/close identified by "thepasshere";

Note the double quotes... They are the problem here (passwords are mixed case and contain special characters). Right now, the only solution I came up with is:

create or replace procedure open_wallet (passwd in varchar2) is
begin
execute immediate 'alter system set encryption wallet open identified by "' || passwd || '";';
end;
/

But that's vulnerable to code injection... So, instead, I tried:

create or replace procedure open_wallet (passwd in varchar2) is
cmd varchar2(512);
begin
cmd != 'alter system set encryption wallet open identified by ":1"';
execute immediate cmd using passwd;
end;
/

which yields "ORA-00911: invalid character" - uh?
Now, if I try:

create or replace procedure open_wallet (passwd in varchar2) is
cmd varchar(512);
begin
cmd := 'alter system set encryption wallet open identified by :1;';
execute immediate cmd using passwd;
end;

it answers "ORA-28357: password required to open the wallet"

Aaargh! I see no way around that :(

Is there a solution at all?