DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_gettableprivileges  31 Aug 14Defects 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

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 39
 MEST 4 Empty String will be replaced by Single Space 43
 MTYP 4 Assignment type mismatch @table_name: varchar(255) = varchar(771) 154
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause tpriv_cursor 82
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_gettableprivileges  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after insert 62
 MNER 3 No Error Check should check return value of exec 154
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Brackets 15
 MUCO 3 Useless Code Useless Brackets 21
 MUCO 3 Useless Code Useless Brackets 30
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 179
 MUIN 3 Column created using implicit nullability 55
 MUIN 3 Column created using implicit nullability 70
 MUIN 3 Column created using implicit nullability 108
 MUIN 3 Column created using implicit nullability 121
 MUIN 3 Column created using implicit nullability 131
 MUIN 3 Column created using implicit nullability 139
 QCTC 3 Conditional Table Creation 108
 QCTC 3 Conditional Table Creation 121
 QCTC 3 Conditional Table Creation 131
 QCTC 3 Conditional Table Creation 139
 QISO 3 Set isolation level 28
 CUPD 2 Updatable Cursor Marker (updatable by default) 82
 MRST 2 Result Set Marker 170
 MTR1 2 Metrics: Comments Ratio Comments: 34% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 10dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 68 2

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)