DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ijdbc_gettableprivileges  31 Aug 14Defects Dependencies

1     
2     /*
3     **  sp_ijdbc_gettableprivileges
4     */
5     
6     create procedure sp_ijdbc_gettableprivileges(
7         @table_qualifier varchar(32),
8         @table_owner varchar(32) = null,
9         @table_name varchar(257) = null)
10    as
11    
12        declare @tablename varchar(257)
13        declare @tableowner varchar(128)
14        declare @privlist varchar(128)
15        declare @privdef varchar(128)
16        declare @searchstr char(3)
17    
18        select @searchstr = 'SUV' /* SYSTEM USER VIEW types only */
19    
20        select @privlist = '193      ' + /* SELECT    */
21            '151      ' + /* REFERENCE */
22            '197      ' + /* UPDATE    */
23            '196      ' + /* DELETE    */
24            '195      ' /* INSERT    */
25    
26        select @privdef = 'SELECT   ' +
27            'REFERENCE' +
28            'UPDATE   ' +
29            'DELETE   ' +
30            'INSERT   '
31    
32    
33        select @tablename = @table_name
34        select @tableowner = @table_owner
35    
36        if (@tableowner is null)
37        begin
38            select @tableowner = '%'
39        end
40    
41        if (@tablename is null)
42        begin
43            select @tablename = '%'
44        end
45    
46        delete #tmp_gettableprivileges
47    
48        insert #tmp_gettableprivileges
49    
50        SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(s.uid),
51            'TABLE_NAME' = name, 'GRANTOR' = user_name(s.uid),
52            'GRANTEE' = user_name(s.uid), 'PRIVILEGE' = 'SELECT', 'IS_GRANTABLE' =
53            substring('YESNO ', (select isnull((select p.protecttype
54                            where p.id = s.id
55                                AND p.protecttype != 2
56                                AND p.grantor = p.uid
57                                AND p.action = 193), 0)) * 3 + 1, 3)
58        FROM sysobjects as s, sysprotects as p
59        WHERE
60            name LIKE @tablename ESCAPE '\'
61            AND user_name(s.uid) LIKE @tableowner ESCAPE '\'
62            AND charindex(substring(type, 1, 1), @searchstr) != 0
63            AND not exists (select * from sysobjects s1, sysprotects p1 WHERE
64                    name LIKE @tablename ESCAPE '\'
65                    AND user_name(s.uid) LIKE @tableowner ESCAPE '\'
66                    AND charindex(substring(type, 1, 1), @searchstr) != 0
67                    AND s1.id = p1.id
68                    AND p1.grantor = p1.uid
69                    AND p1.action = 193
70                    AND p1.protecttype = 2)
71    
72        UNION
73        SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(s.uid),
74            'TABLE_NAME' = name, 'GRANTOR' = user_name(s.uid),
75            'GRANTEE' = user_name(s.uid), 'PRIVILEGE' = 'INSERT', 'IS_GRANTABLE' =
76            substring('YESNO ', (select isnull((select p.protecttype
77                            where p.id = s.id
78                                AND p.protecttype != 2
79                                AND p.grantor = p.uid
80                                AND p.action = 195), 0)) * 3 + 1, 3)
81        FROM sysobjects as s, sysprotects as p
82        WHERE
83            name LIKE @tablename ESCAPE '\'
84            AND user_name(s.uid) LIKE @tableowner ESCAPE '\'
85            AND charindex(substring(type, 1, 1), @searchstr) != 0
86            AND not exists (select * from sysobjects s1, sysprotects p1 WHERE
87                    name LIKE @tablename ESCAPE '\'
88                    AND user_name(s.uid) LIKE @tableowner ESCAPE '\'
89                    AND charindex(substring(type, 1, 1), @searchstr) != 0
90                    AND s1.id = p1.id
91                    AND p1.grantor = p1.uid
92                    AND p1.action = 195
93                    AND p1.protecttype = 2)
94        UNION
95        SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(s.uid),
96            'TABLE_NAME' = name, 'GRANTOR' = user_name(s.uid),
97            'GRANTEE' = user_name(s.uid), 'PRIVILEGE' = 'DELETE', 'IS_GRANTABLE' =
98            substring('YESNO ', (select isnull((select p.protecttype
99                            where p.id = s.id
100                               AND p.protecttype != 2
101                               AND p.grantor = p.uid
102                               AND p.action = 196), 0)) * 3 + 1, 3)
103       FROM sysobjects as s, sysprotects as p
104       WHERE
105           name LIKE @tablename ESCAPE '\'
106           AND user_name(s.uid) LIKE @tableowner ESCAPE '\'
107           AND charindex(substring(type, 1, 1), @searchstr) != 0
108           AND not exists (select * from sysobjects s1, sysprotects p1 WHERE
109                   name LIKE @tablename ESCAPE '\'
110                   AND user_name(s.uid) LIKE @tableowner ESCAPE '\'
111                   AND charindex(substring(type, 1, 1), @searchstr) != 0
112                   AND s1.id = p1.id
113                   AND p1.grantor = p1.uid
114                   AND p1.action = 196
115                   AND p1.protecttype = 2)
116       UNION
117       SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(s.uid),
118           'TABLE_NAME' = name, 'GRANTOR' = user_name(s.uid),
119           'GRANTEE' = user_name(s.uid), 'PRIVILEGE' = 'UPDATE', 'IS_GRANTABLE' =
120           substring('YESNO ', (select isnull((select p.protecttype
121                           where p.id = s.id
122                               AND p.protecttype != 2
123                               AND p.grantor = p.uid
124                               AND p.action = 197), 0)) * 3 + 1, 3)
125       FROM sysobjects as s, sysprotects as p
126       WHERE
127           name LIKE @tablename ESCAPE '\'
128           AND user_name(s.uid) LIKE @tableowner ESCAPE '\'
129           AND charindex(substring(type, 1, 1), @searchstr) != 0
130           AND not exists (select * from sysobjects s1, sysprotects p1 WHERE
131                   name LIKE @tablename ESCAPE '\'
132                   AND user_name(s.uid) LIKE @tableowner ESCAPE '\'
133                   AND charindex(substring(type, 1, 1), @searchstr) != 0
134                   AND s1.id = p1.id
135                   AND p1.grantor = p1.uid
136                   AND p1.action = 197
137                   AND p1.protecttype = 2)
138       UNION
139       SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(s.uid),
140           'TABLE_NAME' = name, 'GRANTOR' = user_name(s.uid),
141           'GRANTEE' = user_name(s.uid), 'PRIVILEGE' = 'REFERENCE', 'IS_GRANTABLE' =
142   
143           substring('YESNO ', (select isnull((select p.protecttype
144                           where p.id = s.id
145                               AND p.protecttype != 2
146                               AND p.grantor = p.uid
147                               AND p.action = 151), 0)) * 3 + 1, 3)
148       FROM sysobjects as s, sysprotects as p
149       WHERE
150           name LIKE @tablename ESCAPE '\'
151           AND user_name(s.uid) LIKE @tableowner ESCAPE '\'
152           AND charindex(substring(type, 1, 1), @searchstr) != 0
153   
154           AND not exists (select * from sysobjects s1, sysprotects p1 WHERE
155                   name LIKE @tablename ESCAPE '\'
156                   AND user_name(s.uid) LIKE @tableowner ESCAPE '\'
157                   AND charindex(substring(type, 1, 1), @searchstr) != 0
158                   AND s1.id = p1.id
159                   AND p1.grantor = p1.uid
160                   AND p1.action = 151
161                   AND p1.protecttype = 2)
162       UNION
163       SELECT 'TABLE_CAT' = db_name(), 'TABLE_SCHEM' = user_name(o.uid),
164           'TABLE_NAME' = o.name, 'GRANTOR' = user_name(p.grantor),
165           'GRANTEE' = user_name(p.uid),
166           'PRIVILEGE' =
167           rtrim(substring(@privdef,
168                   charindex(rtrim(convert(char, p.action)), @privlist), 9)),
169           substring('YESNO ', (p.protecttype * 3) + 1, 3)
170       FROM sysprotects p, sysobjects o
171       WHERE o.id = p.id and protecttype < 2
172           AND o.name LIKE @tablename ESCAPE '\'
173           AND user_name(o.uid) LIKE @tableowner ESCAPE '\'
174           AND p.action in (193, 151, 197, 196, 195)
175           AND charindex(substring(type, 1, 1), @searchstr) != 0
176       ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE
177   
178   /*
179   ** End of sp_ijdbc_gettableprivileges
180   */
181   


