| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_ijdbc_getcolumnprivileges | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_ijdbc_getcolumnprivilege 4 */ 5 6 create procedure sp_ijdbc_getcolumnprivileges( 7 @table_qualifier varchar(32) = null, 8 @table_owner varchar(32) = null, 9 @table_name varchar(257) = null, 10 @column_name varchar(257) = null) 11 as 12 13 declare @tablename varchar(257) 14 declare @tableowner varchar(128) 15 declare @privlist varchar(128) 16 declare @privdef varchar(128) 17 declare @searchstr char(3) 18 declare @msg varchar(255) 19 20 select @searchstr = 'SUV' /* SYSTEM USER VIEW types only */ 21 22 select @privlist = '193 ' + /* SELECT */ 23 '151 ' + /* REFERENCE */ 24 '197 ' + /* UPDATE */ 25 '196 ' + /* DELETE */ 26 '195 ' /* INSERT */ 27 28 select @privdef = 'SELECT ' + 29 'REFERENCE' + 30 'UPDATE ' + 31 'DELETE ' + 32 'INSERT ' 33 34 35 select @tablename = @table_name 36 select @tableowner = @table_owner 37 38 if (@tableowner is null) 39 begin 40 select @tableowner = '%' 41 end 42 43 if (@tablename is null) 44 begin 45 select @tablename = '%' 46 end 47 48 set nocount on 49 if @@trancount = 0 50 begin 51 set chained off 52 end 53 else 54 begin 55 /* we are inside a transaction. catalog procedure sp_column privileges 56 ** can't be run inside a transaction 57 */ 58 exec sp_getmessage 18040, @msg output 59 raiserror 18040, @msg, 'sp_column_privileges' 60 return (1) 61 end 62 63 set transaction isolation level 1 64 65 /* If this is a temporary table; object does not belong to 66 ** this database; (we should be in our temporary database) 67 */ 68 if (@table_name like '#%' and db_name() != db_name(tempdb_id())) 69 begin 70 /* 71 ** 17676, 'This may be a temporary object. Please execute 72 ** procedure from your temporary database.' 73 */ 74 exec sp_getmessage 17676, @msg out 75 raiserror 17676 @msg 76 return (1) 77 end 78 79 /* 80 ** The table_qualifier should be same as the database name. Do the sanity 81 ** check if it is specified 82 */ 83 if (@table_qualifier is null) or (@table_qualifier = '') 84 /* set the table qualifier name */ 85 select @table_qualifier = db_name() 86 else 87 begin 88 if db_name() != @table_qualifier 89 begin 90 exec sp_getmessage 18039, @msg out 91 raiserror 18039 @msg 92 return (1) 93 end 94 end 95 96 /* 97 ** if the table owner is not specified, it will be taken as the id of the 98 ** user executing this procedure. Otherwise find the explicit table name 99 ** prefixed by the owner id 100 */ 101 102 /* 103 ** NOTE: SQL Server allows an underscore '_' in the table owner, even 104 ** though '_' is a single character wildcard. 105 */ 106 if (charindex('%', @table_owner) > 0) 107 begin 108 exec sp_getmessage 17993, @msg output 109 raiserror 17993 @msg, @table_owner 110 return (1) 111 end 112 113 if (@table_owner is null) 114 begin 115 exec sp_getmessage 17993, @msg output 116 raiserror 17993 @msg, 'NULL' 117 return (1) 118 end 119 else 120 begin 121 exec sp_ijdbc_escapeliteralforlike @table_owner output 122 end 123 124 if (@table_name is null) 125 begin 126 exec sp_getmessage 17993, @msg output 127 raiserror 17993 @msg, 'NULL' 128 return (1) 129 end 130 131 if (select count(*) from sysobjects 132 where user_name(uid) like @table_owner ESCAPE '\' 133 and name like @table_name ESCAPE '\' 134 AND charindex(substring(type, 1, 1), @searchstr) != 0 135 ) = 0 136 begin 137 exec sp_getmessage 17674, @msg output 138 raiserror 17674 @msg 139 return 1 140 end 141 142 if @column_name is null 143 select @column_name = '%' 144 else 145 begin 146 if not exists (select * from syscolumns c, sysobjects o 147 where user_name(o.uid) like @table_owner ESCAPE '\' 148 and o.name like @table_name ESCAPE '\' 149 and (charindex(substring(o.type, 1, 1), @searchstr) != 0) 150 and c.id = o.id and c.name like @column_name ESCAPE '\') 151 begin 152 exec sp_getmessage 17563, @msg output 153 raiserror 17563 @msg, @column_name 154 return (1) 155 end 156 end 157 158 159 select uid = o.uid, user_name = user_name(o.uid), 160 oid = o.id, table_name = o.name, colid = c.colid, 161 col_name = c.name, type = o.type 162 into #objects_id 163 from syscolumns c, sysobjects o 164 where user_name(o.uid) like @table_owner ESCAPE '\' 165 and o.name like @table_name ESCAPE '\' 166 and (charindex(substring(o.type, 1, 1), @searchstr) != 0) 167 and c.id = o.id and c.name like @column_name ESCAPE '\' 168 169 170 create table #tmp_grp_getcolumnprivileges(TABLE_CAT varchar(32) null, TABLE_SCHEM varchar(32) null, TABLE_NAME varchar(257) null, COLUMN_NAME varchar(257) null, GRANTOR varchar(32) null, UID int null, GRANTEE varchar(32) null, PRIVILEGE varchar(128) null, IS_GRANTABLE char(3) null) 171 172 delete #tmp_getcolumnprivileges 173 174 insert #tmp_grp_getcolumnprivileges 175 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = t.user_name, 176 'TABLE_NAME' = t.table_name, 'COLUMN_NAME' = t.col_name, 177 'GRANTOR' = t.user_name, 178 'UID' = t.uid, 179 'GRANTEE' = t.user_name, 'PRIVILEGE' = 'SELECT', 'IS_GRANTABLE' = 180 substring('YESNO ', (select isnull((select p.protecttype 181 where p.id = t.oid 182 AND p.protecttype != 2 183 AND p.grantor = p.uid 184 AND p.action = 193), 0)) * 3 + 1, 3) 185 186 FROM #objects_id as t, sysprotects as p 187 WHERE 188 not exists (select * from sysobjects s1, sysprotects p1 WHERE 189 name LIKE @tablename ESCAPE '\' 190 AND user_name(t.uid) LIKE @tableowner ESCAPE '\' 191 AND charindex(substring(type, 1, 1), @searchstr) != 0 192 AND s1.id = p1.id 193 AND p1.grantor = p1.uid 194 AND p1.action = 193 195 AND p1.protecttype = 2) 196 AND (t.colid in (select v.number 197 from master.dbo.spt_values v 198 where convert(tinyint, substring(isnull(p.columns, 0x1), 199 v.low, 1)) & v.high != 0 200 and v.type = "P" and v.number <= 1024) 201 or 0 in (select v.number 202 from master.dbo.spt_values v 203 where convert(tinyint, substring(isnull(p.columns, 0x1), 204 v.low, 1)) & v.high != 0 205 and v.type = "P" and v.number <= 1024) 206 ) 207 208 209 UNION 210 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = t.user_name, 211 'TABLE_NAME' = t.table_name, 'COLUMN_NAME' = t.col_name, 212 'GRANTOR' = t.user_name, 213 'UID' = t.uid, 214 'GRANTEE' = t.user_name, 'PRIVILEGE' = 'INSERT', 'IS_GRANTABLE' = 215 substring('YESNO ', (select isnull((select p.protecttype 216 where p.id = t.oid 217 AND p.protecttype != 2 218 AND p.grantor = p.uid 219 AND p.action = 195), 0)) * 3 + 1, 3) 220 FROM #objects_id as t, sysprotects as p 221 WHERE 222 not exists (select * from sysobjects s1, sysprotects p1 WHERE 223 name LIKE @tablename ESCAPE '\' 224 AND user_name(t.uid) LIKE @tableowner ESCAPE '\' 225 AND charindex(substring(type, 1, 1), @searchstr) != 0 226 AND s1.id = p1.id 227 AND p1.grantor = p1.uid 228 AND p1.action = 195 229 AND p1.protecttype = 2) 230 AND (t.colid in (select v.number 231 from master.dbo.spt_values v 232 where convert(tinyint, substring(isnull(p.columns, 0x1), 233 v.low, 1)) & v.high != 0 234 and v.type = "P" and v.number <= 1024) 235 or 0 in (select v.number 236 from master.dbo.spt_values v 237 where convert(tinyint, substring(isnull(p.columns, 0x1), 238 v.low, 1)) & v.high != 0 239 and v.type = "P" and v.number <= 1024) 240 ) 241 242 UNION 243 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = t.user_name, 244 'TABLE_NAME' = t.table_name, 'COLUMN_NAME' = t.col_name, 245 'GRANTOR' = t.user_name, 246 'UID' = t.uid, 247 'GRANTEE' = t.user_name, 'PRIVILEGE' = 'DELETE', 'IS_GRANTABLE' = 248 substring('YESNO ', (select isnull((select p.protecttype 249 where p.id = t.oid 250 AND p.protecttype != 2 251 AND p.grantor = p.uid 252 AND p.action = 196), 0)) * 3 + 1, 3) 253 FROM #objects_id as t, sysprotects as p 254 WHERE 255 not exists (select * from sysobjects s1, sysprotects p1 WHERE 256 name LIKE @tablename ESCAPE '\' 257 AND user_name(t.uid) LIKE @tableowner ESCAPE '\' 258 AND charindex(substring(type, 1, 1), @searchstr) != 0 259 AND s1.id = p1.id 260 AND p1.grantor = p1.uid 261 AND p1.action = 196 262 AND p1.protecttype = 2) 263 AND (t.colid in (select v.number 264 from master.dbo.spt_values v 265 where convert(tinyint, substring(isnull(p.columns, 0x1), 266 v.low, 1)) & v.high != 0 267 and v.type = "P" and v.number <= 1024) 268 or 0 in (select v.number 269 from master.dbo.spt_values v 270 where convert(tinyint, substring(isnull(p.columns, 0x1), 271 v.low, 1)) & v.high != 0 272 and v.type = "P" and v.number <= 1024) 273 ) 274 275 UNION 276 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = t.user_name, 277 'TABLE_NAME' = t.table_name, 'COLUMN_NAME' = t.col_name, 278 'GRANTOR' = t.user_name, 279 'UID' = t.uid, 280 'GRANTEE' = t.user_name, 'PRIVILEGE' = 'UPDATE', 'IS_GRANTABLE' = 281 substring('YESNO ', (select isnull((select p.protecttype 282 where p.id = t.oid 283 AND p.protecttype != 2 284 AND p.grantor = p.uid 285 AND p.action = 197), 0)) * 3 + 1, 3) 286 FROM #objects_id as t, sysprotects as p 287 WHERE 288 not exists (select * from sysobjects s1, sysprotects p1 WHERE 289 name LIKE @tablename ESCAPE '\' 290 AND user_name(t.uid) LIKE @tableowner ESCAPE '\' 291 AND charindex(substring(type, 1, 1), @searchstr) != 0 292 AND s1.id = p1.id 293 AND p1.grantor = p1.uid 294 AND p1.action = 197 295 AND p1.protecttype = 2) 296 AND (t.colid in (select v.number 297 from master.dbo.spt_values v 298 where convert(tinyint, substring(isnull(p.columns, 0x1), 299 v.low, 1)) & v.high != 0 300 and v.type = "P" and v.number <= 1024) 301 or 0 in (select v.number 302 from master.dbo.spt_values v 303 where convert(tinyint, substring(isnull(p.columns, 0x1), 304 v.low, 1)) & v.high != 0 305 and v.type = "P" and v.number <= 1024) 306 ) 307 308 UNION 309 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = t.user_name, 310 'TABLE_NAME' = t.table_name, 'COLUMN_NAME' = t.col_name, 311 'GRANTOR' = t.user_name, 312 'UID' = t.uid, 313 'GRANTEE' = t.user_name, 'PRIVILEGE' = 'REFERENCE', 'IS_GRANTABLE' = 314 315 substring('YESNO ', (select isnull((select p.protecttype 316 where p.id = t.oid 317 AND p.protecttype != 2 318 AND p.grantor = p.uid 319 AND p.action = 151), 0)) * 3 + 1, 3) 320 FROM #objects_id as t, sysprotects as p 321 WHERE 322 323 not exists (select * from sysobjects s1, sysprotects p1 WHERE 324 name LIKE @tablename ESCAPE '\' 325 AND user_name(t.uid) LIKE @tableowner ESCAPE '\' 326 AND charindex(substring(type, 1, 1), @searchstr) != 0 327 AND s1.id = p1.id 328 AND p1.grantor = p1.uid 329 AND p1.action = 151 330 AND p1.protecttype = 2) 331 AND (t.colid in (select v.number 332 from master.dbo.spt_values v 333 where convert(tinyint, substring(isnull(p.columns, 0x1), 334 v.low, 1)) & v.high != 0 335 and v.type = "P" and v.number <= 1024) 336 or 0 in (select v.number 337 from master.dbo.spt_values v 338 where convert(tinyint, substring(isnull(p.columns, 0x1), 339 v.low, 1)) & v.high != 0 340 and v.type = "P" and v.number <= 1024) 341 ) 342 343 344 UNION 345 SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = t.user_name, 346 'TABLE_NAME' = t.table_name, 'COLUMN_NAME' = t.col_name, 347 'GRANTOR' = user_name(p.grantor), 348 'UID' = p.uid, 349 'GRANTEE' = user_name(p.uid), 350 'PRIVILEGE' = 351 rtrim(substring(@privdef, 352 charindex(rtrim(convert(char, p.action)), @privlist), 9)), 353 substring('YESNO ', (p.protecttype * 3) + 1, 3) 354 FROM sysprotects p, #objects_id as t 355 WHERE t.oid = p.id and protecttype < 2 356 AND p.action in (193, 151, 197, 196, 195) 357 AND charindex(substring(t.type, 1, 1), @searchstr) != 0 358 AND (t.colid in (select v.number 359 from master.dbo.spt_values v 360 where convert(tinyint, substring(isnull(p.columns, 0x1), 361 v.low, 1)) & v.high != 0 362 and v.type = "P" and v.number <= 1024) 363 or 0 in (select v.number 364 from master.dbo.spt_values v 365 where convert(tinyint, substring(isnull(p.columns, 0x1), 366 v.low, 1)) & v.high != 0 367 and v.type = "P" and v.number <= 1024) 368 ) 369 370 371 ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE 372 373 374 375 376 /** Propagate the privileges from the groups 377 ** to the groups members. 378 **/ 379 380 381 insert #tmp_getcolumnprivileges 382 select t.TABLE_CAT, t.TABLE_SCHEM, t.TABLE_NAME, 383 t.COLUMN_NAME, t.GRANTOR, 'GRANTEE' = su.name, t.PRIVILEGE, 384 t.IS_GRANTABLE 385 386 from #tmp_grp_getcolumnprivileges t, sysusers su 387 where 388 t.UID = su.uid 389 and su.gid != su.uid 390 391 union 392 393 select t.TABLE_CAT, t.TABLE_SCHEM, t.TABLE_NAME, 394 t.COLUMN_NAME, t.GRANTOR, 'GRANTEE' = su.name, t.PRIVILEGE, 395 t.IS_GRANTABLE 396 from #tmp_grp_getcolumnprivileges t, sysusers su 397 where 398 t.UID = su.gid 399 and su.gid != su.uid 400 401 402 403 404 /* 405 ** End of sp_ijdbc_getcolumnprivileges 406 */ 407 408
exec sp_procxmode 'sp_ijdbc_getcolumnprivileges', 'AnyMode' go Grant Execute on sp_ijdbc_getcolumnprivileges to public go
| DEPENDENCIES |
| PROCS AND TABLES USED writes table tempdb..#tmp_getcolumnprivileges (1) reads table sybsystemprocs..sysobjects read_writes table tempdb..#objects_id (1) reads table sybsystemprocs..sysprotects reads table sybsystemprocs..syscolumns calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1)  reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusers reads table master..spt_values (1) read_writes table tempdb..#tmp_grp_getcolumnprivileges (1) calls proc sybsystemprocs..sp_ijdbc_escapeliteralforlike |
