1//go:build integration
2// +build integration
3
4package sqlstore
5
6import (
7	"context"
8	"fmt"
9	"testing"
10
11	"github.com/grafana/grafana/pkg/models"
12	"github.com/grafana/grafana/pkg/setting"
13	"github.com/stretchr/testify/require"
14)
15
16func TestUserDataAccess(t *testing.T) {
17
18	ss := InitTestDB(t)
19
20	t.Run("Testing DB - creates and loads user", func(t *testing.T) {
21		cmd := models.CreateUserCommand{
22			Email: "usertest@test.com",
23			Name:  "user name",
24			Login: "user_test_login",
25		}
26		user, err := ss.CreateUser(context.Background(), cmd)
27		require.NoError(t, err)
28
29		query := models.GetUserByIdQuery{Id: user.Id}
30		err = GetUserById(context.Background(), &query)
31		require.Nil(t, err)
32
33		require.Equal(t, query.Result.Email, "usertest@test.com")
34		require.Equal(t, query.Result.Password, "")
35		require.Len(t, query.Result.Rands, 10)
36		require.Len(t, query.Result.Salt, 10)
37		require.False(t, query.Result.IsDisabled)
38
39		query = models.GetUserByIdQuery{Id: user.Id}
40		err = GetUserById(context.Background(), &query)
41		require.Nil(t, err)
42
43		require.Equal(t, query.Result.Email, "usertest@test.com")
44		require.Equal(t, query.Result.Password, "")
45		require.Len(t, query.Result.Rands, 10)
46		require.Len(t, query.Result.Salt, 10)
47		require.False(t, query.Result.IsDisabled)
48	})
49
50	t.Run("Testing DB - creates and loads disabled user", func(t *testing.T) {
51		ss = InitTestDB(t)
52		cmd := models.CreateUserCommand{
53			Email:      "usertest@test.com",
54			Name:       "user name",
55			Login:      "user_test_login",
56			IsDisabled: true,
57		}
58
59		user, err := ss.CreateUser(context.Background(), cmd)
60		require.Nil(t, err)
61
62		query := models.GetUserByIdQuery{Id: user.Id}
63		err = GetUserById(context.Background(), &query)
64		require.Nil(t, err)
65
66		require.Equal(t, query.Result.Email, "usertest@test.com")
67		require.Equal(t, query.Result.Password, "")
68		require.Len(t, query.Result.Rands, 10)
69		require.Len(t, query.Result.Salt, 10)
70		require.True(t, query.Result.IsDisabled)
71	})
72
73	t.Run("Testing DB - create user assigned to other organization", func(t *testing.T) {
74		ss = InitTestDB(t)
75
76		autoAssignOrg := setting.AutoAssignOrg
77		setting.AutoAssignOrg = true
78		defer func() {
79			setting.AutoAssignOrg = autoAssignOrg
80		}()
81
82		orgCmd := &models.CreateOrgCommand{Name: "Some Test Org"}
83		err := CreateOrg(context.Background(), orgCmd)
84		require.Nil(t, err)
85
86		cmd := models.CreateUserCommand{
87			Email: "usertest@test.com",
88			Name:  "user name",
89			Login: "user_test_login",
90			OrgId: orgCmd.Result.Id,
91		}
92
93		user, err := ss.CreateUser(context.Background(), cmd)
94		require.Nil(t, err)
95
96		query := models.GetUserByIdQuery{Id: user.Id}
97		err = GetUserById(context.Background(), &query)
98		require.Nil(t, err)
99
100		require.Equal(t, query.Result.Email, "usertest@test.com")
101		require.Equal(t, query.Result.Password, "")
102		require.Len(t, query.Result.Rands, 10)
103		require.Len(t, query.Result.Salt, 10)
104		require.False(t, query.Result.IsDisabled)
105		require.Equal(t, query.Result.OrgId, orgCmd.Result.Id)
106
107		const nonExistingOrgID = 10000
108		cmd = models.CreateUserCommand{
109			Email: "usertest@test.com",
110			Name:  "user name",
111			Login: "user_test_login",
112			OrgId: nonExistingOrgID,
113		}
114
115		_, err = ss.CreateUser(context.Background(), cmd)
116		require.Equal(t, err, models.ErrOrgNotFound)
117	})
118
119	t.Run("Testing DB - multiple users", func(t *testing.T) {
120		ss = InitTestDB(t)
121
122		createFiveTestUsers(t, ss, func(i int) *models.CreateUserCommand {
123			return &models.CreateUserCommand{
124				Email:      fmt.Sprint("user", i, "@test.com"),
125				Name:       fmt.Sprint("user", i),
126				Login:      fmt.Sprint("loginuser", i),
127				IsDisabled: false,
128			}
129		})
130
131		// Return the first page of users and a total count
132		query := models.SearchUsersQuery{Query: "", Page: 1, Limit: 3}
133		err := SearchUsers(context.Background(), &query)
134
135		require.Nil(t, err)
136		require.Len(t, query.Result.Users, 3)
137		require.EqualValues(t, query.Result.TotalCount, 5)
138
139		// Return the second page of users and a total count
140		query = models.SearchUsersQuery{Query: "", Page: 2, Limit: 3}
141		err = SearchUsers(context.Background(), &query)
142
143		require.Nil(t, err)
144		require.Len(t, query.Result.Users, 2)
145		require.EqualValues(t, query.Result.TotalCount, 5)
146
147		// Return list of users matching query on user name
148		query = models.SearchUsersQuery{Query: "use", Page: 1, Limit: 3}
149		err = SearchUsers(context.Background(), &query)
150
151		require.Nil(t, err)
152		require.Len(t, query.Result.Users, 3)
153		require.EqualValues(t, query.Result.TotalCount, 5)
154
155		query = models.SearchUsersQuery{Query: "ser1", Page: 1, Limit: 3}
156		err = SearchUsers(context.Background(), &query)
157
158		require.Nil(t, err)
159		require.Len(t, query.Result.Users, 1)
160		require.EqualValues(t, query.Result.TotalCount, 1)
161
162		query = models.SearchUsersQuery{Query: "USER1", Page: 1, Limit: 3}
163		err = SearchUsers(context.Background(), &query)
164
165		require.Nil(t, err)
166		require.Len(t, query.Result.Users, 1)
167		require.EqualValues(t, query.Result.TotalCount, 1)
168
169		query = models.SearchUsersQuery{Query: "idontexist", Page: 1, Limit: 3}
170		err = SearchUsers(context.Background(), &query)
171
172		require.Nil(t, err)
173		require.Len(t, query.Result.Users, 0)
174		require.EqualValues(t, query.Result.TotalCount, 0)
175
176		// Return list of users matching query on email
177		query = models.SearchUsersQuery{Query: "ser1@test.com", Page: 1, Limit: 3}
178		err = SearchUsers(context.Background(), &query)
179
180		require.Nil(t, err)
181		require.Len(t, query.Result.Users, 1)
182		require.EqualValues(t, query.Result.TotalCount, 1)
183
184		// Return list of users matching query on login name
185		query = models.SearchUsersQuery{Query: "loginuser1", Page: 1, Limit: 3}
186		err = SearchUsers(context.Background(), &query)
187
188		require.Nil(t, err)
189		require.Len(t, query.Result.Users, 1)
190		require.EqualValues(t, query.Result.TotalCount, 1)
191	})
192
193	t.Run("Testing DB - return list users based on their is_disabled flag", func(t *testing.T) {
194		ss = InitTestDB(t)
195		createFiveTestUsers(t, ss, func(i int) *models.CreateUserCommand {
196			return &models.CreateUserCommand{
197				Email:      fmt.Sprint("user", i, "@test.com"),
198				Name:       fmt.Sprint("user", i),
199				Login:      fmt.Sprint("loginuser", i),
200				IsDisabled: i%2 == 0,
201			}
202		})
203
204		isDisabled := false
205		query := models.SearchUsersQuery{IsDisabled: &isDisabled}
206		err := SearchUsers(context.Background(), &query)
207		require.Nil(t, err)
208
209		require.Len(t, query.Result.Users, 2)
210
211		first, third := false, false
212		for _, user := range query.Result.Users {
213			if user.Name == "user1" {
214				first = true
215			}
216
217			if user.Name == "user3" {
218				third = true
219			}
220		}
221
222		require.True(t, first)
223		require.True(t, third)
224
225		// Re-init DB
226		ss = InitTestDB(t)
227		users := createFiveTestUsers(t, ss, func(i int) *models.CreateUserCommand {
228			return &models.CreateUserCommand{
229				Email:      fmt.Sprint("user", i, "@test.com"),
230				Name:       fmt.Sprint("user", i),
231				Login:      fmt.Sprint("loginuser", i),
232				IsDisabled: false,
233			}
234		})
235
236		err = ss.AddOrgUser(context.Background(), &models.AddOrgUserCommand{
237			LoginOrEmail: users[1].Login, Role: models.ROLE_VIEWER,
238			OrgId: users[0].OrgId, UserId: users[1].Id,
239		})
240		require.Nil(t, err)
241
242		err = testHelperUpdateDashboardAcl(t, ss, 1, models.DashboardAcl{
243			DashboardID: 1, OrgID: users[0].OrgId, UserID: users[1].Id,
244			Permission: models.PERMISSION_EDIT,
245		})
246		require.Nil(t, err)
247
248		err = ss.SavePreferences(context.Background(), &models.SavePreferencesCommand{
249			UserId: users[1].Id, OrgId: users[0].OrgId, HomeDashboardId: 1, Theme: "dark",
250		})
251		require.Nil(t, err)
252
253		// When the user is deleted
254		err = DeleteUser(context.Background(), &models.DeleteUserCommand{UserId: users[1].Id})
255		require.Nil(t, err)
256
257		query1 := &models.GetOrgUsersQuery{OrgId: users[0].OrgId}
258		err = GetOrgUsersForTest(query1)
259		require.Nil(t, err)
260
261		require.Len(t, query1.Result, 1)
262
263		permQuery := &models.GetDashboardAclInfoListQuery{DashboardID: 1, OrgID: users[0].OrgId}
264		err = ss.GetDashboardAclInfoList(context.Background(), permQuery)
265		require.Nil(t, err)
266
267		require.Len(t, permQuery.Result, 0)
268
269		prefsQuery := &models.GetPreferencesQuery{OrgId: users[0].OrgId, UserId: users[1].Id}
270		err = ss.GetPreferences(context.Background(), prefsQuery)
271		require.Nil(t, err)
272
273		require.EqualValues(t, prefsQuery.Result.OrgId, 0)
274		require.EqualValues(t, prefsQuery.Result.UserId, 0)
275
276		// A user is an org member and has been assigned permissions
277		// Re-init DB
278		ss = InitTestDB(t)
279		users = createFiveTestUsers(t, ss, func(i int) *models.CreateUserCommand {
280			return &models.CreateUserCommand{
281				Email:      fmt.Sprint("user", i, "@test.com"),
282				Name:       fmt.Sprint("user", i),
283				Login:      fmt.Sprint("loginuser", i),
284				IsDisabled: false,
285			}
286		})
287		err = ss.AddOrgUser(context.Background(), &models.AddOrgUserCommand{
288			LoginOrEmail: users[1].Login, Role: models.ROLE_VIEWER,
289			OrgId: users[0].OrgId, UserId: users[1].Id,
290		})
291		require.Nil(t, err)
292
293		err = testHelperUpdateDashboardAcl(t, ss, 1, models.DashboardAcl{
294			DashboardID: 1, OrgID: users[0].OrgId, UserID: users[1].Id,
295			Permission: models.PERMISSION_EDIT,
296		})
297		require.Nil(t, err)
298
299		err = ss.SavePreferences(context.Background(), &models.SavePreferencesCommand{
300			UserId: users[1].Id, OrgId: users[0].OrgId, HomeDashboardId: 1, Theme: "dark",
301		})
302		require.Nil(t, err)
303
304		ss.CacheService.Flush()
305
306		query3 := &models.GetSignedInUserQuery{OrgId: users[1].OrgId, UserId: users[1].Id}
307		err = ss.GetSignedInUserWithCacheCtx(context.Background(), query3)
308		require.Nil(t, err)
309		require.NotNil(t, query3.Result)
310		require.Equal(t, query3.OrgId, users[1].OrgId)
311		err = SetUsingOrg(context.Background(), &models.SetUsingOrgCommand{UserId: users[1].Id, OrgId: users[0].OrgId})
312		require.Nil(t, err)
313		query4 := &models.GetSignedInUserQuery{OrgId: 0, UserId: users[1].Id}
314		err = ss.GetSignedInUserWithCacheCtx(context.Background(), query4)
315		require.Nil(t, err)
316		require.NotNil(t, query4.Result)
317		require.Equal(t, query4.Result.OrgId, users[0].OrgId)
318
319		cacheKey := newSignedInUserCacheKey(query4.Result.OrgId, query4.UserId)
320		_, found := ss.CacheService.Get(cacheKey)
321		require.True(t, found)
322
323		disableCmd := models.BatchDisableUsersCommand{
324			UserIds:    []int64{users[0].Id, users[1].Id, users[2].Id, users[3].Id, users[4].Id},
325			IsDisabled: true,
326		}
327
328		err = BatchDisableUsers(context.Background(), &disableCmd)
329		require.Nil(t, err)
330
331		isDisabled = true
332		query5 := &models.SearchUsersQuery{IsDisabled: &isDisabled}
333		err = SearchUsers(context.Background(), query5)
334
335		require.Nil(t, err)
336		require.EqualValues(t, query5.Result.TotalCount, 5)
337
338		// the user is deleted
339		err = DeleteUser(context.Background(), &models.DeleteUserCommand{UserId: users[1].Id})
340		require.Nil(t, err)
341
342		// delete connected org users and permissions
343		query2 := &models.GetOrgUsersQuery{OrgId: users[0].OrgId}
344		err = GetOrgUsersForTest(query2)
345		require.Nil(t, err)
346
347		require.Len(t, query2.Result, 1)
348
349		permQuery = &models.GetDashboardAclInfoListQuery{DashboardID: 1, OrgID: users[0].OrgId}
350		err = ss.GetDashboardAclInfoList(context.Background(), permQuery)
351		require.Nil(t, err)
352
353		require.Len(t, permQuery.Result, 0)
354
355		prefsQuery = &models.GetPreferencesQuery{OrgId: users[0].OrgId, UserId: users[1].Id}
356		err = ss.GetPreferences(context.Background(), prefsQuery)
357		require.Nil(t, err)
358
359		require.EqualValues(t, prefsQuery.Result.OrgId, 0)
360		require.EqualValues(t, prefsQuery.Result.UserId, 0)
361	})
362
363	ss = InitTestDB(t)
364
365	t.Run("Testing DB - enable all users", func(t *testing.T) {
366
367		users := createFiveTestUsers(t, ss, func(i int) *models.CreateUserCommand {
368			return &models.CreateUserCommand{
369				Email:      fmt.Sprint("user", i, "@test.com"),
370				Name:       fmt.Sprint("user", i),
371				Login:      fmt.Sprint("loginuser", i),
372				IsDisabled: true,
373			}
374		})
375
376		disableCmd := models.BatchDisableUsersCommand{
377			UserIds:    []int64{users[0].Id, users[1].Id, users[2].Id, users[3].Id, users[4].Id},
378			IsDisabled: false,
379		}
380
381		err := BatchDisableUsers(context.Background(), &disableCmd)
382		require.Nil(t, err)
383
384		isDisabled := false
385		query := &models.SearchUsersQuery{IsDisabled: &isDisabled}
386		err = SearchUsers(context.Background(), query)
387
388		require.Nil(t, err)
389		require.EqualValues(t, query.Result.TotalCount, 5)
390	})
391
392	ss = InitTestDB(t)
393
394	t.Run("Testing DB - disable only specific users", func(t *testing.T) {
395		users := createFiveTestUsers(t, ss, func(i int) *models.CreateUserCommand {
396			return &models.CreateUserCommand{
397				Email:      fmt.Sprint("user", i, "@test.com"),
398				Name:       fmt.Sprint("user", i),
399				Login:      fmt.Sprint("loginuser", i),
400				IsDisabled: false,
401			}
402		})
403
404		userIdsToDisable := []int64{}
405		for i := 0; i < 3; i++ {
406			userIdsToDisable = append(userIdsToDisable, users[i].Id)
407		}
408		disableCmd := models.BatchDisableUsersCommand{
409			UserIds:    userIdsToDisable,
410			IsDisabled: true,
411		}
412
413		err := BatchDisableUsers(context.Background(), &disableCmd)
414		require.Nil(t, err)
415
416		query := models.SearchUsersQuery{}
417		err = SearchUsers(context.Background(), &query)
418
419		require.Nil(t, err)
420		require.EqualValues(t, query.Result.TotalCount, 5)
421		for _, user := range query.Result.Users {
422			shouldBeDisabled := false
423
424			// Check if user id is in the userIdsToDisable list
425			for _, disabledUserId := range userIdsToDisable {
426				if user.Id == disabledUserId {
427					require.True(t, user.IsDisabled)
428					shouldBeDisabled = true
429				}
430			}
431
432			// Otherwise user shouldn't be disabled
433			if !shouldBeDisabled {
434				require.False(t, user.IsDisabled)
435			}
436		}
437	})
438
439	ss = InitTestDB(t)
440
441	t.Run("Testing DB - search users", func(t *testing.T) {
442		// Since previous tests were destructive
443		createFiveTestUsers(t, ss, func(i int) *models.CreateUserCommand {
444			return &models.CreateUserCommand{
445				Email:      fmt.Sprint("user", i, "@test.com"),
446				Name:       fmt.Sprint("user", i),
447				Login:      fmt.Sprint("loginuser", i),
448				IsDisabled: false,
449			}
450		})
451	})
452
453	t.Run("Testing DB - grafana admin users", func(t *testing.T) {
454
455		ss = InitTestDB(t)
456
457		createUserCmd := models.CreateUserCommand{
458			Email:   fmt.Sprint("admin", "@test.com"),
459			Name:    "admin",
460			Login:   "admin",
461			IsAdmin: true,
462		}
463		user, err := ss.CreateUser(context.Background(), createUserCmd)
464		require.Nil(t, err)
465
466		// Cannot make themselves a non-admin
467		updatePermsError := ss.UpdateUserPermissions(user.Id, false)
468
469		require.Equal(t, updatePermsError, models.ErrLastGrafanaAdmin)
470
471		query := models.GetUserByIdQuery{Id: user.Id}
472		getUserError := GetUserById(context.Background(), &query)
473		require.Nil(t, getUserError)
474
475		require.True(t, query.Result.IsAdmin)
476
477		// One user
478		const email = "user@test.com"
479		const username = "user"
480		createUserCmd = models.CreateUserCommand{
481			Email: email,
482			Name:  "user",
483			Login: username,
484		}
485		_, err = ss.CreateUser(context.Background(), createUserCmd)
486		require.Nil(t, err)
487
488		// When trying to create a new user with the same email, an error is returned
489		createUserCmd = models.CreateUserCommand{
490			Email:        email,
491			Name:         "user2",
492			Login:        "user2",
493			SkipOrgSetup: true,
494		}
495		_, err = ss.CreateUser(context.Background(), createUserCmd)
496		require.Equal(t, err, models.ErrUserAlreadyExists)
497
498		// When trying to create a new user with the same login, an error is returned
499		createUserCmd = models.CreateUserCommand{
500			Email:        "user2@test.com",
501			Name:         "user2",
502			Login:        username,
503			SkipOrgSetup: true,
504		}
505		_, err = ss.CreateUser(context.Background(), createUserCmd)
506		require.Equal(t, err, models.ErrUserAlreadyExists)
507	})
508}
509
510func GetOrgUsersForTest(query *models.GetOrgUsersQuery) error {
511	query.Result = make([]*models.OrgUserDTO, 0)
512	sess := x.Table("org_user")
513	sess.Join("LEFT ", x.Dialect().Quote("user"), fmt.Sprintf("org_user.user_id=%s.id", x.Dialect().Quote("user")))
514	sess.Where("org_user.org_id=?", query.OrgId)
515	sess.Cols("org_user.org_id", "org_user.user_id", "user.email", "user.login", "org_user.role")
516
517	err := sess.Find(&query.Result)
518	return err
519}
520
521func createFiveTestUsers(t *testing.T, sqlStore *SQLStore, fn func(i int) *models.CreateUserCommand) []models.User {
522	t.Helper()
523
524	users := []models.User{}
525	for i := 0; i < 5; i++ {
526		cmd := fn(i)
527
528		user, err := sqlStore.CreateUser(context.Background(), *cmd)
529		users = append(users, *user)
530
531		require.Nil(t, err)
532	}
533
534	return users
535}
536