英文:
User permissions full from app but only select and view from MySQL
问题
我正在创建一个特殊用户，我需要在应用程序执行时赋予他完整权限，但在使用Workbench或控制台使用MySQL时仅允许选择和查看。
这是否可能？出于安全原因。
我之所以提出这个问题，是因为程序员可以在C# web.config中看到这个用户，我们不希望他们直接在控制台进行查询、插入和更新操作。
英文:
I am creating a special user which I have to give full permissions in the app execution but only select and view when I use Workbrench or the console using MySQL.
Is it possible? It is for security reasons.
I asked this question because programmers can see the user in C# web.config and we are not interested they do direct queries of insert and updates in the console.
答案1
得分: 2
给定用户只能拥有一组特权。不管他们使用什么程序进行连接，都无法使用户具有不同的特权取决于客户端程序。
但是，您可以根据客户端的主机来授予不同的特权。
在MySQL中，一个“用户”是由名称和从其连接的客户端主机组合而成的。即使用户名和密码相同，如果客户端从应用程序服务器主机连接，它可能会匹配具有完全特权的 user@host 条目，然后如果客户端从其他主机连接（例如，开发者工作站或跳转主机），则只授予您想要的有限特权。
要实现这一点，您需要创建两个用户。假设您的应用程序服务器主机都在子网192.168.200.%中：
CREATE USER 'app'@'192.168.200.%' IDENTIFIED BY '...';
CREATE USER 'app'@'%' IDENTIFIED BY '...';
然后相应地授予特权：
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'192.168.200.%';
GRANT SELECT ON appdb.* TO 'app'@'%';
当MySQL认证系统识别登录时，它首先匹配到最具体的主机模式。
这假设开发者没有SSH访问应用程序服务器主机。但即使他们有，您仍然可以设置监控，以便如果他们登录应用程序服务器主机并运行mysql命令行客户端，您将有日志记录。
英文:
A given user can only have one set of privileges. It doesn't matter what program they use when they connect. You can't make the user have different privileges depending on the client program.
But you can grant different privileges depending on the client host.
A "user" in MySQL is the combination of the name and the client host from which it connects. Even if the username and password are identical, if the client connects from an app server host, it could match the user@host entry that has full privileges, and then if the client connects from any other host (e.g. a developer workstation or a jump host), grant only the limited privileges you want.
The way you would do this is to create two users. Suppose your app server hosts are all in the subnet 192.168.200.%:
CREATE USER 'app'@'192.168.200.%' IDENTIFIED BY '...';
CREATE USER 'app'@'%' IDENTIFIED BY '...';
Then grant privileges accordingly:
CREATE SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'192.168.200.%';
GRANT SELECT ON appdb.* TO 'app'@'%';
When the MySQL authentication system recognizes a login, it matches it to the most specific host pattern first.
This assumes the developers don't have ssh access to the app server hosts. But even if they do, you could set up monitoring so you'd have a log if they ever do log into the app server hosts and run the mysql command-line client.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论