Storing Secrets for Scripts: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
 
(23 intermediate revisions by the same user not shown)
Line 1: Line 1:
The secure strings provide a way to store passwords an other confidential information to QPR ProcessAnalyzer in a way that the information can be used, but no user can see the information in plaintext. For example, in ETL scripts SAP, Salesforce and ODBC passwords can be stored as secure strings, which can then be referred in the ETL script commands.
Secrets provide method to store passwords and other confidential data in QPR ProcessAnalyzer, so that they can be used without users being able to see the original plaintext. For example in ETL scripts, SAP, Salesforce and ODBC passwords can be stored as secrets, which can be referred by their names in the ETL script commands.


When the secure strings are stored, the purpose (type) of the secure string is also defined. Following types are available: Odbc connection strings, SAP passwords, Salesforce passwords, ADO.Net Sql connection strings and OleDb connection strings. For example, if a secure string's type SAP password, it cannot be used as an ODBC connection string.
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 ==
Secrets are project specific, so their permissions come from the project. To use a secret, the user needs to have ''GenericRead'' permission to the project. To define a secret, the ''ManageProject'' permission to the project is needed.


Secure strings are stored in the QPR ProcessAnalyzer database in the '''PA_KEY_VALUE_STORE''' table. The database administrator can store new secure strings, using the following instructions.
== Setting secrets ==
Secrets can be set by calling the [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#SetSecret|SetSecret]] function for Project entity. There is also the property [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#Project|Secrets]] for projects to list all secrets in the project. Note that the secret value cannot be retrieved even by system administrators.


The PA_KEY_VALUE_STORE table has the following columns:
== Using secrets ==
* '''KVS_ID''': Row id. Leave empty as its filled automatically.
Secrets can be used in the following commands:
* '''KVS_TYPE''': Type of the secure string to determine its purpose. Available values are:
* [[SQL Scripting Commands|SQL scripting commands]]: ImportOdbcQuery, ImportSalesforceQuery and ImportSapQuery.
** '''1''': ODBC connection string. Can be used in the [[Generic_Properties_and_Functions_in_QPR_ProcessAnalyzer_Expressions#ImportOdbcSecure|ImportODBCSecure]] function and [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportOdbcQuery|ImportOdbcQuery]] command.
* [[QPR_ProcessAnalyzer_Model_Datasources#Loading_Data_from_ODBC_Datasource|ODBC datasources of model]].
** '''2''': SAP password. Can be used in the [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportSapQuery|ImportSapQuery]] command.
* [[DataFrame_in_Expression_Language#ImportOdbcSecure|ImportODBCSecure]] function (expression language)
** '''3''': Salesforce password. Can be used in the [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportSalesforceQuery|ImportSalesforceQuery]] command.
* [[Generic_Functions_in_QPR_ProcessAnalyzer#CreateSnowflakeConnection|CreateSnowflakeConnection]] function (expression language)
** '''4''': Sql connection string. Can be used in the [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportSqlQuery|ImportSqlQuery]] command.
** '''5''': OleDb connection string. Can be used in the [[QPR_ProcessAnalyzer_Scripting_Commands#--.23ImportOleDbQuery|ImportOleDbQuery]] command.
* '''KVS_PROJECT_ID''': If of the project, to which the secure string is stored.
* '''KVS_KEY''': Key of the secure string. Use a descriptive value. Note that there cannot be any duplicates within the same project id.
* '''KVS_VALUE''': The actual secured value, which is typically a password or a connection string.


== Using Secure Strings ==
Note: Currently [[SQL Scripting Commands#--.23ImportSqlQuery|ImportSqlQuery]] and [[SQL Scripting Commands#--.23ImportOleDbQuery|ImportOleDbQuery]] don't yet support the secrets.
 
The secure strings can be used in the following places:
* [[QPR ProcessAnalyzer Scripting Commands|SQL scripting commands]]: ImportOdbcQuery, ImportOleDbQuery, ImportSalesforceQuery, ImportSapQuery and ImportSqlQuery.
* In the [[QPR_ProcessAnalyzer_Model_Datasources#Loading_Data_from_ODBC_Datasource|models' ODBC datasources]].
* In the [[Generic_Properties_and_Functions_in_QPR_ProcessAnalyzer_Expressions#ImportOdbcSecure|ImportODBCSecure]] function in the expression language
 
[[Category: QPR ProcessAnalyzer]]

Latest revision as of 14:36, 6 March 2024

Secrets provide method to store passwords and other confidential data in QPR ProcessAnalyzer, so that they can be used without users being able to see the original plaintext. For example in ETL scripts, SAP, Salesforce and ODBC passwords can be stored as secrets, which can be referred by their names in the ETL script commands.

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.

Secrets are project specific, so their permissions come from the project. To use a secret, the user needs to have GenericRead permission to the project. To define a secret, the ManageProject permission to the project is needed.

Setting secrets

Secrets can be set by calling the SetSecret function for Project entity. There is also the property Secrets for projects to list all secrets in the project. Note that the secret value cannot be retrieved even by system administrators.

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=24476"