I wanted your opinion on the best way to store application settings. I've read the following on Ask Tom which recommended the attribute-value pair approach:
https://asktom.oracle.com/pls/apex/f?p=100:11::::RP:P11_QUESTION_ID:9538379900346218883 In my case, we are building a SaaS solution and these settings would be stored per subscriber. My thought was to use a JSON column on my subscriber table with appropriate get/set procedures created for each setting. The JSON model allows for more than just single value settings (or arrays of single values if implementing a child table). With JSON I have much more flexibility in my data model for each individual setting. I just wanted to get your opinion of this approach.
Related to this, a small number of settings may be sensitive data that we will want to encrypt, such as keys to external REST services (again, per subscriber). I know that we can encrypt the entire column if it is a BLOB column, but this seems a bit overkill for the few pieces of data we are talking about. I have not dealt much with encryption, and so I was wondering if it is a valid approach to simply encrypt/decrypt only specific attributes in the JSON document?
Thanks so much for all you do to educate the Oracle developer community!
Regards,
Rodney
When storing the data as JSON:
Pro: you can store whatever you want
Con: you can store whatever you want
:)
As you're comparing JSON to a free-form list of key-value pairs, you already have to carefully manage the values to ensure they're all present, spelt correctly, etc. So the free-form nature is less of a downside than usual.
The JSON model allows for more than just single value settingsDo you have an example where you want to do this and it makes the app design easier/faster/etc.? If so use JSON. If not, why do you think you'll need it?
With a table like:
create table app_config (
config_parameter varchar2(100) not null primary key,
config_value varchar2(1000) not null
);
You could still store the values as JSON if you wish.
When it comes to storing keys, I would keep these
separate from all other configuration data. A data breach where you leak sensitive data like this would be incredibly damaging to your company. So you want to keep it as safe as possible.
Increasing security generally
decreases usability. As you say, encrypting all config data just to protect a few sensitive items adds overhead. So keep them separate.
As an aside:
I know that we can encrypt the entire column if it is a BLOB column
You say you're on 21c, so if you do store JSON data, you should use the JSON data type. You could do it with the above table if you wish and store single values as JSON scalars:
create table app_config (
config_parameter varchar2(100) not null primary key,
config_value json not null
);
insert into app_config values ( 'NUMBER_VALUE', json_scalar ( 42 ) );
insert into app_config values ( 'STRING_VALUE', json_scalar ( 'text' ) );
insert into app_config values ( 'ARRAY_VALUE', json_array ( 'array', 'of', 'stuff' ) );
select * from app_config;
CONFIG_PARAMETER CONFIG_VALUE
NUMBER_VALUE 42
STRING_VALUE "text"
ARRAY_VALUE ["array","of","stuff"]