DatabaseProcApplicationCreatedLinks
sybsystemprocssp_is_valid_user  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for "sp_is_valid_user"
5     ** 	Currently, sp_is_valid_user does not use any messages.
6     **
7     ** Description
8     **
9     **	This procedure is undocumented. It doesn't support Granular Permission.
10    **	Users should run this procedure at their own discretion.
11    **
12    ** 	sp_is_valid_user determines if a user is a 'valid user' in a specified 
13    **	database.  
14    **
15    **	For a user to be considered a 'valid user' in a database, one or more 
16    **	of the following criteria must be met:
17    **		o The user has been granted sa_role.
18    **		o The user is the database owner
19    **		o The user's suid is listed in the target database's
20    **                Sysusers or Sysalternates tables.
21    **              o The target database's Sysusers table contains a 
22    **                guest entry 
23    **
24    ** Parameters
25    **	@uid		- (output)
26    **		  	  Values returned are:
27    **		           The user id of the user in the database.
28    **		           0 if the user is not a valid user in the database.
29    **	@dbname		- name of the target database. null indicates 
30    **			  current database.
31    **	@loginname 	- name of the login being checked, null indicates
32    **			  current login.
33    **      @mode   	- Default value is 0. This is a bit mask to special 
34    **			  case the search criteria.
35    ** 		  		1 => ignore the guest user
36    ** 		  		2 => ignore the sa_role
37    ** 		  		4 => ignore the aliases
38    **
39    ** Returns
40    ** 	0 	- sproc completed successfully
41    **	1	- sproc didn't complete successfully as database could not be 
42    **		  opened.
43    **
44    ** Note
45    **	This procedure may create a temporary table so unless the 'ddl in tran'
46    **	option is turned on for the temp database this procedure may 
47    **	raise the error 2762.
48    **
49    */
50    create or replace procedure sp_is_valid_user
51        @uid int output, /* Output, user id, 0 if invalid user */
52        @dbname varchar(255) = NULL, /* db name to look in. */
53        @loginname varchar(255) = NULL, /* login name to check for */
54        @mode int = 0 /* Mode of search, see description */
55    as
56        begin
57            declare @suid int, /* suid being checked for */
58                @suidstr varchar(32), /* suid as a varchar */
59                @cmd varchar(2000), /* Variable to store the dynamic SQL */
60                @ignore_guest int, /* value for ignore_guest mode */
61                @ignore_sarole int, /* value for ignore_sarole mode */
62                @ignore_alias int /* value for ignore_alias mode */
63    
64            set nocount on
65    
66            /* Initialize @uid to 0 */
67            select @uid = 0
68    
69            /* 
70            ** If @loginname is not specified, work on current login.
71            ** If @dbname is not specified check in current database.
72            */
73            select @suid = case
74                    when @loginname is null then suser_id()
75                    else suser_id(@loginname)
76                end
77                , @dbname = isnull(@dbname, db_name())
78    
79            /* If @suid is NULL i.e. an invalid login name is specified return. */
80            if (@suid is NULL)
81            begin
82                return 0
83            end
84    
85            /* Initialize other variables */
86            select @suidstr = convert(varchar, @suid),
87                @ignore_guest = 1, @ignore_sarole = 2, @ignore_alias = 4
88    
89            /* If we are not ignoring sa_role check if the user has sa_role. */
90            if ((@mode & @ignore_sarole = 0)
91                    and exists (select 1 from master.dbo.sysloginroles
92                        where suid = @suid and srid = role_id('sa_role')))
93            begin
94                select @uid = 1
95                return 0
96            end
97    
98            /* Check if this user is the database owner.  */
99            if exists (select 1 from master.dbo.sysdatabases
100                   where name = @dbname and suid = @suid)
101           begin
102               select @uid = 1
103               return 0
104           end
105   
106           /* We now need to check the database so check if the db is online */
107           if exists (select 1 from master.dbo.sysdatabases
108                   where name = @dbname
109                       and (status & 256 = 256 or status2 & 16 = 16))
110           begin
111               return 1
112           end
113   
114           /* Create temp table for getting uid back from dynamic SQL */
115           create table #t(uid int)
116   
117           /* Now look for the user in sysusers and sysalternates.  */
118   
119           /* construct sql to query sysusers */
120           select @cmd = "insert into #t select uid from "
121               + @dbname + ".dbo.sysusers where suid = " + @suidstr
122   
123           /* If we are not ignoring aliases query sysalternates */
124           if (@mode & @ignore_alias = 0)
125           begin
126               select @cmd = @cmd + " or suid = (select altsuid from "
127                   + @dbname + ".dbo.sysalternates where suid = "
128                   + @suidstr + ")"
129           end
130   
131           exec (@cmd)
132   
133           /*
134           ** If user exists get the uid else if we are not ignoring guest user 
135           ** check for guest user, if present then return the guest user id.
136           */
137           if (@@rowcount > 0)
138           begin
139               /* Get the uid from temp table */
140               select @uid = uid from #t
141           end
142           else if (@mode & @ignore_guest = 0)
143           begin
144               select @cmd = "declare @dummy int select @dummy = 1 from "
145                   + @dbname
146                   + ".dbo.sysusers where suid = -1 and uid = 2"
147               exec (@cmd)
148               if (@@rowcount > 0)
149               begin
150                   select @uid = 2
151               end
152           end
153   
154           drop table #t
155   
156           set nocount off
157   
158           return (0)
159       end
160   
161   


exec sp_procxmode 'sp_is_valid_user', 'AnyMode'
go

Grant Execute on sp_is_valid_user to public
go
DEFECTS
 TNOU 4 Table with no unique index master..sysloginroles master..sysloginroles
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysloginroles  
 MGTP 3 Grant to public sybsystemprocs..sp_is_valid_user  
 MUCO 3 Useless Code Useless Begin-End Pair 56
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 158
 MUIN 3 Column created using implicit nullability 115
 QAFM 3 Var Assignment from potentially many rows 140
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 92
 MDYS 2 Dynamic SQL Marker 131
 MDYS 2 Dynamic SQL Marker 147
 MSUB 2 Subquery Marker 91
 MSUB 2 Subquery Marker 99
 MSUB 2 Subquery Marker 107
 MTR1 2 Metrics: Comments Ratio Comments: 56% 50
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 14dec - 5exi + 2 50
 MTR3 2 Metrics: Query Complexity Complexity: 53 50

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysloginroles (1)  
read_writes table tempdb..#t (1) 
reads table master..sysdatabases (1)  

CALLERS
called by proc sybsystemprocs..sp_plan_dbccdb