Storing Secrets for Scripts: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
 
(19 intermediate revisions by the same user not shown)
Line 1: Line 1:
Secure strings provide a way to store passwords and other confidential information in QPR ProcessAnalyzer in a way that the information can be used without users seeing the original plaintext. For example in ETL scripts, SAP, Salesforce and ODBC passwords can be stored as secure strings, which can then be referred by their names in the ETL script commands.
Secrets provide a method to store passwords and other confidential data in QPR ProcessAnalyzer, and use them without being able to see the stored secret values. For example in scripts, SAP, Salesforce and ODBC passwords can be stored as secrets, which can be referred by their names in the script commands.


When the secure strings are stored, the purpose (called type of the secure string) of the secure string is also defined. Following types are available: ''Odbc connection strings'', ''SAP passwords'' and ''Salesforce passwords''. For example, if a secure string type is ''SAP password'', it cannot be used as an ODBC connection string.
There are project-specific and global secrets. To use a secret, the user needs to have ''GenericRead'' permission to the project (or global ''GenericRead'' to use global secrets). To define a secret, the ''ManageProject'' permission to the project is needed (or global ''ManageProject'' to define global secrets). If both a global and a project-specific secret with the same name and type are set, the project-specific secret will be used in that project, and thus the global secret is unavailable for that project.


Secure strings are project specific, so their permissions come from the project. To use a secure string, the user needs to have at least ''GenericRead'' permission to the project.
Each secret has a type which defines in which command the secret can be used. The purpose of the type is to improve security, so that the secret can only be used in the intended command.


== Storing Secure Strings ==
== Setting and listing secrets ==
Secrets can be set by calling the [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#SetSecret|SetSecret]] function for the project, or the corresponding generic context [[Generic_Functions_in_QPR_ProcessAnalyzer#SetSecret|SetSecret]] function.


Secure strings are stored in the QPR ProcessAnalyzer database in the '''PA_KEY_VALUE_STORE''' table. The database administrator can store new secure strings by following the instructions below.
There is a property [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#Project|Secrets]] for projects to list all secrets in the project. For the global secrets, there is the corresponding global [[Generic_Properties_in_Expression_Language#Secrets|Secrets]] property. Note that the secret value cannot be retrieved even by system administrators.


The PA_KEY_VALUE_STORE table has the following columns:
Example: Set a global and a project-specific secret:
* '''KVS_ID''': Row id. Leave empty as its filled automatically.
<pre>
* '''KVS_TYPE''': Type of the secure string to determine its purpose. Available values are:
SetSecret("sap", "SapReaderPassword", "I l0ve 5AP!");
** '''1''': ODBC connection string. Can be used in the [[DataFrame_in_Expression_Language#ImportOdbcSecure|ImportODBCSecure]] function and [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportOdbcQuery|ImportOdbcQuery]] command.
ProjectById(1).SetSecret("sap", "SapAdminPassword", "I l0ve 5AP!");
** '''2''': SAP password. Can be used in the [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportSapQuery|ImportSapQuery]] command.
</pre>
** '''3''': Salesforce password. Can be used in the [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportSalesforceQuery|ImportSalesforceQuery]] command.
* '''KVS_PROJECT_ID''': Id of the project, to which the secure string is stored. Project id's can be seen in the model properties.
* '''KVS_KEY''': Key (name) of the secure string. Use a descriptive key name. Note that there cannot be any duplicates within the same project id.
* '''KVS_VALUE''': The actual secured data, which is typically a password or a connection string.


Note: Currently [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportSqlQuery|ImportSqlQuery]] and [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportOleDbQuery|ImportOleDbQuery]] don't yet support defining password as a secure string.
Example: Show global secrets:
<pre>
ToJson(Secrets);
</pre>


== Using Secure Strings ==
Example: Show project's secret:
<pre>
ToJson(ProjectById(1).Secrets);
</pre>


Secure strings can be used in the following locations:
== Using secrets ==
* In the  following [[QPR ProcessAnalyzer Scripting Commands|SQL scripting commands]]: ImportOdbcQuery, ImportSalesforceQuery and ImportSapQuery.
Secrets can be used in the following commands:
* In the [[QPR_ProcessAnalyzer_Model_Datasources#Loading_Data_from_ODBC_Datasource|ODBC datasources of model]].
* [[SQL Scripting Commands|SQL scripting commands]]: ImportOdbcQuery, ImportSalesforceQuery and ImportSapQuery.
* In the [[DataFrame_in_Expression_Language#ImportOdbcSecure|ImportODBCSecure]] function (expression language)
* [[QPR_ProcessAnalyzer_Model_Datasources#Loading_Data_from_ODBC_Datasource|ODBC datasources of model]].
* [[DataFrame_in_Expression_Language#ImportOdbcSecure|ImportODBCSecure]] function (expression language)
* [[Generic_Functions_in_QPR_ProcessAnalyzer#CreateSnowflakeConnection|CreateSnowflakeConnection]] function (expression language)


[[Category: QPR ProcessAnalyzer]]
Note: Currently [[SQL Scripting Commands#--.23ImportSqlQuery|ImportSqlQuery]] and [[SQL Scripting Commands#--.23ImportOleDbQuery|ImportOleDbQuery]] don't yet support the secrets.

Latest revision as of 09:44, 27 March 2025

Secrets provide a method to store passwords and other confidential data in QPR ProcessAnalyzer, and use them without being able to see the stored secret values. For example in scripts, SAP, Salesforce and ODBC passwords can be stored as secrets, which can be referred by their names in the script commands.

There are project-specific and global secrets. To use a secret, the user needs to have GenericRead permission to the project (or global GenericRead to use global secrets). To define a secret, the ManageProject permission to the project is needed (or global ManageProject to define global secrets). If both a global and a project-specific secret with the same name and type are set, the project-specific secret will be used in that project, and thus the global secret is unavailable for that project.

Each secret has a type which defines in which command the secret can be used. The purpose of the type is to improve security, so that the secret can only be used in the intended command.

Setting and listing secrets

Secrets can be set by calling the SetSecret function for the project, or the corresponding generic context SetSecret function.

There is a property Secrets for projects to list all secrets in the project. For the global secrets, there is the corresponding global Secrets property. Note that the secret value cannot be retrieved even by system administrators.

Example: Set a global and a project-specific secret: 

SetSecret("sap", "SapReaderPassword", "I l0ve 5AP!");
ProjectById(1).SetSecret("sap", "SapAdminPassword", "I l0ve 5AP!");

Example: Show global secrets: 

ToJson(Secrets);

Example: Show project's secret: 

ToJson(ProjectById(1).Secrets);

Using secrets

Secrets can be used in the following commands:

Note: Currently ImportSqlQuery and ImportOleDbQuery don't yet support the secrets.

Retrieved from "https://wiki.onqpr.com/pa/index.php?title=Storing_Secrets_for_Scripts&oldid=26069"