decrypt default and Implicit Grants

If you do not have explicit or implicit permission on a table, SAP ASE returns the decrypt default value.

In this example (using the emp table), the database owner creates the p_emp procedure which selects from the emp table that he or she owns:

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, SAP ASE returns the decrypt default value of emp.ssn.

In this next example, “joe,” who does not own the database, creates the v_emp view, which selects from the emp table. 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

SAP ASE 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