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