func (e *SimpleExec) executeCreateUser(s *ast.CreateUserStmt) error {
users := make([]string, 0, len(s.Specs))
for _, spec := range s.Specs {
userName, host := parseUser(spec.User)
exists, err1 := userExists(e.ctx, userName, host)
if err1 != nil {
return errors.Trace(err1)
}
if exists {
if !s.IfNotExists {
return errors.New("Duplicate user")
}
continue
}
pwd := ""
if spec.AuthOpt != nil {
if spec.AuthOpt.ByAuthString {
pwd = util.EncodePassword(spec.AuthOpt.AuthString)
} else {
pwd = util.EncodePassword(spec.AuthOpt.HashString)
}
}
user := fmt.Sprintf(`("%s", "%s", "%s")`, host, userName, pwd)
users = append(users, user)
}
if len(users) == 0 {
return nil
}
sql := fmt.Sprintf(`INSERT INTO %s.%s (Host, User, Password) VALUES %s;`, mysql.SystemDB, mysql.UserTable, strings.Join(users, ", "))
_, err := e.ctx.(sqlexec.RestrictedSQLExecutor).ExecRestrictedSQL(e.ctx, sql)
if err != nil {
return errors.Trace(err)
}
return nil
}
func (s *testSuite) TestSetPwd(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
createUserSQL := `CREATE USER 'testpwd'@'localhost' IDENTIFIED BY '';`
tk.MustExec(createUserSQL)
result := tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr := fmt.Sprintf("%v", []byte(""))
result.Check(testkit.Rows(rowStr))
// set password for
tk.MustExec(`SET PASSWORD FOR 'testpwd'@'localhost' = 'password';`)
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("password")))
result.Check(testkit.Rows(rowStr))
// set password
setPwdSQL := `SET PASSWORD = '******'`
// Session user is empty.
_, err := tk.Exec(setPwdSQL)
c.Check(err, NotNil)
tk.Se, err = tidb.CreateSession(s.store)
c.Check(err, IsNil)
ctx := tk.Se.(context.Context)
ctx.GetSessionVars().User = "******"
// Session user doesn't exist.
_, err = tk.Exec(setPwdSQL)
c.Check(terror.ErrorEqual(err, executor.ErrPasswordNoMatch), IsTrue)
// normal
ctx.GetSessionVars().User = "******"
tk.MustExec(setPwdSQL)
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("pwd")))
result.Check(testkit.Rows(rowStr))
}
// Exec implements the stmt.Statement Exec interface.
func (s *CreateUserStmt) Exec(ctx context.Context) (rset.Recordset, error) {
users := make([]string, 0, len(s.Specs))
for _, spec := range s.Specs {
strs := strings.Split(spec.User, "@")
userName := strs[0]
host := strs[1]
exists, err1 := s.userExists(ctx, userName, host)
if err1 != nil {
return nil, errors.Trace(err1)
}
if exists {
if !s.IfNotExists {
return nil, errors.Errorf("Duplicate user")
}
continue
}
pwd := ""
if spec.AuthOpt.ByAuthString {
pwd = util.EncodePassword(spec.AuthOpt.AuthString)
} else {
pwd = util.EncodePassword(spec.AuthOpt.HashString)
}
user := fmt.Sprintf(`("%s", "%s", "%s")`, host, userName, pwd)
users = append(users, user)
}
if len(users) == 0 {
return nil, nil
}
sql := fmt.Sprintf(`INSERT INTO %s.%s (Host, User, Password) VALUES %s;`, mysql.SystemDB, mysql.UserTable, strings.Join(users, ", "))
_, err := ctx.(sqlexec.RestrictedSQLExecutor).ExecRestrictedSQL(ctx, sql)
if err != nil {
return nil, errors.Trace(err)
}
return nil, nil
}
func (s *testSuite) TestCreateUser(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
// Make sure user test not in mysql.User.
result := tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
result.Check(nil)
// Create user test.
createUserSQL := `CREATE USER 'test'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
// Make sure user test in mysql.User.
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr := fmt.Sprintf("%v", []byte(util.EncodePassword("123")))
result.Check(testkit.Rows(rowStr))
// Create duplicate user with IfNotExists will be success.
createUserSQL = `CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
// Create duplicate user without IfNotExists will cause error.
createUserSQL = `CREATE USER 'test'@'localhost' IDENTIFIED BY '123';`
_, err := tk.Exec(createUserSQL)
c.Check(err, NotNil)
dropUserSQL := `DROP USER IF EXISTS 'test'@'localhost' ;`
tk.MustExec(dropUserSQL)
// Create user test.
createUserSQL = `CREATE USER 'test1'@'localhost';`
tk.MustExec(createUserSQL)
// Make sure user test in mysql.User.
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("")))
result.Check(testkit.Rows(rowStr))
dropUserSQL = `DROP USER IF EXISTS 'test1'@'localhost' ;`
tk.MustExec(dropUserSQL)
// Test drop user if exists.
createUserSQL = `CREATE USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(createUserSQL)
dropUserSQL = `DROP USER IF EXISTS 'test1'@'localhost', 'test2'@'localhost', 'test3'@'localhost' ;`
tk.MustExec(dropUserSQL)
// Test negative cases without IF EXISTS.
createUserSQL = `CREATE USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(createUserSQL)
dropUserSQL = `DROP USER 'test1'@'localhost', 'test2'@'localhost', 'test3'@'localhost';`
_, err = tk.Exec(dropUserSQL)
c.Check(err, NotNil)
dropUserSQL = `DROP USER 'test3'@'localhost';`
_, err = tk.Exec(dropUserSQL)
c.Check(err, NotNil)
dropUserSQL = `DROP USER 'test1'@'localhost';`
_, err = tk.Exec(dropUserSQL)
c.Check(err, NotNil)
// Test positive cases without IF EXISTS.
createUserSQL = `CREATE USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(createUserSQL)
dropUserSQL = `DROP USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(dropUserSQL)
}
func (e *SimpleExec) executeAlterUser(s *ast.AlterUserStmt) error {
if s.CurrentAuth != nil {
user := e.ctx.GetSessionVars().User
if len(user) == 0 {
return errors.New("Session user is empty")
}
spec := &ast.UserSpec{
User: user,
AuthOpt: s.CurrentAuth,
}
s.Specs = []*ast.UserSpec{spec}
}
failedUsers := make([]string, 0, len(s.Specs))
for _, spec := range s.Specs {
userName, host := parseUser(spec.User)
exists, err := userExists(e.ctx, userName, host)
if err != nil {
return errors.Trace(err)
}
if !exists {
failedUsers = append(failedUsers, spec.User)
if s.IfExists {
// TODO: Make this error as a warning.
}
continue
}
pwd := ""
if spec.AuthOpt != nil {
if spec.AuthOpt.ByAuthString {
pwd = util.EncodePassword(spec.AuthOpt.AuthString)
} else {
pwd = util.EncodePassword(spec.AuthOpt.HashString)
}
}
sql := fmt.Sprintf(`UPDATE %s.%s SET Password = "******" WHERE Host = "%s" and User = "******";`,
mysql.SystemDB, mysql.UserTable, pwd, host, userName)
_, err = e.ctx.(sqlexec.RestrictedSQLExecutor).ExecRestrictedSQL(e.ctx, sql)
if err != nil {
failedUsers = append(failedUsers, spec.User)
}
}
err := e.ctx.CommitTxn()
if err != nil {
return errors.Trace(err)
}
if len(failedUsers) > 0 {
errMsg := "Operation ALTER USER failed for " + strings.Join(failedUsers, ",")
return terror.ClassExecutor.New(CodeCannotUser, errMsg)
}
return nil
}
func (s *testStmtSuite) TestSetPwdStmt(c *C) {
createUserSQL := `CREATE USER 'testpwd'@'localhost' IDENTIFIED BY '';`
tx := mustBegin(c, s.testDB)
_, err := tx.Query(createUserSQL)
c.Assert(err, NotNil)
mustCommit(c, tx)
tx = mustBegin(c, s.testDB)
rows, err := tx.Query(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
c.Assert(err, IsNil)
rows.Next()
var pwd string
rows.Scan(&pwd)
c.Assert(pwd, Equals, "")
c.Assert(rows.Next(), IsFalse)
rows.Close()
mustCommit(c, tx)
tx = mustBegin(c, s.testDB)
tx.Query(`SET PASSWORD FOR 'testpwd'@'localhost' = 'password';`)
mustCommit(c, tx)
tx = mustBegin(c, s.testDB)
rows, err = tx.Query(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
c.Assert(err, IsNil)
rows.Next()
rows.Scan(&pwd)
c.Assert(pwd, Equals, util.EncodePassword("password"))
c.Assert(rows.Next(), IsFalse)
rows.Close()
mustCommit(c, tx)
}
func (s *testStmtSuite) TestCreateUserStmt(c *C) {
// Make sure user test not in mysql.User.
tx := mustBegin(c, s.testDB)
rows, err := tx.Query(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
c.Assert(err, IsNil)
c.Assert(rows.Next(), IsFalse)
rows.Close()
mustCommit(c, tx)
// Create user test.
createUserSQL := `CREATE USER 'test'@'localhost' IDENTIFIED BY '123';`
mustExec(c, s.testDB, createUserSQL)
// Make sure user test in mysql.User.
tx = mustBegin(c, s.testDB)
rows, err = tx.Query(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
c.Assert(err, IsNil)
rows.Next()
var pwd string
rows.Scan(&pwd)
c.Assert(pwd, Equals, util.EncodePassword("123"))
c.Assert(rows.Next(), IsFalse)
rows.Close()
mustCommit(c, tx)
// Create duplicate user with IfNotExists will be success.
createUserSQL = `CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY '123';`
mustExec(c, s.testDB, createUserSQL)
// Create duplicate user without IfNotExists will cause error.
createUserSQL = `CREATE USER 'test'@'localhost' IDENTIFIED BY '123';`
tx = mustBegin(c, s.testDB)
_, err = tx.Query(createUserSQL)
c.Assert(err, NotNil)
}
func (s *testStmtSuite) TestSetPwdStmt(c *C) {
tx := mustBegin(c, s.testDB)
tx.Query(`INSERT INTO mysql.User VALUES ("localhost", "root", "", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y"), ("127.0.0.1", "root", "", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y")`)
rows, err := tx.Query(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
c.Assert(err, IsNil)
rows.Next()
var pwd string
rows.Scan(&pwd)
c.Assert(pwd, Equals, "")
c.Assert(rows.Next(), IsFalse)
rows.Close()
mustCommit(c, tx)
tx = mustBegin(c, s.testDB)
tx.Query(`SET PASSWORD FOR 'root'@'localhost' = 'password';`)
mustCommit(c, tx)
tx = mustBegin(c, s.testDB)
rows, err = tx.Query(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
c.Assert(err, IsNil)
rows.Next()
rows.Scan(&pwd)
c.Assert(pwd, Equals, util.EncodePassword("password"))
c.Assert(rows.Next(), IsFalse)
rows.Close()
mustCommit(c, tx)
}
// Exec implements the stmt.Statement Exec interface.
func (s *CreateUserStmt) Exec(ctx context.Context) (rset.Recordset, error) {
st := &InsertIntoStmt{
TableIdent: table.Ident{
Name: model.NewCIStr(mysql.UserTable),
Schema: model.NewCIStr(mysql.SystemDB),
},
ColNames: []string{"Host", "User", "Password"},
}
values := make([][]expression.Expression, 0, len(s.Specs))
for _, spec := range s.Specs {
strs := strings.Split(spec.User, "@")
userName := strs[0]
host := strs[1]
exists, err1 := s.userExists(ctx, userName, host)
if err1 != nil {
return nil, errors.Trace(err1)
}
if exists {
if !s.IfNotExists {
return nil, errors.Errorf("Duplicate user")
}
continue
}
value := make([]expression.Expression, 0, 3)
value = append(value, expression.Value{Val: host})
value = append(value, expression.Value{Val: userName})
if spec.AuthOpt.ByAuthString {
value = append(value, expression.Value{Val: util.EncodePassword(spec.AuthOpt.AuthString)})
} else {
// TODO: Maybe we should hash the string here?
value = append(value, expression.Value{Val: util.EncodePassword(spec.AuthOpt.HashString)})
}
values = append(values, value)
}
if len(values) == 0 {
return nil, nil
}
st.Lists = values
_, err := st.Exec(ctx)
if err != nil {
return nil, errors.Trace(err)
}
return nil, nil
}
func (s *testSuite) TestSetPwd(c *C) {
tk := testkit.NewTestKit(c, s.store)
createUserSQL := `CREATE USER 'testpwd'@'localhost' IDENTIFIED BY '';`
tk.MustExec(createUserSQL)
result := tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr := fmt.Sprintf("%v", []byte(""))
result.Check(testkit.Rows(rowStr))
tk.MustExec(`SET PASSWORD FOR 'testpwd'@'localhost' = 'password';`)
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("password")))
result.Check(testkit.Rows(rowStr))
}
// Exec implements the stmt.Statement Exec interface.
func (s *SetPwdStmt) Exec(ctx context.Context) (_ rset.Recordset, err error) {
// If len(s.User) == 0, use CURRENT_USER()
strs := strings.Split(s.User, "@")
userName := strs[0]
host := strs[1]
// Update mysql.user
asgn := expression.Assignment{
ColName: "Password",
Expr: expression.Value{Val: util.EncodePassword(s.Password)},
}
st := &UpdateStmt{
TableRefs: composeUserTableRset(),
List: []expression.Assignment{asgn},
Where: composeUserTableFilter(userName, host),
}
return st.Exec(ctx)
}
func (s *testSuite) TestCreateUser(c *C) {
tk := testkit.NewTestKit(c, s.store)
// Make sure user test not in mysql.User.
result := tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
result.Check(nil)
// Create user test.
createUserSQL := `CREATE USER 'test'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
// Make sure user test in mysql.User.
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr := fmt.Sprintf("%v", []byte(util.EncodePassword("123")))
result.Check(testkit.Rows(rowStr))
// Create duplicate user with IfNotExists will be success.
createUserSQL = `CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
// Create duplicate user without IfNotExists will cause error.
createUserSQL = `CREATE USER 'test'@'localhost' IDENTIFIED BY '123';`
_, err := tk.Exec(createUserSQL)
c.Check(err, NotNil)
}
func (e *SimpleExec) executeSetPwd(s *ast.SetPwdStmt) error {
if len(s.User) == 0 {
vars := e.ctx.GetSessionVars()
s.User = vars.User
if len(s.User) == 0 {
return errors.New("Session error is empty")
}
}
userName, host := parseUser(s.User)
exists, err := userExists(e.ctx, userName, host)
if err != nil {
return errors.Trace(err)
}
if !exists {
return errors.Trace(ErrPasswordNoMatch)
}
// update mysql.user
sql := fmt.Sprintf(`UPDATE %s.%s SET password="******" WHERE User="******" AND Host="%s";`, mysql.SystemDB, mysql.UserTable, util.EncodePassword(s.Password), userName, host)
_, err = e.ctx.(sqlexec.RestrictedSQLExecutor).ExecRestrictedSQL(e.ctx, sql)
return errors.Trace(err)
}
func (e *SimpleExec) executeSetPwd(s *ast.SetPwdStmt) error {
// TODO: If len(s.User) == 0, use CURRENT_USER()
userName, host := parseUser(s.User)
// Update mysql.user
sql := fmt.Sprintf(`UPDATE %s.%s SET password="******" WHERE User="******" AND Host="%s";`, mysql.SystemDB, mysql.UserTable, util.EncodePassword(s.Password), userName, host)
_, err := e.ctx.(sqlexec.RestrictedSQLExecutor).ExecRestrictedSQL(e.ctx, sql)
return errors.Trace(err)
}
// Exec implements the stmt.Statement Exec interface.
func (s *SetPwdStmt) Exec(ctx context.Context) (rset.Recordset, error) {
// TODO: If len(s.User) == 0, use CURRENT_USER()
userName, host := parseUser(s.User)
// Update mysql.user
sql := fmt.Sprintf(`UPDATE %s.%s SET password="******" WHERE User="******" AND Host="%s";`, mysql.SystemDB, mysql.UserTable, util.EncodePassword(s.Password), userName, host)
_, err := ctx.(sqlexec.RestrictedSQLExecutor).ExecRestrictedSQL(ctx, sql)
return nil, errors.Trace(err)
}
func (s *testSuite) TestUser(c *C) {
defer testleak.AfterTest(c)()
tk := testkit.NewTestKit(c, s.store)
// Make sure user test not in mysql.User.
result := tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
result.Check(nil)
// Create user test.
createUserSQL := `CREATE USER 'test'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
// Make sure user test in mysql.User.
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr := fmt.Sprintf("%v", []byte(util.EncodePassword("123")))
result.Check(testkit.Rows(rowStr))
// Create duplicate user with IfNotExists will be success.
createUserSQL = `CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
// Create duplicate user without IfNotExists will cause error.
createUserSQL = `CREATE USER 'test'@'localhost' IDENTIFIED BY '123';`
_, err := tk.Exec(createUserSQL)
c.Check(err, NotNil)
dropUserSQL := `DROP USER IF EXISTS 'test'@'localhost' ;`
tk.MustExec(dropUserSQL)
// Create user test.
createUserSQL = `CREATE USER 'test1'@'localhost';`
tk.MustExec(createUserSQL)
// Make sure user test in mysql.User.
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("")))
result.Check(testkit.Rows(rowStr))
dropUserSQL = `DROP USER IF EXISTS 'test1'@'localhost' ;`
tk.MustExec(dropUserSQL)
// Test alter user.
createUserSQL = `CREATE USER 'test1'@'localhost' IDENTIFIED BY '123', 'test2'@'localhost' IDENTIFIED BY '123', 'test3'@'localhost' IDENTIFIED BY '123';`
tk.MustExec(createUserSQL)
alterUserSQL := `ALTER USER 'test1'@'localhost' IDENTIFIED BY '111';`
tk.MustExec(alterUserSQL)
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("111")))
result.Check(testkit.Rows(rowStr))
alterUserSQL = `ALTER USER IF EXISTS 'test2'@'localhost' IDENTIFIED BY '222', 'test_not_exist'@'localhost' IDENTIFIED BY '1';`
_, err = tk.Exec(alterUserSQL)
c.Check(err, NotNil)
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("222")))
result.Check(testkit.Rows(rowStr))
alterUserSQL = `ALTER USER IF EXISTS'test_not_exist'@'localhost' IDENTIFIED BY '1', 'test3'@'localhost' IDENTIFIED BY '333';`
_, err = tk.Exec(alterUserSQL)
c.Check(err, NotNil)
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("333")))
result.Check(testkit.Rows(rowStr))
// Test alter user user().
alterUserSQL = `ALTER USER USER() IDENTIFIED BY '1';`
_, err = tk.Exec(alterUserSQL)
c.Check(err, NotNil)
tk.Se, err = tidb.CreateSession(s.store)
c.Check(err, IsNil)
ctx := tk.Se.(context.Context)
ctx.GetSessionVars().User = "******"
tk.MustExec(alterUserSQL)
result = tk.MustQuery(`SELECT Password FROM mysql.User WHERE User="******" and Host="localhost"`)
rowStr = fmt.Sprintf("%v", []byte(util.EncodePassword("1")))
result.Check(testkit.Rows(rowStr))
dropUserSQL = `DROP USER 'test1'@'localhost', 'test2'@'localhost', 'test3'@'localhost';`
tk.MustExec(dropUserSQL)
// Test drop user if exists.
createUserSQL = `CREATE USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(createUserSQL)
dropUserSQL = `DROP USER IF EXISTS 'test1'@'localhost', 'test2'@'localhost', 'test3'@'localhost' ;`
tk.MustExec(dropUserSQL)
// Test negative cases without IF EXISTS.
createUserSQL = `CREATE USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(createUserSQL)
dropUserSQL = `DROP USER 'test1'@'localhost', 'test2'@'localhost', 'test3'@'localhost';`
_, err = tk.Exec(dropUserSQL)
c.Check(err, NotNil)
dropUserSQL = `DROP USER 'test3'@'localhost';`
_, err = tk.Exec(dropUserSQL)
c.Check(err, NotNil)
dropUserSQL = `DROP USER 'test1'@'localhost';`
_, err = tk.Exec(dropUserSQL)
c.Check(err, NotNil)
// Test positive cases without IF EXISTS.
createUserSQL = `CREATE USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(createUserSQL)
dropUserSQL = `DROP USER 'test1'@'localhost', 'test3'@'localhost';`
tk.MustExec(dropUserSQL)
}