If you do not have explicit or implicit permission on a table, Adaptive Server returns the decrypt default value.
In this example (using the emp table described above), the DBO creates the p_emp procedure which selects from the DBO-owned emp table:
create procedure p_emp as select name, ssn from emp grant exec on p_emp to corp_role
Because you have the corp_role, you have implicit select and decrypt permission on emp
exec p_emp
name ssn ------------------------------ ------------ Tinna Salt 123-45-6789 Joe Cool 321-54-9879
If the emp table and p_emp stored procedure have been created by different users, you must have select permission on emp to avoid permissions errors. If you have select permission but not decrypt permission, Adaptive Server returns the decrypt default value of emp.ssn.
In this next example, “joe,” a non-DBO user, creates the v_emp view, which selects from the DBO-owned emp table. In this case, any permissions granted on the view are not implicitly applied to the base table.
create view v_emp as select name, ssn from emp grant select on v_emp to emp_role grant select on emp to emp_role grant decrypt on v_emp to emp_role
Although you have the emp_role, when you issue:
select * from joe.v_emp
Adaptive Server returns the following because decrypt permission on dbo.emp.ssn has not been granted to the emp_role, and there is no implicit grant to emp_role on dbo.emp.ssn:
name ssn -------------------------- --------------- Tinna Salt 000-00-0000 Joe Cool 000-00-0000
