Generating cryptographic secure IDs instead of sequential identity / auto increment

While this could be implemented externally, this does need to store and atomically access state (the permutation position or last id), which means implementing externally would be grossly inefficient (it's the equivalent of running a subsequent

 UPDATE table SET crypto_id = FN_CRYPTO(autoincrement_id)  WHERE autoincrement_id=LAST_INSERT_ID()

You could use generated/virtual column to avoid running proposed UPDATE for every insert:

-- pseudocodeCREATE TABLE tab(   autoincrement_id INT AUTO_INCREMENT,   crypto_id <type> GENERATED ALWAYS AS (FN_CRYPTO(autoincrement_id)) STORED);-- SQL Server example, SHA function is an example and should be replacedCREATE TABLE tab( autoincrement_id INT IDENTITY(1,1), crypto_id AS (HASHBYTES('SHA2_256',CAST(autoincrement_id AS NVARCHAR(MAX))))     PERSISTED);

db<>fiddle demo

More info:

• SQL Server computed columns

• Computed / calculated / virtual / derived columns in PostgreSQL

• Column Depending on other column

EDIT by Dinu

If you use SHA, don't forget to concatenate a secret salt to the autoincrement_id; alternately, you could use i.e. AES128 to encrypt the autoincrement_id with a secret password and IV.

Also worth noting: any DB user with access to the table DDL will have access to your secret salt/key/iv. If this is of concern to you, you can use a parameterized stored procedure i.e. FN_CRYPTO(id,key,iv) instead and send them along with every insert.

To retrieve the crypto_id on the app-side without needing a subsequent query, you would need to replicate the encryption function app-side to run on the returned autoincrement_id. Note: if using autoincrement_id as byte array for AES128, be very careful about endianness, it may differ DB and app-side. The only alternative is to use the OUTPUT syntax of mssql, but that is specific to mssql and it requires running the ExecuteScalar API instead of ExecuteNonQuery.

Just a thought... Is the DB itself secure? If so, you might consider a "key pool" table that holds a list of pseudo-random keys and a "status" column for each key in the table. Then you could assign the next key when required. The key pool can get populated during idle times and/or based on a trigger if the available keys drops below a set threshold.

Again, this method would depend on being able to secure the key pool table, but it would assure that the keys assigned would be random and unique.

Also, you would need to be sure that you don't create concurrency issues, but this could be done with a stored procedure, and still should be faster than generating the secure IDs on demand.