[ https://issues.apache.org/jira/browse/SYNCOPE-1666?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17502157#comment-17502157 ]
Andrea Patricelli edited comment on SYNCOPE-1666 at 3/7/22, 9:07 AM: --------------------------------------------------------------------- In order to upgrade existing enviroments using security questions and answers the best choice is need to act directly on database. Here are some native utilities for the supported DBMSes: * PostgreSQL 12+: [https://www.postgresql.org/docs/12/pgcrypto.html#id-1.11.7.34.5] Sample code: {code:java} psql -U [pg_user] -d [pg_db_name] -c "CREATE EXTENSION pgcrypto;" UPDATE syncopeuser SET securityanswer = encode(digest('the security answer', 'sha256'), 'hex') where securityanswer is not null;{code} * MySQL 8+ [https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html] {code:java} UPDATE syncopeuser SET securityanswer = SHA2(securityanswer, 256) where securityanswer is not null; {code} * Oracle [https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_CRYPTO.html] [https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647] Sample code: {code:java} UPDATE syncopeuser SET securityanswer = rawtohex(standard_hash ( securityanswer, 'SHA256')) where securityanswer is not null;{code} * SQLServer 2017+ [https://docs.microsoft.com/it-it/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15] Sampple code: {code:java} UPDATE syncopeuser SET securityanswer = select CONVERT(VARCHAR(MAX), (SELECT HASHBYTES('SHA2_256',securityanswer)), 1) where securityanswer is not null;{code} If you need more control you can even opt for a BASH or Python script that generates data to write on the database. For example you can generate with BASH terminale SHA2 strings as shown here: [https://beamtic.com/terminal-hash-making|http://example.com/] was (Author: andreapatricelli): In order to upgrade existing enviroments using security questions and answers the best choice is need to act directly on database. Here are some native utilities for the supported DBMSes: * PostgreSQL 12+: * [https://www.postgresql.org/docs/12/pgcrypto.html#id-1.11.7.34.5] * Here a sample code to hash in SHA256 {code:java} psql -U [pg_user] -d [pg_db_name] -c "CREATE EXTENSION pgcrypto;" UPDATE syncopeuser SET securityanswer = encode(digest('the security answer', 'sha256'), 'hex') where securityanswer is not null;{code} * MySQL 8+ [https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html] UPDATE syncopeuser SET securityanswer = SHA2(securityanswer, 256) where securityanswer is not null; * Oracle # [https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_CRYPTO.html] # [https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647] {code:java} UPDATE syncopeuser SET securityanswer = rawtohex(standard_hash ( securityanswer, 'SHA256')) where securityanswer is not null;{code} * SQLServer 2017+ * [https://docs.microsoft.com/it-it/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15] {code:java} UPDATE syncopeuser SET securityanswer = select CONVERT(VARCHAR(MAX), (SELECT HASHBYTES('SHA2_256',securityanswer)), 1) where securityanswer is not null;{code} If you need more control you can even opt for a BASH or Python script that generates data to write on the database. For example you can generate with BASH terminale SHA2 strings as shown here: [https://beamtic.com/terminal-hash-making|http://example.com] > Security Answer encryption > --------------------------- > > Key: SYNCOPE-1666 > URL: https://issues.apache.org/jira/browse/SYNCOPE-1666 > Project: Syncope > Issue Type: Improvement > Components: core > Affects Versions: 2.1.10 > Reporter: Andrea Patricelli > Assignee: Andrea Patricelli > Priority: Major > Fix For: 2.1.11, 3.0.0 > > > Security answer is stored as cleartext field, but, since contains sesitive > information, must be encrypted. We hav to use the same algorithms available > for password. > Provide also an upgrade guide and a migration tool to encrypt passwords on > already existing installations. -- This message was sent by Atlassian Jira (v8.20.1#820001)