Chapter 3 DBMS Reference Guide

SqlPermQuery

SQL Query to reverse engineer object permissions.

The result must conform to proper SQL syntax.

Example

In Sybase Adaptive Server Anywhere 8:

{ GRANTEE, GRANTOR,  PERMISSION}

select 
u1.user_name grantee, u.user_name grantor, 
case
when (p.privilege_type = 1 and p.is_grantable ='Y' ) then 'SELECT+'
when (p.privilege_type = 1)  then 'SELECT'
when (p.privilege_type = 8 and p.is_grantable ='Y' ) then 'UPDATE+'
when (p.privilege_type = 8)  then 'UPDATE'
when (p.privilege_type = 16 and p.is_grantable ='Y' ) then 'REFERENCES+'
when (p.privilege_type = 16)  then 'REFERENCES'
end
permission
from sysuserperm u, sysuserperm u1, syscolumn s, syscolperm p, systable t
where 
(t.table_name = %.q:TABLE%) and
(t.table_id = p.table_id) and
(s.column_name = %.q:COLUMN% ) and
(s.table_id = t.table_id) and
(u1.user_id = p.grantee) and
(u.user_id = p.grantor)

Copyright (C) 2005. Sybase Inc. All rights reserved.