MySQL 应用程序中的安全多租户
我有一个 JSP/MySQL Web 服务，用户可以在其中与“进程”交互——他们可以为给定进程上传数据、配置、查看报告等。他们还可以创建新流程或运行比较多个流程的报告。
目前，进程 ID 在 URL（GET 参数）中指定，因此任何用户都可以与任何进程交互。 我被要求为此服务添加安全性和多租户。为简单起见，假设每个租户都拥有对一组进程的完全访问权限，但多个租户可以访问进程。
我的首选方法：
- 添加用户表（PK_User_Id、password_hash、名称等）
- 添加访问表（FK_User_Id、FK_Process_Id） 将
- Tenant_Id 存储在会话中的 SSL 登录页面
- 允许您选择要使用的 Process_Id 的进程选择页面可以访问并将其存储在会话中
- 几乎每个页面都会根据会话的 Process_Id 创建其 SQL 查询
- “跨进程”页面（例如“创建”、“选择”和“比较”）将根据会话的 User_Id 进行工作相反，
我的老板认为这“不够”安全，无法满足外部代码审核的要求。他担心任性的开发人员仍然可能编写一个查询，将一个客户的数据暴露给另一个客户或其他人。
他希望我还使用 ANSI SQL 的内置角色（应用程序必须与数据库无关）为每个用户创建一个数据库角色。该角色将详细说明该角色有权访问哪些表、共享表中的哪些行等。这样，在登录时，连接将是“安全的”，并且开发人员的错误不会导致问题。
- 这可能吗？
- 是否有与 MySQL 一起使用的与数据库无关的“角色”？
- 角色是否可以指定当主键为“foo”时允许您向表中添加行？
- 按照行业标准，我的系统“足够安全”吗？
I have a JSP/MySQL web service where users interact with "processes" -- they can upload data, configure, view reports, etc for a given process. They can also create new processes or run reports that compare several processes.
Currently, the process id is specified in the URL (a GET parameter) so any user can interact with any process. I have been asked to add security and multi-tenancy to this service. For simplicity, let's say each tenant has full access to a set of processes, but processes may be accessible by multiple tenants.
My preferred approach:
- Add a user table (PK_User_Id, password_hash, name, etc)
- Add an access table (FK_User_Id, FK_Process_Id)
- An SSL login page that stores the Tenant_Id in the Session
- A process-select page that lets you choose a Process_Id that you have access to, and stores that in the Session
- Almost every page will create its SQL queries based on the Session's Process_Id
- "Cross-process" pages like Create, Select, and Compare will work off of the Session's User_Id instead
My boss thinks that this is not secure "enough" to satisfy an external code audit. He fears that a wayward developer could still write a query that exposes one customer's data to another, or something.
He wants me to also use ANSI SQL's built in ROLES (the app must stay DB agnostic) to create a db role for each user. The role will detail which tables the role has access to, which rows in shared tables, etc. This way, upon login, the Connection will be "safe" and no developer mistake can possibly cause issues.
- Is this possible?
- Are there such a thing as DB-agnostic "Roles" that work with MySQL?
- Can the roles specify that you are allowed to add rows to a table iff the primary key is 'foo'?
- Is my system "secure enough" by industry standards?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
以下是我对具有单个数据库的 MySQL 多租户所做的操作，以确保数据的私有性：
由于应用程序正在使用租户的mysql用户，因此没有机会他们可能会意外地获取其他租户的数据。
我能够在一个周末将大型单租户 mysql 应用程序转换为多租户，只需进行最小的更改。我在这里记录了设计： https://opensource.io/it/mysql-multi-tenant /
Here is what I do for MySQL multi-tenant with a single database to ensure data is private:
Since the application is using the tenant's mysql user there is no chance that they can accidentally get another tenant's data.
I was able to convert a large single-tenant mysql application to multi-tenant in a weekend with minimal changes. I documented the design here: https://opensource.io/it/mysql-multi-tenant/
改用 PostgreSQL，因为它支持真实模式，与 MySQL 不同
如果必须使用 MySQL，请执行以下操作：
tenantVARCHAR(16) NOT NULL，现在用户只能查看自己的租户信息。
use PostgreSQL instead, as it supports real schemas, unlike MySQL
if you have to use MySQL, do the following:
tenantVARCHAR(16) NOT NULLAnd now the user can only see their own tenant information.
我们对多租户安全性和处理请求进行了类似的讨论所以问题 。但总之我认为在会话中存储tenantID是一个巨大的安全风险。用户可以从一个租户转到另一个租户，租户 ID 将保持不变，并且租户 ID 不应通过 url 发送。
We had a similar discussion on multitenancy security and handling requests on so question. But in short I think storing tenantID in session is a huge security risk. User can go from one tenant to other and tenantID will remain the same, also tenantID should not be send through url.