exec sp_procxmode 'sp_ijdbc_gettableprivileges', 'AnyMode'
go

Grant Execute on sp_ijdbc_gettableprivileges to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysprotects sybsystemprocs..sysprotects
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
55
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
65
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
69
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
78
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
88
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
92
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
100
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
110
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
114
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
122
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
132
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
136
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
145
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
156
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
160
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprotects.csysprotects unique clustered
(id, action, grantor, uid, protecttype)
Intersection: {protecttype, action}
171
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 55
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 57
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 69
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 70
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 78
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 80
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 92
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 93
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 100
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 102
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 114
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 115
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 122
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 124
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 136
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 137
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 145
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 147
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 160
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 161
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 171
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_gettableprivileges  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprotects  
 MNER 3 No Error Check should check @@error after delete 46
 MNER 3 No Error Check should check @@error after insert 48
 MUCO 3 Useless Code Useless Brackets in create proc 6
 MUCO 3 Useless Code Useless Brackets 36
 MUCO 3 Useless Code Useless Brackets 41
 MUCO 3 Useless Code Useless Brackets 169
 QNAJ 3 Not using ANSI Inner Join 58
 QNAJ 3 Not using ANSI Inner Join 63
 QNAJ 3 Not using ANSI Inner Join 81
 QNAJ 3 Not using ANSI Inner Join 86
 QNAJ 3 Not using ANSI Inner Join 103
 QNAJ 3 Not using ANSI Inner Join 108
 QNAJ 3 Not using ANSI Inner Join 125
 QNAJ 3 Not using ANSI Inner Join 130
 QNAJ 3 Not using ANSI Inner Join 148
 QNAJ 3 Not using ANSI Inner Join 154
 QNAJ 3 Not using ANSI Inner Join 170
 QNUA 3 Should use Alias: Column name should use alias s 51
 QNUA 3 Should use Alias: Column name should use alias s 60
 QNUA 3 Should use Alias: Column type should use alias s 62
 QNUA 3 Should use Alias: Column name should use alias s1 64
 QNUA 3 Should use Alias: Column type should use alias s1 66
 QNUA 3 Should use Alias: Column name should use alias s 74
 QNUA 3 Should use Alias: Column name should use alias s 83
 QNUA 3 Should use Alias: Column type should use alias s 85
 QNUA 3 Should use Alias: Column name should use alias s1 87
 QNUA 3 Should use Alias: Column type should use alias s1 89
 QNUA 3 Should use Alias: Column name should use alias s 96
 QNUA 3 Should use Alias: Column name should use alias s 105
 QNUA 3 Should use Alias: Column type should use alias s 107
 QNUA 3 Should use Alias: Column name should use alias s1 109
 QNUA 3 Should use Alias: Column type should use alias s1 111
 QNUA 3 Should use Alias: Column name should use alias s 118
 QNUA 3 Should use Alias: Column name should use alias s 127
 QNUA 3 Should use Alias: Column type should use alias s 129
 QNUA 3 Should use Alias: Column name should use alias s1 131
 QNUA 3 Should use Alias: Column type should use alias s1 133
 QNUA 3 Should use Alias: Column name should use alias s 140
 QNUA 3 Should use Alias: Column name should use alias s 150
 QNUA 3 Should use Alias: Column type should use alias s 152
 QNUA 3 Should use Alias: Column name should use alias s1 155
 QNUA 3 Should use Alias: Column type should use alias s1 157
 QNUA 3 Should use Alias: Column protecttype should use alias p 171
 QNUA 3 Should use Alias: Column type should use alias o 175
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
64
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
87
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
109
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
131
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
155
 QTJ1 3 Table only appears in inner join clause 58
 QTJ1 3 Table only appears in inner join clause 81
 QTJ1 3 Table only appears in inner join clause 103
 QTJ1 3 Table only appears in inner join clause 125
 QTJ1 3 Table only appears in inner join clause 148
 QUNI 3 Check Use of 'union' vs 'union all' 50
 VUNU 3 Variable is not used @table_qualifier 7
 MSUC 2 Correlated Subquery Marker 53
 MSUC 2 Correlated Subquery Marker 63
 MSUC 2 Correlated Subquery Marker 76
 MSUC 2 Correlated Subquery Marker 86
 MSUC 2 Correlated Subquery Marker 98
 MSUC 2 Correlated Subquery Marker 108
 MSUC 2 Correlated Subquery Marker 120
 MSUC 2 Correlated Subquery Marker 130
 MSUC 2 Correlated Subquery Marker 143
 MSUC 2 Correlated Subquery Marker 154
 MTR1 2 Metrics: Comments Ratio Comments: 2% 6
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 2dec - 1exi + 2 6
 MTR3 2 Metrics: Query Complexity Complexity: 125 6
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 53
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 63
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 76
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 86
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 98
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 108
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 120
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 130
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 143
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 154
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprotects} 0 163

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#tmp_gettableprivileges (1) 
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysprotects