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