Database | Proc | Application | Created | Links |
sybsystemprocs | sp_ijdbc_gettableprivileges ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_ijdbc_gettableprivileges 4 */ 5 6 create procedure sp_ijdbc_gettableprivileges( 7 @table_qualifier varchar(32), 8 @table_owner varchar(32) = null, 9 @table_name varchar(257) = null) 10 as 11 12 declare @tablename varchar(257) 13 declare @tableowner varchar(128) 14 declare @privlist varchar(128) 15 declare @privdef varchar(128) 16 declare @searchstr char(3) 17 18 select @searchstr = 'SUV' /* SYSTEM USER VIEW types only */ 19 20 select @privlist = '193 ' + /* SELECT */ 21 '151 ' + /* REFERENCE */ 22 '197 ' + /* UPDATE */ 23 '196 ' + /* DELETE */ 24 '195 ' /* INSERT */ 25 26 select @privdef = 'SELECT ' + 27 'REFERENCE' + 28 'UPDATE ' + 29 'DELETE ' + 30 'INSERT ' 31 32 33 select @tablename = @table_name 34 select @tableowner = @table_owner 35 36 if (@tableowner is null) 37 begin 38 select @tableowner = '%' 39 end 40 41 if (@tablename is null) 42 begin 43 select @tablename = '%' 44 end 45 46 delete #tmp_gettableprivileges 47 48 insert #tmp_gettableprivileges 49 50 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(s.uid), 51 'TABLE_NAME' = name, 'GRANTOR' = user_name(s.uid), 52 'GRANTEE' = user_name(s.uid), 'PRIVILEGE' = 'SELECT', 'IS_GRANTABLE' = 53 substring('YESNO ', (select isnull((select p.protecttype 54 where p.id = s.id 55 AND p.protecttype != 2 56 AND p.grantor = p.uid 57 AND p.action = 193), 0)) * 3 + 1, 3) 58 FROM sysobjects as s, sysprotects as p 59 WHERE 60 name LIKE @tablename ESCAPE '\' 61 AND user_name(s.uid) LIKE @tableowner ESCAPE '\' 62 AND charindex(substring(type, 1, 1), @searchstr) != 0 63 AND not exists (select * from sysobjects s1, sysprotects p1 WHERE 64 name LIKE @tablename ESCAPE '\' 65 AND user_name(s.uid) LIKE @tableowner ESCAPE '\' 66 AND charindex(substring(type, 1, 1), @searchstr) != 0 67 AND s1.id = p1.id 68 AND p1.grantor = p1.uid 69 AND p1.action = 193 70 AND p1.protecttype = 2) 71 72 UNION 73 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(s.uid), 74 'TABLE_NAME' = name, 'GRANTOR' = user_name(s.uid), 75 'GRANTEE' = user_name(s.uid), 'PRIVILEGE' = 'INSERT', 'IS_GRANTABLE' = 76 substring('YESNO ', (select isnull((select p.protecttype 77 where p.id = s.id 78 AND p.protecttype != 2 79 AND p.grantor = p.uid 80 AND p.action = 195), 0)) * 3 + 1, 3) 81 FROM sysobjects as s, sysprotects as p 82 WHERE 83 name LIKE @tablename ESCAPE '\' 84 AND user_name(s.uid) LIKE @tableowner ESCAPE '\' 85 AND charindex(substring(type, 1, 1), @searchstr) != 0 86 AND not exists (select * from sysobjects s1, sysprotects p1 WHERE 87 name LIKE @tablename ESCAPE '\' 88 AND user_name(s.uid) LIKE @tableowner ESCAPE '\' 89 AND charindex(substring(type, 1, 1), @searchstr) != 0 90 AND s1.id = p1.id 91 AND p1.grantor = p1.uid 92 AND p1.action = 195 93 AND p1.protecttype = 2) 94 UNION 95 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(s.uid), 96 'TABLE_NAME' = name, 'GRANTOR' = user_name(s.uid), 97 'GRANTEE' = user_name(s.uid), 'PRIVILEGE' = 'DELETE', 'IS_GRANTABLE' = 98 substring('YESNO ', (select isnull((select p.protecttype 99 where p.id = s.id 100 AND p.protecttype != 2 101 AND p.grantor = p.uid 102 AND p.action = 196), 0)) * 3 + 1, 3) 103 FROM sysobjects as s, sysprotects as p 104 WHERE 105 name LIKE @tablename ESCAPE '\' 106 AND user_name(s.uid) LIKE @tableowner ESCAPE '\' 107 AND charindex(substring(type, 1, 1), @searchstr) != 0 108 AND not exists (select * from sysobjects s1, sysprotects p1 WHERE 109 name LIKE @tablename ESCAPE '\' 110 AND user_name(s.uid) LIKE @tableowner ESCAPE '\' 111 AND charindex(substring(type, 1, 1), @searchstr) != 0 112 AND s1.id = p1.id 113 AND p1.grantor = p1.uid 114 AND p1.action = 196 115 AND p1.protecttype = 2) 116 UNION 117 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(s.uid), 118 'TABLE_NAME' = name, 'GRANTOR' = user_name(s.uid), 119 'GRANTEE' = user_name(s.uid), 'PRIVILEGE' = 'UPDATE', 'IS_GRANTABLE' = 120 substring('YESNO ', (select isnull((select p.protecttype 121 where p.id = s.id 122 AND p.protecttype != 2 123 AND p.grantor = p.uid 124 AND p.action = 197), 0)) * 3 + 1, 3) 125 FROM sysobjects as s, sysprotects as p 126 WHERE 127 name LIKE @tablename ESCAPE '\' 128 AND user_name(s.uid) LIKE @tableowner ESCAPE '\' 129 AND charindex(substring(type, 1, 1), @searchstr) != 0 130 AND not exists (select * from sysobjects s1, sysprotects p1 WHERE 131 name LIKE @tablename ESCAPE '\' 132 AND user_name(s.uid) LIKE @tableowner ESCAPE '\' 133 AND charindex(substring(type, 1, 1), @searchstr) != 0 134 AND s1.id = p1.id 135 AND p1.grantor = p1.uid 136 AND p1.action = 197 137 AND p1.protecttype = 2) 138 UNION 139 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(s.uid), 140 'TABLE_NAME' = name, 'GRANTOR' = user_name(s.uid), 141 'GRANTEE' = user_name(s.uid), 'PRIVILEGE' = 'REFERENCE', 'IS_GRANTABLE' = 142 143 substring('YESNO ', (select isnull((select p.protecttype 144 where p.id = s.id 145 AND p.protecttype != 2 146 AND p.grantor = p.uid 147 AND p.action = 151), 0)) * 3 + 1, 3) 148 FROM sysobjects as s, sysprotects as p 149 WHERE 150 name LIKE @tablename ESCAPE '\' 151 AND user_name(s.uid) LIKE @tableowner ESCAPE '\' 152 AND charindex(substring(type, 1, 1), @searchstr) != 0 153 154 AND not exists (select * from sysobjects s1, sysprotects p1 WHERE 155 name LIKE @tablename ESCAPE '\' 156 AND user_name(s.uid) LIKE @tableowner ESCAPE '\' 157 AND charindex(substring(type, 1, 1), @searchstr) != 0 158 AND s1.id = p1.id 159 AND p1.grantor = p1.uid 160 AND p1.action = 151 161 AND p1.protecttype = 2) 162 UNION 163 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(o.uid), 164 'TABLE_NAME' = o.name, 'GRANTOR' = user_name(p.grantor), 165 'GRANTEE' = user_name(p.uid), 166 'PRIVILEGE' = 167 rtrim(substring(@privdef, 168 charindex(rtrim(convert(char, p.action)), @privlist), 9)), 169 substring('YESNO ', (p.protecttype * 3) + 1, 3) 170 FROM sysprotects p, sysobjects o 171 WHERE o.id = p.id and protecttype < 2 172 AND o.name LIKE @tablename ESCAPE '\' 173 AND user_name(o.uid) LIKE @tableowner ESCAPE '\' 174 AND p.action in (193, 151, 197, 196, 195) 175 AND charindex(substring(type, 1, 1), @searchstr) != 0 176 ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE 177 178 /* 179 ** End of sp_ijdbc_gettableprivileges 180 */ 181
exec sp_procxmode 'sp_ijdbc_gettableprivileges', 'AnyMode' go Grant Execute on sp_ijdbc_gettableprivileges to public go
DEFECTS | |
![]() | sybsystemprocs..sysprotects |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 55 |
![]() (name, uid) Intersection: {uid} | 65 |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 69 |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 78 |
![]() (name, uid) Intersection: {uid} | 88 |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 92 |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 100 |
![]() (name, uid) Intersection: {uid} | 110 |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 114 |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 122 |
![]() (name, uid) Intersection: {uid} | 132 |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 136 |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 145 |
![]() (name, uid) Intersection: {uid} | 156 |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 160 |
![]() (id, action, grantor, uid, protecttype) Intersection: {protecttype, action} | 171 |
![]() | 55 |
![]() | 57 |
![]() | 69 |
![]() | 70 |
![]() | 78 |
![]() | 80 |
![]() | 92 |
![]() | 93 |
![]() | 100 |
![]() | 102 |
![]() | 114 |
![]() | 115 |
![]() | 122 |
![]() | 124 |
![]() | 136 |
![]() | 137 |
![]() | 145 |
![]() | 147 |
![]() | 160 |
![]() | 161 |
![]() | 171 |
![]() | |
![]() | |
![]() | |
![]() | 46 |
![]() | 48 |
![]() | 6 |
![]() | 36 |
![]() | 41 |
![]() | 169 |
![]() | 58 |
![]() | 63 |
![]() | 81 |
![]() | 86 |
![]() | 103 |
![]() | 108 |
![]() | 125 |
![]() | 130 |
![]() | 148 |
![]() | 154 |
![]() | 170 |
![]() | 51 |
![]() | 60 |
![]() | 62 |
![]() | 64 |
![]() | 66 |
![]() | 74 |
![]() | 83 |
![]() | 85 |
![]() | 87 |
![]() | 89 |
![]() | 96 |
![]() | 105 |
![]() | 107 |
![]() | 109 |
![]() | 111 |
![]() | 118 |
![]() | 127 |
![]() | 129 |
![]() | 131 |
![]() | 133 |
![]() | 140 |
![]() | 150 |
![]() | 152 |
![]() | 155 |
![]() | 157 |
![]() | 171 |
![]() | 175 |
![]() (name, uid) Intersection: {name} | 64 |
![]() (name, uid) Intersection: {name} | 87 |
![]() (name, uid) Intersection: {name} | 109 |
![]() (name, uid) Intersection: {name} | 131 |
![]() (name, uid) Intersection: {name} | 155 |
![]() | 58 |
![]() | 81 |
![]() | 103 |
![]() | 125 |
![]() | 148 |
![]() | 50 |
![]() | 7 |
![]() | 53 |
![]() | 63 |
![]() | 76 |
![]() | 86 |
![]() | 98 |
![]() | 108 |
![]() | 120 |
![]() | 130 |
![]() | 143 |
![]() | 154 |
![]() | 6 |
![]() | 6 |
![]() | 6 |
![]() | 53 |
![]() | 63 |
![]() | 76 |
![]() | 86 |
![]() | 98 |
![]() | 108 |
![]() | 120 |
![]() | 130 |
![]() | 143 |
![]() | 154 |
![]() | 163 |
DEPENDENCIES |
PROCS AND TABLES USED writes table tempdb..#tmp_gettableprivileges (1) reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..sysprotects ![]() |