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, such as:
Selects data
Allows the user to update any of the data
Applies the 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 over-writing valid data with decrypt default values, you can 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
When the application selects data from the customer table, the user sees this data if he or she does not have decrypt permission on cc_num:
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>"
This protects the integrity of the data. For a better solution, you can filter these updates when you write the application’s logic.
