I have a Talend job where I can write down an .SQL query and then run in it on multiplte databases.
I have a context parameter context.admin with a boolean value. If the admin = true then i want to connect to the databases with a DB user with full permission. If admin = false I only want to use my database user with only read permission.
One way of solving this is using the run if triggers and go one way if admin is true and the other way if false and then have two DBInputs with different username and password. I don't really like this approach since it's a pretty big job and i have two make a lot of splits like this.
Another way would be to save the username and password for the database also in a parameter. This way I could dynamicly change the name of the db user and pw depending on the value in context.admin.
I have no idea if this is a safe way? If i hard code my users and pw in some parameter or variables they can be easy hacked or not?
Yes, values stored in context variables are easily reversible. So storing sensible information there might not be the safest option.
There are some options you could try that would make duplicating every step on your Job unnecessary. I'd suggest you try these options to see what fits best for your project:
Alternative 1: Create only one user and password context variables and you can keep them empty. Then you can pass their corresponding values as parameters depending if you want to run the Job as admin or not.
To pass context variables as parameters, you can build the Job and run its .bat or .sh script and add the parameters like following:
If you're using TAC or TMC it would be even easier using one of these interfaces.
Alternative 2: Actually this is more like a complement to the first alternative so you don't need to pass context variables as parameters.
Store context variables content in a database or a vault, for example. Then you can connect the "credential_check_admin" to a tPrejob component to get the context values according with your need.
If you use a database for this purpose, you can write some query like "SELECT username, password FROM mytable WHERE permission='"+context.permission+"'", where permission context variable would be your only parameter and you could fill it using "write" or "readonly".
This way might be a little more complex to implement but basically you can use tContextLoad component to fill context variables values based on what the "credential_check_admin" job returns.
I tried something myself but i don't know if it's a good idea.
What I have done is that I created a new table in my database where I inserted my two database users credentials.
INSERT INTO talend_db_users (username, "password")
VALUES
('admin_user', 'mypassword'),
('read_user', 'mypassword2');
Then with help of TDBinput and tJavaRow I fetched the both users and pw's and placed them into 4 context.variables.
context.read_user= input_row.username;
context.read_user_pw= input_row.password;
context.admin_user= input_row.username;
context.admin_user_pw= input_row.password;
After that I have a tJava component where I check if the user is admin or not. When true the final context db_user and db_pw would have the admin credentials and if false only the read ones.
if (context.is_admin) {
context.db_user = context.admin_user;
context.db_pw = context.admin_user_pw;
} else {
context.db_user = context.read_user;
context.db_pw = context.read_user_pw;
}
After that i can use context.db_user and context.db_pw everywhere in the connection settings .
Is this approach safe since I don't have any hardcoded values in the contexts and i grab them from a database instead?
First I even inserted the passwords with crypt('mypassword',gen_salt('bf')) but then I can't decrypt them back inside of Talend so right now my password are hardcoded in the Database table which i think is ok.
That's a nice approach in my opinion. It is important that it works for you, establish it as a pattern for the whole project, document it well and of course, make it as safe as possible.
Regarding its safety, I believe it is as safe as storing credentials in a database can be and you did well looking for ways of encrypt it. I would suggest you take a look at this article for a reference about using jasypt: https://www.talend.com/resources/best-practices-for-using-context-variables-part-2/
And if you have the opportunity, please take a look at some dedicated solutions for storing secrets, like CyberArk, Vault by HashiCorp or other alternatives.
