DatabaseProcApplicationCreatedLinks
sybsystemprocssp_logintrigger  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_logintrigger"    
6     **
7     ** 19388, "Global login trigger updated."
8     ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'"
9     */
10    
11    /* 
12    ** IMPORTANT: Please read the following instructions before
13    **   making changes to this stored procedure.
14    **
15    **	To make this stored procedure compatible with High Availability (HA),
16    **	changes to certain system tables must be propagated to the companion 
17    **	server under some conditions.
18    **	The tables include (but are not limited to):
19    **		syslogins, sysservers, sysattributes, systimeranges,
20    **		sysresourcelimits, sysalternates, sysdatabases,
21    **		syslanguages, sysremotelogins, sysloginroles,
22    **		sysalternates (master DB only), systypes (master DB only),
23    **		sysusers (master DB only), sysprotects (master DB only)
24    **	please refer to the HA documentation for details.
25    **
26    **	Here is what you need to do: 
27    **	For each insert/update/delete statement, add three sections to
28    **	-- start HA transaction prior to the statement
29    **	-- add the statement
30    **	-- add HA synchronization code to propagate the change to the companion
31    **
32    **	For example, if you are adding 
33    **		insert master.dbo.syslogins ......
34    **	the code should look like:
35    **	1. Before that SQL statement:
36    **		
37    **	2. Now, the SQL statement:
38    **		insert master.dbo.syslogins ......
39    **	3. Add a HA synchronization section right after the SQL statement:
40    **		
41    **
42    **	You may need to do similar change for each built-in function you
43    **	want to add.
44    **
45    **	Finally, add a separate part at a place where it can not
46    **	be reached by the normal execution path:
47    **	clean_all:
48    **		
49    **		return (1)
50    */
51    
52    create procedure sp_logintrigger
53        @trigname varchar(256) = NULL
54    AS
55        BEGIN
56    
57            DECLARE @msg varchar(1024),
58                @authid int,
59                @suid int,
60                @login_class smallint,
61                @attrib smallint,
62                @action smallint,
63                @dummy int,
64                @HA_CERTIFIED tinyint, /* Is the SP HA certified ? */
65                @retstat int,
66                @config int,
67                @type char(2),
68                @del int
69    
70            /*
71            ** The login trigger is stored in sysattributes:
72            **
73            **	- class:	20
74            **	- attribute:	1
75            **	- type:		'LT'
76            **	- char_value:	login trigger name. It can include database and
77            **	  owner, but the total length must be < 256 chars.
78            **
79            ** If there is a login trigger configured, we will update it, otherwise
80            ** we will insert a new row.
81            **
82            ** If the trigger name is null, the current trigger name will be displayed.
83            ** If the trigger name is the keyword 'drop', the current trigger will be 
84            ** removed from sysattributes.
85            **
86            */
87            select @login_class = 20, @attrib = 1, @type = 'LT'
88            if (@trigname is null)
89            BEGIN
90                select 'name' = char_value,
91                    'status' =
92                    case when (@@logintrigger is null)
93                        then 'Disabled'
94                        else 'Enabled'
95                    end
96                into #tmp
97                from master.dbo.sysattributes
98                where class = 20 and attribute = 1 and object_type = 'LT'
99    
100               exec sp_autoformat
101                   @fulltabname = '#tmp',
102                   @selectlist = '''Global login trigger'' = name, 
103   				''Status'' = status'
104               return 0
105           END
106   
107   
108           /*
109           ** Only a user with sso_role can set login triggers
110           */
111           if (proc_role('sso_role') < 1)
112               return 1
113   
114           select @HA_CERTIFIED = 0
115           select @retstat = 0
116   
117   
118   
119           /* check to see if we are using HA specific SP for a HA enabled server */
120           exec @retstat = sp_ha_check_certified 'sp_logintrigger', @HA_CERTIFIED
121           if (@retstat != 0)
122               return (1)
123   
124           if (upper(@trigname) = 'DROP')
125               select @del = 1
126           else
127               select @del = 0
128   
129   
130           /*
131           ** Check whether a login trigger already exists. If it does, update it
132           ** or delete it, depending on the variable @del.
133           */
134           if exists (select 1 from master.dbo.sysattributes where
135                       class = @login_class
136                       and attribute = @attrib
137                       and object_type = @type)
138           BEGIN
139               if (@del = 0)
140                   select @action = 2 /* update attribute */
141               else
142                   select @action = 3 /* delete attribute */
143           END
144           else
145           BEGIN
146               if (@del = 0)
147                   select @action = 1 /* insert attribute */
148               else
149                   /* Nothing to delete. */
150                   return (0)
151           END
152   
153           /*
154           ** First validate the row. 
155           */
156           if attrib_valid(@login_class, @attrib, 'LT', NULL, NULL, NULL, NULL,
157                   NULL, NULL, @trigname, NULL, NULL, NULL, @action) = 0
158           BEGIN
159               return (1)
160           END
161   
162   
163   
164           /*
165           ** Now insert/update/delete the row
166           */
167           if @action = 1
168           BEGIN
169               insert into master.dbo.sysattributes(class, attribute,
170                   object_type, char_value)
171               values (@login_class, @attrib, @type, @trigname)
172           END
173           else if @action = 2
174           BEGIN
175               update master.dbo.sysattributes
176               set char_value = @trigname
177               where class = @login_class
178                   and attribute = @attrib
179                   and object_type = @type
180   
181           END
182           else if @action = 3
183           BEGIN
184               delete master.dbo.sysattributes
185               where class = @login_class
186                   and attribute = @attrib
187                   and object_type = @type
188           END
189   
190           if (@@error != 0)
191               goto clean_lt_all
192   
193           /*
194           ** 19388, "Global login trigger updated."
195           */
196           exec sp_getmessage 19388, @msg output
197           print @msg
198   
199   
200   
201           /*
202           ** Sync the in-memory RDES with the new values
203           ** in sysattributes.
204           */
205           if attrib_notify(@login_class, @attrib, 'LT', NULL, NULL, NULL,
206                   NULL, NULL, NULL, @trigname, NULL, NULL, NULL,
207                   @action) = 0
208               return (1)
209           else
210               return (@retstat)
211       END
212   
213   clean_lt_all:
214   
215       return (1)
216   


exec sp_procxmode 'sp_logintrigger', 'AnyMode'
go

Grant Execute on sp_logintrigger to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 98
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sp_logintrigger  
 MNER 3 No Error Check should check @@error after select into 90
 MNER 3 No Error Check should check return value of exec 100
 MNER 3 No Error Check should check @@error after insert 169
 MNER 3 No Error Check should check @@error after update 175
 MNER 3 No Error Check should check @@error after delete 184
 MNER 3 No Error Check should check return value of exec 196
 MUCO 3 Useless Code Useless Begin-End Pair 55
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 215
 MUOT 3 Updates outside transaction 184
 QCTC 3 Conditional Table Creation 90
 QIWC 3 Insert with not all columns specified missing 11 columns out of 15 169
 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}
98
 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}
135
 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}
177
 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}
185
 VUNU 3 Variable is not used @authid 58
 VUNU 3 Variable is not used @suid 59
 VUNU 3 Variable is not used @dummy 63
 VUNU 3 Variable is not used @config 66
 MSUB 2 Subquery Marker 134
 MTR1 2 Metrics: Comments Ratio Comments: 50% 52
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 17dec - 4exi + 2 52
 MTR3 2 Metrics: Query Complexity Complexity: 73 52

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_autoformat  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..syscolumns (1)  
   reads table tempdb..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
read_writes table master..sysattributes (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
writes table tempdb..#tmp (1) 

CALLERS
called by proc sybsystemprocs..sp_modifylogin