DatabaseProcApplicationCreatedLinks
sybsystemprocssp_activeroles  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	5.0	1.0	01/28/97	sproc/src/activeroles */
4     
5     /*
6     ** Messages for "sp_activeroles"	18341
7     ** 18341, "An invalid argument was entered. Usage: sp_activeroles [expand_down]"
8     */
9     
10    create or replace procedure sp_activeroles
11        @mode varchar(30) = NULL /* "expand_down" only */
12    
13    as
14    
15        declare @hier_level int
16            , @udr_class int /* value of UDR_CLASS in sysattrib's */
17            , @udr_role_hier int /* value of attribute in sysattributes
18            ** for rows corresponding to role 
19            ** hierarchy
20            */
21            , @msg varchar(255) /* message holder */
22            , @rolelist varchar(255) /* list of roles from show_role() */
23            , @maxsysrolenum int
24            , @lpid int
25    
26        select @maxsysrolenum = 31
27        select @lpid = NULL
28    
29        /* 
30        ** A @@trancount of 0 indicates no current transaction.
31        */
32        if @@trancount = 0
33        begin
34            /* In chained mode, SQL Server implicitly executes a "begin 
35            ** transaction" before the following statements: delete, fetch,
36            ** insert, open, select, and update.
37            */
38            set chained off
39        end
40    
41        /* Exclusive lock on objects being changed, held until transaction ends. No
42        ** shared locks
43        */
44        set transaction isolation level 1
45    
46        /* Initialize variables */
47        select @udr_class = class
48        from master.dbo.sysattributes
49        where object_type = "UR"
50    
51        select @udr_role_hier = 2
52    
53        /* Convert arg to lower case with no leading/trailing blanks. */
54        if @mode is not NULL
55        begin
56            select @mode = lower(ltrim(rtrim(@mode)))
57    
58            if (@mode != "expand_down")
59            begin
60                /* 18341, "An invalid argument was entered. 
61                ** 	   Usage: sp_activeroles [expand_down]." 
62                */
63                raiserror 18341
64                return (1)
65            end
66        end
67    
68        select @hier_level = 1
69    
70        /*
71        ** Get user's login profile id, if any; otherwise see if there's a default
72        */
73        select @lpid = lpid from master.dbo.syslogins
74        where name = suser_name()
75        if (@lpid = NULL)
76        begin
77            select @lpid = object from master.dbo.sysattributes
78            where class = 39 and attribute = 4 and object_type = "LR"
79        end
80        /*
81        ** Create a temporary table to hold the intermediate result of expansion
82        */
83        create table #intermediate_roleset
84        (rolename varchar(30) not null,
85            parentrolename varchar(30) null,
86            role_level smallint null)
87    
88        /* create another temporary table to hold another result of expansion */
89    
90        create table #tmp_roleset
91        (rolename varchar(30) not null,
92            parentrolename varchar(30) null,
93            role_level smallint null)
94    
95        /*
96        ** Select all rolenames from syssrvroles for this user and
97        ** his login profile, and insert the ones for which
98        ** charindex(r.name, show_role()) returns > 0 into
99        ** #intermediate_roleset.  This gives the active system roles
100       ** without using a call to proc_role().
101       */
102       select @rolelist = show_role()
103   
104       insert into #intermediate_roleset(rolename, role_level)
105       select distinct r.name, @hier_level
106       from master.dbo.sysloginroles l,
107           master.dbo.syssrvroles r
108       where l.srid = r.srid
109           and charindex(r.name, @rolelist) > 0
110           and (l.suid = suser_id()
111               or (@lpid != NULL and l.suid = @lpid))
112   
113       /*
114       ** Get the user-defined roles for this user and his login profile.
115       ** We will need to call proc_role() on these.
116       */
117       insert into #tmp_roleset(rolename, role_level)
118       select distinct r.name, @hier_level
119       from master.dbo.sysloginroles l,
120           master.dbo.syssrvroles r
121       where l.srid = r.srid
122           and r.srid > @maxsysrolenum
123           and (l.suid = suser_id()
124               or (@lpid != NULL and l.suid = @lpid))
125   
126       /* Call proc_role() on the user-defined roles to see which
127       ** are active, and insert them into the intermediate table.
128       */
129       insert into #intermediate_roleset(rolename, role_level)
130       select rolename, role_level from #tmp_roleset t
131       where proc_role(t.rolename) = 1
132   
133       if @mode is NULL
134       begin
135           /* display the rows from #intermediate_roleset and return */
136   
137           select "Role Name" = rolename
138           from #intermediate_roleset
139   
140           return (0)
141       end
142   
143       /* create two more temp tables, #cumulative_role_set and #temp_role_set */
144       create table #cumulative_role_set
145       (rolename varchar(30) not null,
146           parentrolename varchar(30) null,
147           role_level smallint null)
148   
149       create table #temp_role_set
150       (rolename varchar(30) not null,
151           parentrolename varchar(30) null,
152           role_level smallint null)
153   
154       /* 
155       ** For the while loop below,
156       **	the start condition is:
157       **		#cumulative_role_set contains nothing
158       **		#intermediate_roleset contains the base set of roles
159       **		#temp_roleset contains nothing
160       */
161   
162       while exists (select * from #intermediate_roleset)
163       begin
164           select @hier_level = @hier_level + 1
165   
166           /* insert into #temp_role_set(role_level, select role1, role2 
167           ** from sysattributes where row is of type 'role1 contains 
168           ** role2' and role1 is in #intermediate_roleset)
169           */
170   
171           insert into #temp_role_set(rolename, parentrolename, role_level)
172           select role_name(object_info1), rolename, @hier_level
173           from master.dbo.sysattributes, #intermediate_roleset
174           where object = role_id(rolename)
175               and class = @udr_class
176               and attribute = @udr_role_hier
177   
178           /* copy all rows from #intermediate_roleset into #cumulative_role_set*/
179           /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #cumulative_role_set
180           select #intermediate_roleset.rolename, #intermediate_roleset.parentrolename, #intermediate_roleset.role_level
181           from #intermediate_roleset
182           where rolename not in (select rolename from #cumulative_role_set)
183               or parentrolename not in (select parentrolename
184                   from #cumulative_role_set
185                   where parentrolename is not null)
186   
187           /* delete all rows from #intermediate_roleset */
188           delete from #intermediate_roleset
189   
190           /* copy all rows from #temp_role_set into #intermediate_roleset */
191           /* Adaptive Server has expanded all '*' elements in the following statement */ insert into #intermediate_roleset
192           select #temp_role_set.rolename, #temp_role_set.parentrolename, #temp_role_set.role_level
193           from #temp_role_set
194   
195           /* delete all rows from #temp_roleset */
196           delete from #temp_role_set
197       end
198   
199       /* select all rows from #cumulative_role_set and display it */
200   
201       select distinct "Role Name" = rolename, "Parent Role Name" = parentrolename,
202           "Level" = role_level
203       from #cumulative_role_set
204       order by role_level
205   
206       return (0)
207   
208   


exec sp_procxmode 'sp_activeroles', 'AnyMode'
go

Grant Execute on sp_activeroles to public
go
RESULT SETS
sp_activeroles_rset_002
sp_activeroles_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch role_level: smallint = int 105
 MTYP 4 Assignment type mismatch role_level: smallint = int 118
 MTYP 4 Assignment type mismatch role_level: smallint = int 172
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type}
49
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object}
Uncovered: [object_type, object_info1, object_info2, object_info3, object_cinfo]
174
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 78
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 175
 QTYP 4 Comparison type mismatch smallint = int 175
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 176
 QTYP 4 Comparison type mismatch smallint = int 176
 TNOU 4 Table with no unique index master..sysloginroles master..sysloginroles
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysloginroles  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_activeroles  
 MNAC 3 Not using ANSI 'is null' 75
 MNAC 3 Not using ANSI 'is null' 111
 MNAC 3 Not using ANSI 'is null' 124
 MNER 3 No Error Check should check @@error after insert 104
 MNER 3 No Error Check should check @@error after insert 117
 MNER 3 No Error Check should check @@error after insert 129
 MNER 3 No Error Check should check @@error after insert 171
 MNER 3 No Error Check should check @@error after insert 179
 MNER 3 No Error Check should check @@error after delete 188
 MNER 3 No Error Check should check @@error after insert 191
 MNER 3 No Error Check should check @@error after delete 196
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 206
 QAFM 3 Var Assignment from potentially many rows 47
 QAFM 3 Var Assignment from potentially many rows 77
 QCRS 3 Conditional Result Set 137
 QDIS 3 Check correct use of 'select distinct' 105
 QDIS 3 Check correct use of 'select distinct' 118
 QGWO 3 Group by/Distinct/Union without order by 105
 QGWO 3 Group by/Distinct/Union without order by 118
 QISO 3 Set isolation level 44
 QIWC 3 Insert with not all columns specified missing 1 columns out of 3 104
 QIWC 3 Insert with not all columns specified missing 1 columns out of 3 117
 QIWC 3 Insert with not all columns specified missing 1 columns out of 3 129
 QJWT 3 Join or Sarg Without Index on temp table 182
 QJWT 3 Join or Sarg Without Index on temp table 183
 QNAJ 3 Not using ANSI Inner Join 106
 QNAJ 3 Not using ANSI Inner Join 119
 QNAJ 3 Not using ANSI Inner Join 173
 QNUA 3 Should use Alias: Column object_info1 should use alias sysattributes 172
 QNUA 3 Should use Alias: Column rolename should use alias #intermediate_roleset 172
 QNUA 3 Should use Alias: Table #intermediate_roleset 173
 QNUA 3 Should use Alias: Table master..sysattributes 173
 QNUA 3 Should use Alias: Column object should use alias sysattributes 174
 QNUA 3 Should use Alias: Column rolename should use alias #intermediate_roleset 174
 QNUA 3 Should use Alias: Column class should use alias sysattributes 175
 QNUA 3 Should use Alias: Column attribute should use alias sysattributes 176
 QPNC 3 No column in condition 111
 QPNC 3 No column in condition 124
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, attribute}
78
 QSWV 3 Sarg with variable @lpid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 111
 QSWV 3 Sarg with variable @maxsysrolenum, Candidate Index: syssrvroles.csyssrvroles unique clustered(srid) F 122
 QSWV 3 Sarg with variable @lpid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 124
 QTLO 3 Top-Level OR 182
 VUNU 3 Variable is not used @msg 21
 MRST 2 Result Set Marker 137
 MRST 2 Result Set Marker 201
 MSUB 2 Subquery Marker 183
 MTR1 2 Metrics: Comments Ratio Comments: 42% 10
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 9dec - 3exi + 2 10
 MTR3 2 Metrics: Query Complexity Complexity: 95 10
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 105
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 118

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#intermediate_roleset (1) 
reads table master..sysloginroles (1)  
read_writes table tempdb..#temp_role_set (1) 
writes table sybsystemprocs..sp_activeroles_rset_001 
reads table master..syssrvroles (1)  
read_writes table tempdb..#cumulative_role_set (1) 
read_writes table tempdb..#tmp_roleset (1) 
reads table master..syslogins (1)  
reads table master..sysattributes (1)  
writes table sybsystemprocs..sp_activeroles_rset_002