none
Создание нескольких пользователей в MySQL RRS feed

  • Вопрос

  • Добрый день. Мне необходимо сделать авторизацию пользователей при входе в программу, разработанную на C#. Погуглив нашел что лучше и безопаснее использовать пользователей MySQl с определёнными привилегиями.
    План работы такой появляется форма, где нужно ввести логин  и пароль.
    1) Если всё верно, то входим в систему и открывается главная форма, а предыдущая закрывается.
    2) Если не верно, то вывод сведений об ошибке
    Нужно создать 2 группы пользователей в mysql: admin (может делать всё с бд.) и user (только просмотр и печать). Как это сделать?
    Администратор бд должен иметь возможность добавления других пользователей в группы admin и user с их логинами и паролями!!!
    27 апреля 2012 г. 13:07

Ответы

  • Вот собственно базовый шаблон, того что вы хотите. Заранее отмечу, что это не полное решение, а маленькая часть того функционала, доработать который нужно много времени. Вот схема БД.

    -- Скрипт сгенерирован Devart dbForge Studio for MySQL, Версия 5.0.48.1
    -- Домашняя страница продукта: http://www.devart.com/ru/dbforge/mysql/studio
    -- Дата скрипта: 28.04.2012 15:22:44
    -- Версия сервера: 5.5.9
    -- Версия клиента: 4.1
    
    CREATE DATABASE IF NOT EXISTS myroledb
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;
    
    USE myroledb;
    
    CREATE TABLE IF NOT EXISTS roles(
      RoleId INT(11) NOT NULL,
      RoleName VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      PRIMARY KEY (RoleId)
    )
    ENGINE = INNODB
    AVG_ROW_LENGTH = 8192
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;
    
    CREATE TABLE IF NOT EXISTS users(
      UserId INT(11) NOT NULL,
      UserName VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      UserPass VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      PRIMARY KEY (UserId)
    )
    ENGINE = INNODB
    AVG_ROW_LENGTH = 16384
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;
    
    CREATE TABLE IF NOT EXISTS usersinroles(
      UserId INT(11) NOT NULL,
      RoleId INT(11) NOT NULL,
      PRIMARY KEY (RoleId, UserId),
      INDEX FK_usersinroles_users_UserId (UserId),
      CONSTRAINT FK_usersinroles_roles_RoleId FOREIGN KEY (RoleId)
      REFERENCES roles (RoleId) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT FK_usersinroles_users_UserId FOREIGN KEY (UserId)
      REFERENCES users (UserId) ON DELETE RESTRICT ON UPDATE RESTRICT
    )
    ENGINE = INNODB
    AVG_ROW_LENGTH = 16384
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;
    
    DELIMITER $$
    
    CREATE DEFINER = 'root'@'localhost'
    FUNCTION ValidateUser(nameParam VARCHAR(255),
                          passParam VARCHAR(255)
                          )
    RETURNS TINYINT(1)
    READS SQL DATA
    BEGIN
      DECLARE upass VARCHAR
      (255);
      DECLARE uname VARCHAR
      (255);
    
      SELECT UserName
      INTO
        uname
      FROM
        myroledb.users
      WHERE
        UserName = nameParam;
      IF uname = NULL THEN
        RETURN FALSE;
      END IF;
      SELECT UserPass
      INTO
        upass
      FROM
        myroledb.users
      WHERE
        UserName = uname;
      IF upass = passParam THEN
        RETURN TRUE;
      END IF;
      RETURN FALSE;
    END
    $$
    
    DELIMITER ;

    Тут я успел создать только оду хранимую процедуру, для ваших нужд их надо создать несколько, каждому своя задача. И нужно определить только одного пользователя, с привелегиями только на выполнение определённой процедуры, как показано ниже.

    А вот код который будет работать, со всем этим.

    using MySql.Data.MySqlClient;
    
    namespace MyRoleApp
    {
      public partial class Form1 : Form
      {
        public Form1()
        {
          InitializeComponent();
        }
    
        private void loginButton_Click(object sender, EventArgs e)
        {
          if (UserProvider.ValidateUser(nameTextBox.Text, passTextBox.Text))
            MessageBox.Show("Есть такой пользователь.");
          else
            MessageBox.Show("Нет такого пользователя.");
        }
      }
      public static class UserProvider
      {
        //Пользователь у которого одна толька привилегия, возможность выполнить функцию ValidateUser
        private static string connectionString = "server=localhost;User Id=myroledbreader;Password=1234;database=myroledb";
        //private static string connectionString = "server=localhost;User Id=root;Password=solo;database=myroledb";
        private static MySqlConnection connection;
        public static bool ValidateUser(string name, string password)
        {
          string commandText = "ValidateUser";
          bool loginValid;
          using (connection = new MySqlConnection(connectionString))
          {
            MySqlCommand command = new MySqlCommand(commandText, connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@nameParam", MySqlDbType.VarChar);
            command.Parameters.Add("@passParam", MySqlDbType.VarChar);
            command.Parameters["@nameParam"].Value = name;
            command.Parameters["@passParam"].Value = password;
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@ireturnvalue", MySqlDbType.Int16);
            command.Parameters["@ireturnvalue"].Direction = ParameterDirection.ReturnValue;
            connection.Open();
            command.ExecuteScalar();
            loginValid = Convert.ToBoolean(command.Parameters["@ireturnvalue"].Value);
          }
          if (loginValid)
            return true;
          return false;
        }
        /*Надо будет все эти методы реализовать*/
        public static void CreateUser(string name, string password) { }
        public static void UpdateUserRole(string userName, string userRole) { }
        public static bool UserIsAdmin(string userName) { return false; }
        public static IMyRole GetUserRole() { return new MyRole(); }
        public static void UpdateUserPassword(string newPassword) { }
      }
      public interface  IMyUser
      {
        string UserName { get; }
        IMyRole[] Roles { get; }
      }
      public interface  IMyRole
      {
        string RoleName { get; }
        IMyUser[] Users { get; }
      }
      public class MyUser : IMyUser
      {
        #region IMyUser Members
    
        public string UserName
        {
          get { throw new NotImplementedException(); }
        }
    
        public IMyRole[] Roles
        {
          get { throw new NotImplementedException(); }
        }
    
        #endregion
      }
      public class MyRole : IMyRole
      {
        #region IMyRole Members
    
        public string RoleName
        {
          get { throw new NotImplementedException(); }
        }
    
        public IMyUser[] Users
        {
          get { throw new NotImplementedException(); }
        }
    
        #endregion
      }
    }

    И опять повторюсь, что это только пища для размышления, т.е. в какую сторону копать, а реализовать это задача ресурсоёмкая.


    • Изменено YatajgaModerator 28 апреля 2012 г. 11:34
    • Помечено в качестве ответа vantur 2 мая 2012 г. 16:48
    28 апреля 2012 г. 11:34
    Модератор

Все ответы

  • Добрый день.

    Хм,  а не подскажите, почему ваш выбор остановился именно на MySQL? Почему не бесплатный MS SQL Server Express?

    Собственно по теме.

    1. Почитать про управление пользователями в MySQL можно здесь.

    2. Компоненты для подключения можно скачать здесь.

    3. Приподключении, по всей видимости, логин и пароль пользователя вам ридеться передавать в виде парметров ConnectionString. Про строки подключения в общем виде можете почитать здесь.

    Но если очень важных факторов требующих применения MySQL нет, то я бы рекомендовал MS SQL Servers.

    27 апреля 2012 г. 18:31
    Отвечающий
  • Спс. Но мне не совсем понятно  управление пользователями в MySQL  здесь. Можно пример для моего случая...
    Как возможно это сделать в MySQL Workbench 5.2?
    27 апреля 2012 г. 18:58
  • Видите ли в чем дело, на данном форуме, насколько я понимаю, в основном помогают специалисты использующие пул продуктов Microsoft. Для меня чем отличается язык SQL в MySQL от языка SQL в MySQL Workbench 5.2 тайна за семью печатями. Но у меня есть подозрение, что ничем. Поэтому открываете ссылку и читаете какие есть SQL команды для создания пользователей, групп, как назначить им права на доступ к базам данных и таблицам. По ссылке все это вроде есть. Но т.к. у меня не установлена ни одна версия MySQL а искать в Internet сервер для экспериментов будет долго и не интересно, то увы и ах, пример для MySQL-я я вам точно не покажу. Может кто из других форумчан?

    Если вы все таки определитесь, что в ваших задачах MySQL это не обязательное требование и можно воспользоваться бесплатным  MS SQL Server Express, то как заводить пользователей и роли в этой СУБД, я могу вам с удовольствием показать. Причем, как на примере SQL запросов, так и на примере действий через пользовательский интерфейс.

    28 апреля 2012 г. 3:39
    Отвечающий
  • Так, свободное время появилось, так что постараюсь помочь. "Но мне не совсем понятно  управление пользователями в MySQL" - если кратко, то все данные о пользователях хранятся исключительно в таблицах, то есть управляя данными этих таблиц, Вы по сути управляете пользователями. Самые главные из них находятся в базе mysql, это user - данные пользователей и db - данные о базах и правах пользователей на них. А далее я постараюсь привести пример.
    • Предложено в качестве ответа Abolmasov Dmitry 28 апреля 2012 г. 7:11
    28 апреля 2012 г. 5:38
    Модератор
  • Вот собственно базовый шаблон, того что вы хотите. Заранее отмечу, что это не полное решение, а маленькая часть того функционала, доработать который нужно много времени. Вот схема БД.

    -- Скрипт сгенерирован Devart dbForge Studio for MySQL, Версия 5.0.48.1
    -- Домашняя страница продукта: http://www.devart.com/ru/dbforge/mysql/studio
    -- Дата скрипта: 28.04.2012 15:22:44
    -- Версия сервера: 5.5.9
    -- Версия клиента: 4.1
    
    CREATE DATABASE IF NOT EXISTS myroledb
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;
    
    USE myroledb;
    
    CREATE TABLE IF NOT EXISTS roles(
      RoleId INT(11) NOT NULL,
      RoleName VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      PRIMARY KEY (RoleId)
    )
    ENGINE = INNODB
    AVG_ROW_LENGTH = 8192
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;
    
    CREATE TABLE IF NOT EXISTS users(
      UserId INT(11) NOT NULL,
      UserName VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      UserPass VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      PRIMARY KEY (UserId)
    )
    ENGINE = INNODB
    AVG_ROW_LENGTH = 16384
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;
    
    CREATE TABLE IF NOT EXISTS usersinroles(
      UserId INT(11) NOT NULL,
      RoleId INT(11) NOT NULL,
      PRIMARY KEY (RoleId, UserId),
      INDEX FK_usersinroles_users_UserId (UserId),
      CONSTRAINT FK_usersinroles_roles_RoleId FOREIGN KEY (RoleId)
      REFERENCES roles (RoleId) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT FK_usersinroles_users_UserId FOREIGN KEY (UserId)
      REFERENCES users (UserId) ON DELETE RESTRICT ON UPDATE RESTRICT
    )
    ENGINE = INNODB
    AVG_ROW_LENGTH = 16384
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;
    
    DELIMITER $$
    
    CREATE DEFINER = 'root'@'localhost'
    FUNCTION ValidateUser(nameParam VARCHAR(255),
                          passParam VARCHAR(255)
                          )
    RETURNS TINYINT(1)
    READS SQL DATA
    BEGIN
      DECLARE upass VARCHAR
      (255);
      DECLARE uname VARCHAR
      (255);
    
      SELECT UserName
      INTO
        uname
      FROM
        myroledb.users
      WHERE
        UserName = nameParam;
      IF uname = NULL THEN
        RETURN FALSE;
      END IF;
      SELECT UserPass
      INTO
        upass
      FROM
        myroledb.users
      WHERE
        UserName = uname;
      IF upass = passParam THEN
        RETURN TRUE;
      END IF;
      RETURN FALSE;
    END
    $$
    
    DELIMITER ;

    Тут я успел создать только оду хранимую процедуру, для ваших нужд их надо создать несколько, каждому своя задача. И нужно определить только одного пользователя, с привелегиями только на выполнение определённой процедуры, как показано ниже.

    А вот код который будет работать, со всем этим.

    using MySql.Data.MySqlClient;
    
    namespace MyRoleApp
    {
      public partial class Form1 : Form
      {
        public Form1()
        {
          InitializeComponent();
        }
    
        private void loginButton_Click(object sender, EventArgs e)
        {
          if (UserProvider.ValidateUser(nameTextBox.Text, passTextBox.Text))
            MessageBox.Show("Есть такой пользователь.");
          else
            MessageBox.Show("Нет такого пользователя.");
        }
      }
      public static class UserProvider
      {
        //Пользователь у которого одна толька привилегия, возможность выполнить функцию ValidateUser
        private static string connectionString = "server=localhost;User Id=myroledbreader;Password=1234;database=myroledb";
        //private static string connectionString = "server=localhost;User Id=root;Password=solo;database=myroledb";
        private static MySqlConnection connection;
        public static bool ValidateUser(string name, string password)
        {
          string commandText = "ValidateUser";
          bool loginValid;
          using (connection = new MySqlConnection(connectionString))
          {
            MySqlCommand command = new MySqlCommand(commandText, connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@nameParam", MySqlDbType.VarChar);
            command.Parameters.Add("@passParam", MySqlDbType.VarChar);
            command.Parameters["@nameParam"].Value = name;
            command.Parameters["@passParam"].Value = password;
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@ireturnvalue", MySqlDbType.Int16);
            command.Parameters["@ireturnvalue"].Direction = ParameterDirection.ReturnValue;
            connection.Open();
            command.ExecuteScalar();
            loginValid = Convert.ToBoolean(command.Parameters["@ireturnvalue"].Value);
          }
          if (loginValid)
            return true;
          return false;
        }
        /*Надо будет все эти методы реализовать*/
        public static void CreateUser(string name, string password) { }
        public static void UpdateUserRole(string userName, string userRole) { }
        public static bool UserIsAdmin(string userName) { return false; }
        public static IMyRole GetUserRole() { return new MyRole(); }
        public static void UpdateUserPassword(string newPassword) { }
      }
      public interface  IMyUser
      {
        string UserName { get; }
        IMyRole[] Roles { get; }
      }
      public interface  IMyRole
      {
        string RoleName { get; }
        IMyUser[] Users { get; }
      }
      public class MyUser : IMyUser
      {
        #region IMyUser Members
    
        public string UserName
        {
          get { throw new NotImplementedException(); }
        }
    
        public IMyRole[] Roles
        {
          get { throw new NotImplementedException(); }
        }
    
        #endregion
      }
      public class MyRole : IMyRole
      {
        #region IMyRole Members
    
        public string RoleName
        {
          get { throw new NotImplementedException(); }
        }
    
        public IMyUser[] Users
        {
          get { throw new NotImplementedException(); }
        }
    
        #endregion
      }
    }

    И опять повторюсь, что это только пища для размышления, т.е. в какую сторону копать, а реализовать это задача ресурсоёмкая.


    • Изменено YatajgaModerator 28 апреля 2012 г. 11:34
    • Помечено в качестве ответа vantur 2 мая 2012 г. 16:48
    28 апреля 2012 г. 11:34
    Модератор
  • Спасибо огромное - буду разбираться. А нет ли более простого метода реализации поставленной задачи?
    Что-то вроде этого:
    есть админ бд, который через .net насоздавал пользователей и присвоил им привилегии (admin или user) - все эти данные хранятся в каком-то виде в бд. 
    При запуске приложения вводим логин и пароль и в зависимости от назначенных привилегий входим или как admin, или как user...
    28 апреля 2012 г. 18:26
  • Ну так это и есть самое простое, основное. Правда методы которые Вам не нужны можете убрать, это я сделал как бы с прицелом на будущее, но схему базы я всё таки рекомендую. И самое главное делайте проверку через хранимые прцедуры, на севере, это намного безопаснее. И не только, всё что возможно через хранимые процедуры и минимум привилегий, только самое необходимое. Ну через процедуры я только имел ввиду, то что связано с безопасностью, а применять их повсеместно не нужно, иногда это не оптимально.
    28 апреля 2012 г. 18:57
    Модератор
  • А если использовать grant или create user, что-то вроде Grant?
    28 апреля 2012 г. 19:50
  • Что обозначают эти строки (как создали разные User Id):

     //Пользователь у которого одна только привилегия, возможность выполнить функцию ValidateUserprivate
    static string connectionString = "server=localhost;User Id=myroledbreader;Password=1234;database=myroledb";
    //private static string connectionString = "server=localhost;User Id=root;Password=solo;database=myroledb";

    В коде приведён пример проверки есть ли такой пользователь. А как проверить под какой ролью он заходит в бд? Не совсем понятно как используется IMyRole ... Т.е. я создал 2 роли: admin и user. Нужно после проверки того что такой пользователь есть вывести сообщение под какой ролью он вошёл...
    И как назначать для созданной роли привилегии: admin-может делать всё, a user- только просмотр?



    • Изменено vantur 29 апреля 2012 г. 7:33
    29 апреля 2012 г. 7:30
  • СПС. Разобрался и к выше изложенному добавил md5 шифрование. Тема закрыта.
  • Спасибо, что не забыли отметить ответ. Возникнут вопросы - задавайте еще.


    Для связи [mail]