[
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:09 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:
First of all enable pgcrypto extension.
{code:java}
psql -U [pg_user] -d [pg_db_name] -c "CREATE EXTENSION pgcrypto;"{code}
{code:java}
UPDATE syncopeuser SET securityanswer = encode(digest(securityanswer,
'sha256'), 'hex') where securityanswer is not null;{code}
* MySQL 8+
[https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html]
Sample code:
{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]
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]
Sample code:
{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/]
> 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)
