DatabaseProcApplicationCreatedLinks
sybsystemprocssp_logintrigger  14 déc. 14Defects Propagation 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 or replace 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                @nullarg char(1),
70                @status int,
71                @gp_enabled int
72    
73            /*
74            ** The login trigger is stored in sysattributes:
75            **
76            **	- class:	20
77            **	- attribute:	1
78            **	- type:		'LT'
79            **	- char_value:	login trigger name. It can include database and
80            **	  owner, but the total length must be < 256 chars.
81            **
82            ** If there is a login trigger configured, we will update it, otherwise
83            ** we will insert a new row.
84            **
85            ** If the trigger name is null, the current trigger name will be displayed.
86            ** If the trigger name is the keyword 'drop', the current trigger will be 
87            ** removed from sysattributes.
88            **
89            */
90            select @login_class = 20, @attrib = 1, @type = 'LT'
91            if (@trigname is null)
92            BEGIN
93                select 'name' = char_value,
94                    'status' =
95                    case when (@@logintrigger is null)
96                        then 'Disabled'
97                        else 'Enabled'
98                    end
99                into #tmp
100               from master.dbo.sysattributes
101               where class = 20 and attribute = 1 and object_type = 'LT'
102   
103               exec sp_autoformat
104                   @fulltabname = '#tmp',
105                   @selectlist = '''Global login trigger'' = name, 
106   				''Status'' = status'
107               return 0
108           END
109   
110   
111           /* 
112           ** When setting login triggers:
113           ** If granular permissions is not enabled then sso_role is required.
114           ** If granular permissions is enabled then the permission 
115           ** 'manage security configuration' is required.  proc_role and 
116           ** proc_auditperm will also do auditing if required. Both will also print 
117           ** error message if required.
118           */
119   
120           select @nullarg = NULL
121           execute @status = sp_aux_checkroleperm "sso_role",
122               "manage security configuration", @nullarg, @gp_enabled output
123   
124           /* For Auditing */
125           if (@gp_enabled = 0)
126           begin
127               if (proc_role("sso_role") = 0)
128                   return (1)
129           end
130           else
131           begin
132               select @dummy =
133                   proc_auditperm("manage security configuration", @status)
134           end
135   
136           if (@status != 0)
137               return (1)
138   
139           select @HA_CERTIFIED = 0
140           select @retstat = 0
141   
142   
143   
144           /* check to see if we are using HA specific SP for a HA enabled server */
145           exec @retstat = sp_ha_check_certified 'sp_logintrigger', @HA_CERTIFIED
146           if (@retstat != 0)
147               return (1)
148   
149           if (upper(@trigname) = 'DROP')
150               select @del = 1
151           else
152               select @del = 0
153   
154   
155           /*
156           ** Check whether a login trigger already exists. If it does, update it
157           ** or delete it, depending on the variable @del.
158           */
159           if exists (select 1 from master.dbo.sysattributes where
160                       class = @login_class
161                       and attribute = @attrib
162                       and object_type = @type)
163           BEGIN
164               if (@del = 0)
165                   select @action = 2 /* update attribute */
166               else
167                   select @action = 3 /* delete attribute */
168           END
169           else
170           BEGIN
171               if (@del = 0)
172                   select @action = 1 /* insert attribute */
173               else
174                   /* Nothing to delete. */
175                   return (0)
176           END
177   
178           /*
179           ** First validate the row. 
180           */
181           if attrib_valid(@login_class, @attrib, 'LT', NULL, NULL, NULL, NULL,
182                   NULL, NULL, @trigname, NULL, NULL, NULL, @action) = 0
183           BEGIN
184               return (1)
185           END
186   
187   
188   
189           /*
190           ** Now insert/update/delete the row
191           */
192           if @action = 1
193           BEGIN
194               insert into master.dbo.sysattributes(class, attribute,
195                   object_type, char_value)
196               values (@login_class, @attrib, @type, @trigname)
197           END
198           else if @action = 2
199           BEGIN
200               update master.dbo.sysattributes
201               set char_value = @trigname
202               where class = @login_class
203                   and attribute = @attrib
204                   and object_type = @type
205   
206           END
207           else if @action = 3
208           BEGIN
209               delete master.dbo.sysattributes
210               where class = @login_class
211                   and attribute = @attrib
212                   and object_type = @type
213           END
214   
215           if (@@error != 0)
216               goto clean_lt_all
217   
218           /*
219           ** 19388, "Global login trigger updated."
220           */
221           exec sp_getmessage 19388, @msg output
222           print @msg
223   
224   
225   
226           /*
227           ** Sync the in-memory RDES with the new values
228           ** in sysattributes.
229           */
230           if attrib_notify(@login_class, @attrib, 'LT', NULL, NULL, NULL,
231                   NULL, NULL, NULL, @trigname, NULL, NULL, NULL,
232                   @action) = 0
233               return (1)
234           else
235               return (@retstat)
236       END
237   
238   clean_lt_all:
239   
240       return (1)
241   


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 101
 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 93
 MNER 3 No Error Check should check return value of exec 103
 MNER 3 No Error Check should check @@error after insert 194
 MNER 3 No Error Check should check @@error after update 200
 MNER 3 No Error Check should check @@error after delete 209
 MNER 3 No Error Check should check return value of exec 221
 MUCO 3 Useless Code Useless Begin-End Pair 55
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 215
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 240
 MUOT 3 Updates outside transaction 209
 QCTC 3 Conditional Table Creation 93
 QIWC 3 Insert with not all columns specified missing 11 columns out of 15 194
 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}
101
 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}
160
 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}
202
 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}
210
 VNRD 3 Variable is not read @dummy 132
 VUNU 3 Variable is not used @authid 58
 VUNU 3 Variable is not used @suid 59
 VUNU 3 Variable is not used @config 66
 MSUB 2 Subquery Marker 159
 MTR1 2 Metrics: Comments Ratio Comments: 51% 52
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 20dec - 5exi + 2 52
 MTR3 2 Metrics: Query Complexity Complexity: 81 52

DATA PROPAGATION detailed
ColumnWritten To
@trignamesysattributes.char_value   sysattributes.int_value   °.char_value   sp_dropdevice_rset_001.device sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.status sp_forceonline_object_rset_002.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Access
sp_listsuspect_page_rset_001.Access sp_makesuspect_obj_rset_001.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Indid °.LogType °.PageType
°.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_passwordpolicy_rset_001.message
sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.Online mode sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference

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

CALLERS
called by proc sybsystemprocs..sp_modifylogin