| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_oledb_getcolumnprivileges | | 31 Aug 14 | Defects Dependencies |
1 2 3 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 4 5 create procedure sp_oledb_getcolumnprivileges( 6 @table_name varchar(771) = null, 7 @table_owner varchar(32) = null, 8 @table_qualifier varchar(32) = null, 9 @column_name varchar(771) = null, 10 @grantor varchar(32) = null, 11 @grantee varchar(32) = null) 12 13 as 14 15 declare @owner_id int 16 declare @full_table_name varchar(1543) 17 declare @tab_id int /* object id of the table specified */ 18 declare @startedInTransaction bit 19 if (@@trancount > 0) 20 select @startedInTransaction = 1 21 else 22 select @startedInTransaction = 0 23 24 set nocount on 25 /* 26 ** set the transaction isolation level 27 */ 28 if @@trancount = 0 29 begin 30 set chained off 31 end 32 33 34 set transaction isolation level 1 35 if (@startedInTransaction = 1) 36 save transaction oledb_keep_temptable_tx 37 38 /* 39 ** Check to see that the table is qualified with database name 40 */ 41 if @table_name like "%.%.%" /*CT: oledb can be null */ 42 begin 43 /* 18021, "Object name can only be qualified with owner name" */ 44 raiserror 18021 45 return (1) 46 end 47 48 /* If this is a temporary table; object does not belong to 49 ** this database; (we should be in our temporary database) 50 */ 51 if (@table_name like "#%" and db_name() != 'tempdb') /*CT: tempdb*, use template*/ 52 begin 53 /* 54 ** 17676, "This may be a temporary object. Please execute 55 ** procedure from your temporary database." 56 */ 57 raiserror 17676 58 return (1) 59 end 60 61 /* 62 ** The table_qualifier should be same as the database name. Do the sanity check 63 ** if it is specified 64 */ 65 if (@table_qualifier is null) or (@table_qualifier = '') 66 /* set the table qualifier name */ 67 select @table_qualifier = db_name() 68 69 /* 70 ** if the table owner is not specified, it will be taken as the id of the 71 ** user executing this procedure. Otherwise find the explicit table name prefixed 72 ** by the owner id 73 */ 74 if (@table_owner is null) or (@table_owner = '') 75 select @full_table_name = @table_name 76 else 77 begin 78 if (@table_name like "%.%") and 79 substring(@table_name, 1, charindex(".", @table_name) - 1) != @table_owner 80 begin 81 /* 18011, Object name must be qualified with the owner name * */ 82 raiserror 18011 83 return (1) 84 end 85 86 if not (@table_name like "%.%") 87 select @full_table_name = @table_owner + '.' + @table_name 88 else 89 select @full_table_name = @table_name 90 91 end 92 93 /* Create temp table to store results from sp_aux_computeprivs */ 94 create table #results_table 95 (table_qualifier varchar(32), 96 table_owner varchar(32), 97 table_name varchar(255), 98 column_name varchar(255) NULL, 99 grantor varchar(32), 100 grantee varchar(32), 101 privilege varchar(32), 102 is_grantable varchar(3)) 103 104 /* 105 ** if the column name is not specified, set the column name to wild 106 ** character such it matches all the columns in the table 107 */ 108 if @column_name is null 109 select @column_name = '%' 110 /* 111 ** check to see if the specified table exists or not 112 */ 113 select @tab_id = object_id(@full_table_name) 114 115 if (@tab_id is not null) 116 begin 117 118 /* 119 ** check to see if the @tab_id indeeed represents a table or a view 120 */ 121 122 if exists (select * 123 from sysobjects 124 where (@tab_id = id) and 125 ((type = 'U') or 126 (type = 'S') or 127 (type = 'V'))) 128 begin 129 /* 130 ** check to see if the specified column is indeed a column belonging 131 ** to the table 132 */ 133 if exists (select * 134 from syscolumns 135 where (id = @tab_id) and 136 (name like @column_name)) 137 begin 138 139 /* 140 ** declare cursor to cycle through all possible columns 141 */ 142 declare cursor_columns cursor 143 for select name from syscolumns 144 where (id = @tab_id) 145 and (name like @column_name) 146 147 /* 148 ** For each column in the list, generate privileges 149 */ 150 open cursor_columns 151 fetch cursor_columns into @column_name 152 while (@@sqlstatus = 0) 153 begin 154 155 /* 156 ** compute the table owner id 157 */ 158 159 select @owner_id = uid 160 from sysobjects 161 where id = @tab_id 162 163 164 /* 165 ** get table owner name 166 */ 167 168 select @table_owner = name 169 from sysusers 170 where uid = @owner_id 171 172 /*exec sp_aux_computeprivs @table_name, @table_owner, @table_qualifier, 173 @column_name, 1, @tab_id*/ 174 175 exec sp_oledb_computeprivs @table_name, @table_owner, @table_qualifier, 176 @column_name, 1, @tab_id 177 178 set nocount off 179 180 fetch cursor_columns into @column_name 181 end 182 183 close cursor_columns 184 deallocate cursor cursor_columns 185 end 186 end 187 end 188 189 /* Print out results */ 190 191 if (@grantor is null) and (@grantee is null) 192 select distinct GRANTOR = r.grantor, 193 GRANTEE = r.grantee, 194 TABLE_CATALOG = r.table_qualifier, 195 TABLE_SCHEMA = r.table_owner, 196 TABLE_NAME = r.table_name, 197 COLUMN_NAME = r.column_name, 198 COLUMN_GUID = convert(varchar(36), null), 199 COLUMN_PROPID = convert(int, null), 200 PRIVILEGE_TYPE = case when r.privilege = 'REFERENCE' then "REFERENCES" 201 else r.privilege end, 202 IS_GRANTABLE = case when r.is_grantable = 'YES' then convert(bit, 1) 203 else convert(bit, 0) end 204 from #results_table r 205 order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE 206 else 207 if @grantee is null 208 select distinct GRANTOR = r.grantor, 209 GRANTEE = r.grantee, 210 TABLE_CATALOG = r.table_qualifier, 211 TABLE_SCHEMA = r.table_owner, 212 TABLE_NAME = r.table_name, 213 COLUMN_NAME = r.column_name, 214 COLUMN_GUID = convert(varchar(36), null), 215 COLUMN_PROPID = convert(int, null), 216 PRIVILEGE_TYPE = case when r.privilege = 'REFERENCE' then "REFERENCES" 217 else r.privilege end, 218 IS_GRANTABLE = case when r.is_grantable = 'YES' then convert(bit, 1) 219 else convert(bit, 0) end 220 from #results_table r where r.grantor = @grantor 221 order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE 222 else 223 if @grantor is null 224 select distinct GRANTOR = r.grantor, 225 GRANTEE = r.grantee, 226 TABLE_CATALOG = r.table_qualifier, 227 TABLE_SCHEMA = r.table_owner, 228 TABLE_NAME = r.table_name, 229 COLUMN_NAME = r.column_name, 230 COLUMN_GUID = convert(varchar(36), null), 231 COLUMN_PROPID = convert(int, null), 232 PRIVILEGE_TYPE = case when r.privilege = 'REFERENCE' then "REFERENCES" 233 else r.privilege end, 234 IS_GRANTABLE = case when r.is_grantable = 'YES' then convert(bit, 1) 235 else convert(bit, 0) end 236 from #results_table r where r.grantee = @grantee 237 order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE 238 else 239 select distinct GRANTOR = r.grantor, 240 GRANTEE = r.grantee, 241 TABLE_CATALOG = r.table_qualifier, 242 TABLE_SCHEMA = r.table_owner, 243 TABLE_NAME = r.table_name, 244 COLUMN_NAME = r.column_name, 245 COLUMN_GUID = convert(varchar(36), null), 246 COLUMN_PROPID = convert(int, null), 247 PRIVILEGE_TYPE = case when r.privilege = 'REFERENCE' then "REFERENCES" 248 else r.privilege end, 249 IS_GRANTABLE = case when r.is_grantable = 'YES' then convert(bit, 1) 250 else convert(bit, 0) end 251 from #results_table r where r.grantor = @grantor and r.grantee = @grantee 252 order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE 253 254 255 if (@startedInTransaction = 1) 256 save transaction oledb_keep_temptable_tx 257 258 return (0) 259
exec sp_procxmode 'sp_oledb_getcolumnprivileges', 'AnyMode' go Grant Execute on sp_oledb_getcolumnprivileges to public go
| RESULT SETS | |
| sp_oledb_getcolumnprivileges_rset_004 | |
| sp_oledb_getcolumnprivileges_rset_003 | |
| sp_oledb_getcolumnprivileges_rset_002 | |
| sp_oledb_getcolumnprivileges_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_oledb_computeprivs read_writes table tempdb..#distinct_grantors (1) reads table sybsystemprocs..syscolumns calls proc sybsystemprocs..syb_aux_privexor calls proc sybsystemprocs..syb_aux_expandbitmap calls proc sybsystemprocs..syb_aux_printprivs calls proc sybsystemprocs..syb_aux_colbit reads table sybsystemprocs..sysobjects read_writes table tempdb..#sysprotects (1) calls proc sybsystemprocs..syb_aux_privunion read_writes table tempdb..#column_privileges (1) reads table sybsystemprocs..sysprotects reads table sybsystemprocs..sysusers read_writes table tempdb..#useful_groups (1) writes table tempdb..#results_table (1) read_writes table tempdb..#results_table (1) reads table sybsystemprocs..syscolumns reads table sybsystemprocs..sysusers reads table sybsystemprocs..sysobjects |
