Database | Proc | Application | Created | Links |
sybsystemprocs | sp_logintrigger ![]() | ![]() | 31 Aug 14 | Defects 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 | |
![]() | master..sysattributes |
![]() | 98 |
![]() | |
![]() | |
![]() | 90 |
![]() | 100 |
![]() | 169 |
![]() | 175 |
![]() | 184 |
![]() | 196 |
![]() | 55 |
![]() | 88 |
![]() | 92 |
![]() | 111 |
![]() | 121 |
![]() | 122 |
![]() | 124 |
![]() | 139 |
![]() | 146 |
![]() | 150 |
![]() | 159 |
![]() | 190 |
![]() | 208 |
![]() | 210 |
![]() | 215 |
![]() | 184 |
![]() | 90 |
![]() | 169 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type, attribute} | 98 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type, attribute} | 135 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type, attribute} | 177 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type, attribute} | 185 |
![]() | 58 |
![]() | 59 |
![]() | 63 |
![]() | 66 |
![]() | 134 |
![]() | 52 |
![]() | 52 |
![]() | 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 ![]() |