I need a quick "no" for DELETE/UPDATE/INSERT, since 3p reporting tool allows users to write their own SQL.
我需要快速“删除”DELETE / UPDATE / INSERT,因为3p报告工具允许用户编写自己的SQL。
I know that I should probably add a new user and set permissions on tables/sp/views/etc..., and then create a new connection as restricted user.
我知道我应该添加一个新用户并设置table / sp / views / etc ...的权限,然后创建一个新的连接作为受限用户。
Is there a quicker way to force a transaction or connection in SQL Server to read only mode?
有没有更快的方法强制SQL Server中的事务或连接只读模式?
5 个解决方案
#1
Does it have to be with named users ? I have a "report" user and a "browser" user that just has select rights on most tables. Anyone that needs data uses those accounts and since they are select only I don't have to worry about them.
它必须与命名用户一起吗?我有一个“报告”用户和一个“浏览器”用户,它只对大多数表有选择权限。任何需要数据的人都使用这些帐户,因为他们只是选择,我不必担心它们。
See Kern's link.
请参阅Kern的链接。
#2
I don't know. If the 3P tool is that crazy, I would be completely paranoid about what I exposed to it. I think that setting up a new user is the best thing. Maybe even just giving them certian views and/or stored procs and calling it a day.
我不知道。如果3P工具那么疯狂,我会对我所接触到的东西完全偏执。我认为建立新用户是最好的选择。甚至可能只是给他们一些certian视图和/或存储过程并将其称为一天。
#3
Why are you worried about your users' ability to put arbitrary SQL in their reporting queries? If they have the rights to change data in your database, surely they can just connect to it with any ODBC client and execute the SQL directly.
为什么您担心用户在报告查询中放置任意SQL的能力?如果他们有权更改数据库中的数据,他们肯定可以使用任何ODBC客户端连接到它并直接执行SQL。
I'm not sure it's 3P that's the issue here, it sounds more like you need to restrict your users but haven't.
我不确定它是3P这是什么问题,听起来更像是你需要限制你的用户但却没有。
If you have a class of users who shouldn't be allowed to change your data, then set their accounts up that way. Relying on the fact that they'll only use a reporting tool that doesn't let them change data is a security hole I could drive a truck through.
如果您有一类不允许更改数据的用户,请以此方式设置其帐户。依靠他们只使用不让他们更改数据的报告工具的事实是一个安全漏洞,我可以驾驶卡车通过。
If they are allowed to change the data, restricting sessions from 3P won't help secure your system.
如果允许他们更改数据,则限制3P的会话将无法保护您的系统。
Unless I've misunderstood your set-up. I've been wrong before, just ask my wife. In which case, feel free to educate me.
除非我误解了你的设置。我以前错了,就问我老婆。在这种情况下,随时教育我。
#4
Change the permissions for the user (the one used in the connection string) on the SQL Server.
更改SQL Server上用户(连接字符串中使用的用户)的权限。
#5
If you have control when the connection is created and closed the you could perform a BEGIN TRAN and then do a ROLLBACK at the end. That way anything this reporting tool does will be rolled back at the end. However, if it has the ability to manage these transactions or new connections, or if the user base is unknown and potentially malicious then it is not foolproof. In addition, any large transaction may result in your database being locked by your users actions
如果您在创建和关闭连接时可以控制,则可以执行BEGIN TRAN,然后在结束时执行ROLLBACK。这样,这个报告工具所做的任何事情都将在最后回滚。但是,如果它能够管理这些事务或新连接,或者如果用户群是未知的并且可能是恶意的,那么它就不是万无一失的。此外,任何大型事务都可能导致您的数据库被用户操作锁定
I have to say though, the real answer is security is allocated to users. The "quicker" way you're after is a new user with just read only permissions.
我不得不说,真正的答案是安全性分配给用户。您所使用的“更快”方式是具有只读权限的新用户。
#1
Does it have to be with named users ? I have a "report" user and a "browser" user that just has select rights on most tables. Anyone that needs data uses those accounts and since they are select only I don't have to worry about them.
它必须与命名用户一起吗?我有一个“报告”用户和一个“浏览器”用户,它只对大多数表有选择权限。任何需要数据的人都使用这些帐户,因为他们只是选择,我不必担心它们。
See Kern's link.
请参阅Kern的链接。
#2
I don't know. If the 3P tool is that crazy, I would be completely paranoid about what I exposed to it. I think that setting up a new user is the best thing. Maybe even just giving them certian views and/or stored procs and calling it a day.
我不知道。如果3P工具那么疯狂,我会对我所接触到的东西完全偏执。我认为建立新用户是最好的选择。甚至可能只是给他们一些certian视图和/或存储过程并将其称为一天。
#3
Why are you worried about your users' ability to put arbitrary SQL in their reporting queries? If they have the rights to change data in your database, surely they can just connect to it with any ODBC client and execute the SQL directly.
为什么您担心用户在报告查询中放置任意SQL的能力?如果他们有权更改数据库中的数据,他们肯定可以使用任何ODBC客户端连接到它并直接执行SQL。
I'm not sure it's 3P that's the issue here, it sounds more like you need to restrict your users but haven't.
我不确定它是3P这是什么问题,听起来更像是你需要限制你的用户但却没有。
If you have a class of users who shouldn't be allowed to change your data, then set their accounts up that way. Relying on the fact that they'll only use a reporting tool that doesn't let them change data is a security hole I could drive a truck through.
如果您有一类不允许更改数据的用户,请以此方式设置其帐户。依靠他们只使用不让他们更改数据的报告工具的事实是一个安全漏洞,我可以驾驶卡车通过。
If they are allowed to change the data, restricting sessions from 3P won't help secure your system.
如果允许他们更改数据,则限制3P的会话将无法保护您的系统。
Unless I've misunderstood your set-up. I've been wrong before, just ask my wife. In which case, feel free to educate me.
除非我误解了你的设置。我以前错了,就问我老婆。在这种情况下,随时教育我。
#4
Change the permissions for the user (the one used in the connection string) on the SQL Server.
更改SQL Server上用户(连接字符串中使用的用户)的权限。
#5
If you have control when the connection is created and closed the you could perform a BEGIN TRAN and then do a ROLLBACK at the end. That way anything this reporting tool does will be rolled back at the end. However, if it has the ability to manage these transactions or new connections, or if the user base is unknown and potentially malicious then it is not foolproof. In addition, any large transaction may result in your database being locked by your users actions
如果您在创建和关闭连接时可以控制,则可以执行BEGIN TRAN,然后在结束时执行ROLLBACK。这样,这个报告工具所做的任何事情都将在最后回滚。但是,如果它能够管理这些事务或新连接,或者如果用户群是未知的并且可能是恶意的,那么它就不是万无一失的。此外,任何大型事务都可能导致您的数据库被用户操作锁定
I have to say though, the real answer is security is allocated to users. The "quicker" way you're after is a new user with just read only permissions.
我不得不说,真正的答案是安全性分配给用户。您所使用的“更快”方式是具有只读权限的新用户。