| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_jdbc_gettableprivileges | | 31 Aug 14 | Defects Dependencies |
1 2 create procedure sp_jdbc_gettableprivileges( 3 @table_qualifier varchar(32), 4 @table_owner varchar(32) = null, 5 @table_name varchar(771) = null) 6 AS 7 8 declare @tab_name varchar(771) 9 declare @tab_owner varchar(32) 10 declare @table_id int /* object id of the 11 table specified*/ 12 declare @startedInTransaction bit 13 14 15 if (@@trancount = 0) 16 begin 17 set chained off 18 end 19 20 /* check if we're in a transaction, before we try any select statements */ 21 if (@@trancount > 0) 22 select @startedInTransaction = 1 23 else 24 select @startedInTransaction = 0 25 26 set nocount on 27 28 set transaction isolation level 1 29 30 if (@startedInTransaction = 1) 31 save transaction jdbc_keep_temptables_from_tx 32 33 /* this will make sure that all rows are sent even if 34 ** the client "set rowcount" is differect 35 */ 36 37 set rowcount 0 38 39 if (@table_qualifier is null) or (@table_qualifier = '') 40 /* set the table qualifier name */ 41 select @table_qualifier = db_name() 42 43 if (@table_owner is null) or (@table_owner = '') 44 select @table_owner = '%' 45 46 if (@table_name is null) 47 begin 48 select @table_name = '%' 49 end 50 51 /* Now, create a temporary table to hold a list of all the possible 52 tables that we could get with the trio of table name, table owner and 53 table catalog. Then, populate that table.*/ 54 55 create table #jdbc_tprivs 56 (tab_id int primary key, 57 tab_name varchar(255), 58 tab_owner varchar(32) null, 59 uid int, 60 type varchar(10)) 61 62 insert #jdbc_tprivs 63 SELECT id, name, user_name(uid), uid, type 64 FROM sysobjects s 65 WHERE name LIKE @table_name ESCAPE '\' 66 AND user_name(uid) LIKE @table_owner ESCAPE '\' 67 AND charindex(substring(type, 1, 1), 'SUV') != 0 68 69 /* Create temp table to store results from sp_jdbc_computeprivs */ 70 create table #results_table 71 (TABLE_CAT varchar(32), 72 TABLE_SCHEM varchar(32), 73 TABLE_NAME varchar(255), 74 column_name varchar(255) NULL, 75 GRANTOR varchar(32), 76 GRANTEE varchar(32), 77 PRIVILEGE varchar(32), 78 IS_GRANTABLE varchar(3)) 79 80 81 declare tpriv_cursor cursor for 82 select tab_name, tab_owner, tab_id from #jdbc_tprivs 83 84 open tpriv_cursor 85 86 fetch tpriv_cursor into @tab_name, @tab_owner, @table_id 87 88 while (@@sqlstatus != 2) 89 begin 90 91 /* 92 ** The following four temp tables used to be created in 93 ** sp_jdbc_computeprivs, but to avoid errors when this proc was 94 ** run in a transaction, we had to move the creation of the 95 ** temp tables here. Note that this repeated creation and 96 ** destruction of the temp tables is no less efficient than 97 ** what was being done previously, since these tables were being 98 ** created and destroyed with each call to computeprivs in the while 99 ** loop 100 */ 101 102 /* 103 ** create a temporary sysprotects table that only has grant/revoke 104 ** tuples for the requested table. This is done as an optimization 105 ** as the sysprotects table may need to be traversed several times 106 */ 107 108 create table #sysprotects 109 (uid int, 110 action smallint, 111 protecttype tinyint, 112 columns varbinary(133) NULL, 113 grantor int) 114 115 /* 116 ** This table contains all the groups including PUBLIC that users, who 117 ** have been granted privilege on this table, belong to. Also it 118 ** includes groups that have been explicitly granted privileges on the 119 ** table object 120 */ 121 create table #useful_groups 122 (grp_id int) 123 124 /* 125 ** create a table that contains the list of grantors for the object 126 ** requested. We will do a cartesian product of this table with 127 ** sysusers in the current database to capture all grantor/grantee 128 ** tuples 129 */ 130 131 create table #distinct_grantors 132 (grantor int) 133 134 /* 135 ** We need to create a table which will contain a row for every object 136 ** privilege to be returned to the client. 137 */ 138 139 create table #column_privileges 140 (grantee_gid int, 141 grantor int, 142 grantee int, 143 insertpriv tinyint, 144 insert_go tinyint NULL, 145 deletepriv tinyint, 146 delete_go tinyint NULL, 147 selectpriv varbinary(133) NULL, 148 select_go varbinary(133) NULL, 149 updatepriv varbinary(133) NULL, 150 update_go varbinary(133) NULL, 151 referencepriv varbinary(133) NULL, 152 reference_go varbinary(133) NULL) 153 154 exec sp_jdbc_computeprivs @tab_name, @tab_owner, @table_qualifier, 155 NULL, 0, @table_id 156 fetch tpriv_cursor into @tab_name, @tab_owner, @table_id 157 158 drop table #column_privileges 159 drop table #distinct_grantors 160 drop table #sysprotects 161 drop table #useful_groups 162 163 end 164 165 close tpriv_cursor 166 167 168 /* Output the results table */ 169 170 select TABLE_CAT, TABLE_SCHEM, TABLE_NAME, GRANTOR, GRANTEE, 171 PRIVILEGE, IS_GRANTABLE 172 from #results_table 173 order by TABLE_SCHEM, TABLE_NAME, PRIVILEGE 174 175 drop table #jdbc_tprivs 176 drop table #results_table 177 set nocount off 178 179 if (@startedInTransaction = 1) 180 rollback transaction jdbc_keep_temptables_from_tx 181 182
exec sp_procxmode 'sp_jdbc_gettableprivileges', 'AnyMode' go Grant Execute on sp_jdbc_gettableprivileges to public go
| RESULT SETS | |
| sp_jdbc_gettableprivileges_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED writes table tempdb..#sysprotects (1) read_writes table tempdb..#jdbc_tprivs (1) writes table tempdb..#column_privileges (1) calls proc sybsystemprocs..sp_jdbc_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 read_writes table tempdb..#sysprotects (1) reads table sybsystemprocs..sysprotects reads table sybsystemprocs..sysobjects reads table sybsystemprocs..syscolumns read_writes table tempdb..#distinct_grantors (1) read_writes table tempdb..#useful_groups (1) reads table sybsystemprocs..sysusers read_writes table tempdb..#column_privileges (1) calls proc sybsystemprocs..syb_aux_printprivs calls proc sybsystemprocs..syb_aux_colbit writes table tempdb..#useful_groups (1) reads table sybsystemprocs..sysobjects writes table tempdb..#distinct_grantors (1) read_writes table tempdb..#results_table (1) |
