The decrypt default parameter does not affect target lists of insert and update statements.
If you use a column with a decrypt default value in the where clause of an update or delete statement, Adaptive Server may not update or delete any rows. For example, when using the emp table and permissions from the previous examples, if you do not have the hr_role and issue the following query, Adaptive Server does not delete the user’s name:
delete emp where ssn = '123-45-6789' (0 rows affected)
Decrypt default attributes may indirectly affect inserting and updating data if an application, particularly one with a graphical user interface (GUI) process:
Selects data
Allow a user to update any of the data.
Applies the changed row back to the same or a different table
If the user does not have decrypt permission on the encrypted columns, the application retrieves the decrypt default value and may automatically write the the unchanged decrypt default value back to the table. To avoid overwriting valid data with decrypt default values, use a check constraint to prevent these values from being automatically applied. For example:
create table customer (name char(30)), cc_num int check (cc_num != -1) encrypt decrypt_default -1
If the user does not have decrypt permission on cc_num and selects data from the customer table, this data appears:
name cc_num -------------------- ------------ Paul Jones -1 Mick Watts -1
However, if the user changes a name and updates the database, and the application attempts to update all fields from the values displayed, the default value for cc_num causes Adaptive Server to issue error 548:
"Check constraint violation occurred, dbname = <dbname>, table name = <table_name>, constraint name = <internal_constraint _name>"
Setting a check constraint protects the integrity of the data. For a better solution, you can filter these updates when you write the application’s logic.
