Welcome to Qlik Community forum and thanks for reaching out here!
Could you please let us know the exact scenario? The Oracle database is source database, or target database, and what time do you want to run the procedure ? Before starting up the task, or at the end of the Full Load stage etc.
BTW, what's the purpose of 'ALTER ROLE'? a sample is helpful to understand the request.
Regards,
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
We don't want Qlik user to log into SOURCE PRODUCTION database with the DEFAULT ROLE due to STIG/security reason; therefore once in then we would want to execute the following stored procedure
CREATE OR REPLACE PROCEDURE SET_ROLE(i_role IN VARCHAR2, i_password IN VARCHAR2) AUTHID CURRENT_USER AS
BEGIN
EXECUTE IMMEDIATE 'ALTER ROLE '|| i_role ||' IDENTIFIED BY '||i_password||'';
END; /
EXECUTE PROCEDURE SET_ROLE (i_role, i_password);
How do you call the SET_ROLE stored procedure in Qlik Replicate?
@lqthinguyen - your suggestion is not reasonable. It is totally insecure. Your security team will not, should not, ever accept this as it suggests to code passwords in plain text.
Just use a proper login in the proper target role and don't try to fake it logging in through an intermediate role. In some companies that is a fireable offense!
You should work with your security folks if need be with the help of Qlik Professional services to explain how the standard Replicate login method encrypts passwords and avoids plain text passwords such as you suggested.
Please carefully study chapter "3 Security considerations" in the Replicate UserGuide.
For Oracle (and SQLserver) Replicate even has a mechanism to 'change the password and throw away the key' so to speak. Check out : "4.5 Configuring Replicate to automatically replace the user-entered password". Using this, the provided password is used only once, only to replace it with an Replicate generated random password, only ever stored in encrypted format. This means that no Replicate Operator can get at it, you DBA provides a password once and it is change such that no person knows it. That should satisfy the most stringent requirements.
I believe there is a mis-understanding or just didn't communicate to the team clearly
your suggestion is not reasonable. It is totally insecure. Your security team will not, should not, ever accept this as it suggests to code passwords in plain text. -
THE PASSWORD WILL BE ENCRYPTED. As a DBA/software engineer with 30 years under my credential so there are certain things are NO NO but then even with encrypted the password there is always a way to decrypt it don't you think 🙂 I am just being facetious 😉
May I read the rest of your reply because I couldn't pass beyond your first paragraph. My question was how to call a stored procedure because the DBA on the other side wants to move away from the default profile and role
For starters, I think the answer to the question is going to be NO, so any discussion is probably moot. Most likely direction would be a private syntax, but I don't see an 'anchor' spot for the request like some environment/nls control statement. The closest statement currently used in the syntax is perhaps "change_password": "ALTER USER ${QO}${USER_NAME}${QC} IDENTIFIED BY ${QO}${PASSWORD}${QC} REPLACE ${QO}${OLD_PASSWORD}${QC}", - but that doesn't help you.
Knocking on the door with one name, and than changing to another to do the work just does not sit right by me. I think it creates more problem than it solves. If there is honest work to be done, than just show your name (role) and be registered.
If that proposed i_role/i_password is provided through Replicate, I don't see how you are going to encrypt or otherwise hide this.
>> May I read the rest of your reply
You probably should. Specifically the alter-password option. It is very annoying when in place, but also very secure.
