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