Configure the MySQL account associate to the domain user via MySQL Windows Authentication Plugin

最新推荐文章于 2025-09-08 20:55:49 发布

weixin_30646315

最新推荐文章于 2025-09-08 20:55:49 发布

阅读量85

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/daly/p/4682669.html

本文介绍如何使用MySQL的Windows身份验证插件将Windows用户映射为MySQL用户的方法。包括安装插件、创建代理账户、授予权限等步骤，并验证配置是否生效。

在此记录如何将之前一次做第三发软件在配置的过程。将AD user通过代理映射到mysql 用户。

在Mysql官网有这样一段话：

The server-side Windows authentication plugin is included only in commercial distributions. It is not included in MySQL community distributions. The client-side plugin is included in all distributions, including community distributions. The Windows authentication plugin should work on Windows 2000 Professional and up. It requires MySQL Server 5.5.16 or newer.

这个只在商业版上有这种Windows authentication plugin

On the server side:

1. Install the Windows Authentication Plugin:

Method 1). Put these lines in my.ini(C:\Program Files\MySQL\MySQL Server5.5) file:

[mysqld]

plugin-load=authentication_windows.dll

Then restart MySQL service.

Method 2). Launch MySQL server as root user and run the following commands:

mysql>install plugin authentication_windows_server soname 'authentication_windows.dll';

mysql>show plugins;

Installed the Windows Authentication Plugin successfully if you see the following information.

2. Using the Windows Authentication Plugin to create user map to domain groups or users:

1). Create the proxy MySQL account "win_proxy" for Windows users(xxx.test\\administrator, xxx.test\\dzuser2) to connect to, and configure this account so that users and groups map to the appropriate MySQL accounts("mysql_admin", "mysql_user"):

c:\>mysql --user=root --password=password

mysql>create user win_proxy identified with authentication_windows as 'xxx.test\\administrator=mysql_admin, xxx.test\\dzuser2=mysql_user';

2). For proxying to work, the proxied accounts must exist, so create them and grant some privileges to them:

mysql>create user mysql_admin identified by 'password';

mysql>create user mysql_user identified by 'password';

mysql>grant all privileges on *.* to 'mysql_admin'@'%'; all privileges on MySQL instance

mysql>grant all privileges on mysql.* to 'mysql_user'@'%';privileges on mysql database

3). Grant the PROXY privilege for each of the proxied accounts to the proxy account:

mysql>grant proxy on mysql_admin to win_proxy;

mysql>grant proxy on mysql_user to win_proxy;

Now the Windows users "xxx.test\\administrator" can connect to the MySQL server as "win_proxy" and when authenticated have the privileges of the account "mysql_admin".

"xxx.test\\dzuser2" can connect to the MySQL server as "win_proxy" and when authenticated have the privileges of the account "mysql_user"

Check the configuration whether take effect on the client side:

1. Logon the OS as user "xxx.test\\administrator":

c:\>mysql -u win_proxy -h10.100.xx.xx (the MySQL server IP)

mysql> select user(),current_user(),@@proxy_user;

+----------------------+----------------+-----------------+

| user() | current_user() | @@proxy_user |

+----------------------+----------------+-----------------+

| win_proxy@xxx | mysql_admin@% | 'win_proxy'@'%' |

+----------------------+----------------+-----------------+

1 row in set (0.02 sec)

Domain user "xxx.test\\administrator" map to the MySQL account "mysql_admin" successfully.