| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_oledb_views | | 31 Aug 14 | Defects Dependencies |
1 2 create procedure sp_oledb_views 3 @table_catalog varchar(32) = null, 4 @table_schema varchar(32) = null, 5 @table_name varchar(771) = null 6 as 7 8 declare @type1 varchar(3) 9 declare @tableindex int 10 declare @startedInTransaction bit 11 if (@@trancount > 0) 12 select @startedInTransaction = 1 13 else 14 select @startedInTransaction = 0 15 16 17 if @@trancount = 0 18 begin 19 set chained off 20 end 21 22 set transaction isolation level 1 23 if (@startedInTransaction = 1) 24 save transaction oledb_keep_temptable_tx 25 26 /* temp table */ 27 if (@table_name like "#%" and 28 db_name() != db_name(tempdb_id())) 29 begin 30 /* 31 ** Can return data about temp. tables only in tempdb 32 */ 33 raiserror 17676 34 return (1) 35 end 36 37 create table #oledb_results_table 38 ( 39 TABLE_CATALOG varchar(32) null, 40 TABLE_SCHEMA varchar(32) null, 41 TABLE_NAME varchar(255) null, 42 VIEW_DEFINITION varchar(255) null, 43 CHECK_OPTION bit default 1 not null, 44 IS_UPDATABLE bit default 0 not null, 45 DESCRIPTION varchar(255) null, 46 DATE_CREATED datetime null, 47 DATE_MODIFIED datetime null 48 ) 49 50 /* 51 ** Special feature #1: enumerate databases when owner and name 52 ** are blank but qualifier is explicitly '%'. 53 */ 54 if @table_catalog = '%' and 55 @table_schema = '' and 56 @table_name = '' 57 begin 58 59 /* 60 ** If enumerating databases 61 */ 62 insert #oledb_results_table 63 select 64 TABLE_CATALOG = name, 65 TABLE_SCHEMA = null, 66 TABLE_NAME = null, 67 VIEW_DEFINITION = convert(varchar(255), null), 68 CHECK_OPTION = 1, 69 IS_UPDATABLE = 0, 70 DESCRIPTION = convert(varchar(255), null), 71 DATE_CREATED = convert(datetime, null), 72 DATE_MODIFIED = convert(datetime, null) 73 74 from master..sysdatabases 75 76 /* 77 ** eliminate MODEL database 78 */ 79 where name != 'model' 80 order by TABLE_CATALOG 81 end 82 83 /* 84 ** Special feature #2: enumerate owners when qualifier and name 85 ** are blank but owner is explicitly '%'. 86 */ 87 else if @table_catalog = '' and 88 @table_schema = '%' and 89 @table_name = '' 90 begin 91 92 /* 93 ** If enumerating owners 94 */ 95 insert #oledb_results_table 96 select distinct 97 TABLE_CATALOG = null, 98 TABLE_SCHEMA = user_name(uid), 99 TABLE_NAME = null, 100 VIEW_DEFINITION = convert(varchar(255), null), 101 CHECK_OPTION = 1, 102 IS_UPDATABLE = 0, 103 DESCRIPTION = convert(varchar(255), null), 104 DATE_CREATED = convert(datetime, null), 105 DATE_MODIFIED = convert(datetime, null) 106 107 from sysobjects 108 order by TABLE_SCHEMA 109 end 110 else 111 begin 112 113 /* 114 ** end of special features -- do normal processing 115 */ 116 if @table_catalog is not null 117 118 begin 119 if db_name() != @table_catalog 120 begin 121 if @table_catalog = '' 122 begin 123 124 /* 125 ** If empty qualifier supplied 126 ** Force an empty result set 127 */ 128 select @table_name = '' 129 select @table_schema = '' 130 end 131 else 132 begin 133 134 /* 135 ** If qualifier doesn't match current 136 ** database. 137 */ 138 raiserror 18039 139 return 1 140 end 141 end 142 end 143 144 select @type1 = 'V' 145 146 if @table_name is null 147 148 begin 149 150 /* 151 ** If table name not supplied, match all 152 */ 153 select @table_name = '%' 154 end 155 else 156 begin 157 if (@table_schema is null) and 158 (charindex('%', @table_name) = 0) 159 begin 160 161 /* 162 ** If owner not specified and table is specified 163 */ 164 if exists (select * from sysobjects 165 where uid = user_id() 166 and id = object_id(@table_name) 167 and type = 'V') 168 begin 169 170 /* 171 ** Override supplied owner w/owner of table 172 */ 173 select @table_schema = user_name() 174 end 175 end 176 end 177 178 /* 179 ** If no owner supplied, force wildcard 180 */ 181 if @table_schema is null 182 select @table_schema = '%' 183 insert #oledb_results_table 184 select 185 TABLE_CATALOG = db_name(), 186 TABLE_SCHEMA = user_name(o.uid), 187 TABLE_NAME = o.name, 188 VIEW_DEFINITION = convert(varchar(255), null), 189 CHECK_OPTION = 1, 190 IS_UPDATABLE = 0, 191 DESCRIPTION = convert(varchar(255), null), 192 DATE_CREATED = convert(datetime, null), 193 DATE_MODIFIED = convert(datetime, null) 194 from sysusers u, sysobjects o 195 where 196 /* Special case for temp. tables. Match ids */ 197 (o.name like @table_name or o.id = object_id(@table_name)) 198 and user_name(o.uid) like @table_schema 199 200 /* 201 ** Only desired types 202 */ 203 and charindex(substring(o.type, 1, 1), @type1) != 0 204 205 /* 206 ** constrain sysusers uid for use in subquery 207 */ 208 and u.uid = user_id() 209 and ( 210 suser_id() = 1 /* User is the System Administrator */ 211 or o.uid = user_id() /* User created the object */ 212 /* here's the magic..select the highest 213 ** precedence of permissions in the 214 ** order (user,group,public) 215 */ 216 217 /* 218 ** The value of protecttype is 219 ** 220 ** 0 for grant with grant 221 ** 1 for grant and, 222 ** 2 for revoke 223 ** 224 ** As protecttype is of type tinyint, protecttype/2 is 225 ** integer division and will yield 0 for both types of 226 ** grants and will yield 1 for revoke, i.e., when 227 ** the value of protecttype is 2. The XOR (^) operation 228 ** will reverse the bits and thus (protecttype/2)^1 will 229 ** yield a value of 1 for grants and will yield a 230 ** value of zero for revoke. 231 ** 232 ** For groups, uid = gid. We shall use this to our advantage. 233 ** 234 ** If there are several entries in the sysprotects table 235 ** with the same Object ID, then the following expression 236 ** will prefer an individual uid entry over a group entry 237 ** 238 ** For example, let us say there are two users u1 and u2 239 ** with uids 4 and 5 respectiveley and both u1 and u2 240 ** belong to a group g12 whose uid is 16390. table t1 241 ** is owned by user u0 and user u0 performs the following 242 ** actions: 243 ** 244 ** grant select on t1 to g12 245 ** revoke select on t1 from u1 246 ** 247 ** There will be two entries in sysprotects for the object t1, 248 ** one for the group g12 where protecttype = grant (1) and 249 ** one for u1 where protecttype = revoke (2). 250 ** 251 ** For the group g12, the following expression will 252 ** evaluate to: 253 ** 254 ** ((abs(16390-16390)*2) + ((1/2)^1) 255 ** = ((0) + (0)^1) = 0 + 1 = 1 256 ** 257 ** For the user entry u1, it will evaluate to: 258 ** 259 ** (((+)*abs(4-16390)*2) + ((2/2)^1)) 260 ** = (abs(-16386)*2 + (1)^1) 261 ** = 16386*2 + 0 = 32772 262 ** 263 ** As the expression evaluates to a bigger number for the 264 ** user entry u1, select max() will chose 32772 which, 265 ** ANDed with 1 gives 0, i.e., sp_oledb_tables will not display 266 ** this particular table to the user. 267 ** 268 ** When the user u2 invokes sp_oledb_tables, there is only one 269 ** entry for u2, which is the entry for the group g12, and 270 ** so the group entry will be selected thus allowing the 271 ** table t1 to be displayed. 272 ** 273 ** ((select max((abs(uid-u.gid)*2) 274 ** + ((protecttype/2)^1)) 275 ** 276 ** Notice that multiplying by 2 makes the number an 277 ** even number (meaning the last digit is 0) so what 278 ** matters at the end is (protecttype/2)^1. 279 ** 280 **/ 281 282 or ((select max((abs(p.uid - u2.gid) * 2) + ((p.protecttype / 2) ^ 1)) 283 from sysprotects p, sysusers u2 284 where p.id = o.id /* outer join to correlate 285 ** with all rows in sysobjects 286 */ 287 and u2.uid = user_id() 288 /* 289 ** get rows for public, current users, user's groups 290 */ 291 and (p.uid = 0 or /* public */ 292 p.uid = user_id() or /* current user */ 293 p.uid = u2.gid) /* users group */ 294 295 /* 296 ** check for SELECT, EXECUTE privilege. 297 */ 298 and (p.action in (193, 224))) & 1 299 300 /* 301 ** more magic...normalise GRANT 302 ** and final magic...compare 303 ** Grants. 304 */ 305 ) = 1 306 /* 307 ** If one of any user defined roles or contained roles for the 308 ** user has permission, the user has the permission 309 */ 310 or exists (select 1 311 from sysprotects p1, 312 master.dbo.syssrvroles srvro, 313 sysroles ro 314 where p1.id = o.id 315 and p1.uid = ro.lrid 316 and ro.id = srvro.srid 317 and has_role(srvro.name, 1) > 0 318 and p1.action = 193)) 319 320 order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME 321 end /* Adaptive Server has expanded all '*' elements in the following statement */ 322 select #oledb_results_table.TABLE_CATALOG, #oledb_results_table.TABLE_SCHEMA, #oledb_results_table.TABLE_NAME, #oledb_results_table.VIEW_DEFINITION, #oledb_results_table.CHECK_OPTION, #oledb_results_table.IS_UPDATABLE, #oledb_results_table.DESCRIPTION, #oledb_results_table.DATE_CREATED, #oledb_results_table.DATE_MODIFIED from #oledb_results_table 323 324 325 if (@startedInTransaction = 1) 326 rollback transaction oledb_keep_temptable_tx 327 328 return (0) 329
exec sp_procxmode 'sp_oledb_views', 'AnyMode' go Grant Execute on sp_oledb_views to public go
| RESULT SETS | |
| sp_oledb_views_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table tempdb..#oledb_results_table (1) reads table master..sysdatabases (1)  reads table sybsystemprocs..sysobjects reads table sybsystemprocs..sysprotects reads table master..syssrvroles (1) reads table sybsystemprocs..sysroles reads table sybsystemprocs..sysusers |
