如何更改存储过程中的用户密码 [英] How to change user passwords in stored procedures

问题描述

我们有一个第三方应用，它使用SQL登录进行用户访问。该应用程序使用单个管理员帐户进行硬编码，该帐户可以重置用户密码。此管理员帐户的密码由两名DBA控制，我们的IT主管希望授予权限
以将这些密码重置给其他IT员工而不授予任何其他权利，但应用程序开发人员无法更改应用程序允许将此权限委托给其他指定的帐户。



我想通过创建相关的IT人员访问新的证书签名sp来解决这个问题，并使用我升级的权限开发执行此操作以及重置密码时需要执行的其他几项任务（例如，将更改记录到
审计表中，并将密码标记为在其中一个apps表中过期，以便用户立即执行需要使用应用程序进程重置密码。）


我要注意不要打开我的sp到SQL注入，并希望将动态SQL与参数化语句一起使用。我希望使用这样的东西，其中sp由IT人员传递@username和@password：

We have a third-party app that uses SQL logins for user access. The app is hard coded with a single admin account that can reset user passwords. The password for this admin account is controlled by two DBAs and our IT Director would like to grant rights to reset these passwords to other IT staff without granting any other rights, but the app developer isn't open to changing the app to allow delegating this right to other named accounts.

I like to resolve this by creating the relevant IT staff access to a new cert signed sp with the escalated permissions that I develop to perform this and a couple of other tasks that need to happen when resetting a password (e.g., logging the change into an audit table, and flagging the password as expired in one of the apps tables, so the user immediately needs to reset the password using the apps process).

I want to take care not to open my sp to SQL injection and want to use dynamic SQL with parameterised statements. I was hoping to use something like this, where the sp is passed @username and @password by the IT staff:

EXEC sp_executesql
    N'ALTER LOGIN ' + @usrname + ' WITH PASSWORD = ''' + @passwd + '''
     , N'@usrname nvarchar(50)', N'@passwd nvarchar(50)'
     , @usrname = @username, @passwd = @password

注意 - 上面这将是运行查询以拉出可接受的登录以重置的步骤（即，sp不允许上述内容在sa或主管理员帐户上运行）。 



但它在第一个@usrname处出错并且看起来你不能在ALTER LOGIN语句中使用'login_name'的传递参数？



我发现很多人首先构建SQL的例子，但在我看来，它对SQL注入是开放的。 
$


我假设我可以使用sp_password而不是ALTER LOGIN，但是没有尝试过，因为它已被弃用。



有关如何通过存储过程实现此目的的任何建议，我可以让其他IT运行吗？

Note - above this will be steps that run a query to pull acceptable logins to reset (i.e., the sp won't allow the above to run on sa or the main admin account). 

But it gives an error at the first @usrname and it appears you can't use passed parameters for 'login_name' in the ALTER LOGIN statement?

I've found plenty of examples of people building the SQL first, but in ways that seem to me that it's open to SQL injection. 

I assume I can use sp_password rather than ALTER LOGIN, but haven't tried this, as it's deprecated.

Any advice on how to accomplish this via a stored procedure that I can let other IT run?

推荐答案

我不会在PORD中使用这种方法但是  DDL不接受变量。如果你动态构建字符串，请在变量名称周围使用quotename（）
来正确引用它们：

DECLARE @SQLStrng NVARCHAR（500）

SET @SQLString =' ALTER登录'+引号（@loginid）  +'WITH PASSWORD ='+ quotename（@newpassword，
''''）  +'OLD_PASSWORD ='+ quotename（@oldpassword，''''）

EXEC（ SQLString  ）

DECLARE @SQLStrng NVARCHAR(500)
SET @SQLString = 'ALTER LOGIN ' + quotename(@loginid) + ' WITH PASSWORD= ' + quotename(@newpassword, '''') + ' OLD_PASSWORD= ' + quotename(@oldpassword, '''')
EXEC(SQLString )

这篇关于如何更改存储过程中的用户密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！