I created a table with a column
IS_CONFIDENTIAL CHAR(1)
and I added this clause to my query if the user should not see the confidential data
AND IS_CONFIDENTIAL <> 'Y'
Now the user could not see the confidential records. All good!
But he could not see few other records which were not confidential ?? I checked the table and found records with IS_CONFIDENTIAL values 'Y','N' and NULL and the records with NULL value didn't show up !!
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
IS_CONFIDENTIAL <> 'Y' does not mean it to return rows with NULL value !!
This is what I did (though there could be multiple solutions and may be better :))
IS_CONFIDENTIAL NOT NULL CHAR(1)
Or I should have changed my query to AND ( IS_CONFIDENTIAL = 'N' OR IS_CONFIDENTIAL IS NULL)
Note: I agree that positive check is always better. But I thought what if there are more values than Y and N, IS_CONFIDENTIAL<> 'Y' was the choice for me.
Bulb: Did you know you can enable hibernate to print the queries it executes! It is very helpful.
Bulb: CHAR vs VARCHAR
IS_CONFIDENTIAL CHAR(1)
and I added this clause to my query if the user should not see the confidential data
AND IS_CONFIDENTIAL <> 'Y'
Now the user could not see the confidential records. All good!
But he could not see few other records which were not confidential ?? I checked the table and found records with IS_CONFIDENTIAL values 'Y','N' and NULL and the records with NULL value didn't show up !!
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
IS_CONFIDENTIAL <> 'Y' does not mean it to return rows with NULL value !!
This is what I did (though there could be multiple solutions and may be better :))
IS_CONFIDENTIAL NOT NULL CHAR(1)
Or I should have changed my query to AND ( IS_CONFIDENTIAL = 'N' OR IS_CONFIDENTIAL IS NULL)
Note: I agree that positive check is always better. But I thought what if there are more values than Y and N, IS_CONFIDENTIAL<> 'Y' was the choice for me.
Bulb: Did you know you can enable hibernate to print the queries it executes! It is very helpful.
Bulb: CHAR vs VARCHAR
No comments:
Post a Comment