206.编程管理SQL SERVER的帐号

红太狼 2024-02-19 19:40 48阅读 0赞

编程管理SQL SERVER的帐号  
      
    每个人都关心数据安全。
    如果不关心的话，可能没有意识到访问服务器中的敏感数据是非常容易的。
    因为SQL SERVER在这方面已经替我们考虑过了，
    所以在我们开发应用程序就可以直接使用SQL SERVER的安全策略。 
    
    	在SQL SERVER中，我们可以在ENTERPRISE MANAGER中创建一个登录帐号，
    并且赋予一定的权限，如果要做到我们的应用程序中呢？ 
    
    
    ==============================================================================
    SQL SERVER提供了如下系统过程 
    
    
    ▲▲一、SP_ADDLOGIN
    创建新的 Microsoft SQL Server 登录，使用户得以连接使用 SQL Server 身份验证的 SQL Server 实例。
    
    语法
    sp_addlogin [ @loginame = ] 'login'
        [ , [ @passwd = ] 'password' ]
        [ , [ @defdb = ] 'database' ]
        [ , [ @deflanguage = ] 'language' ]
        [ , [ @sid = ] sid ]
        [ , [ @encryptopt = ] 'encryption_option' ]
    
    参数
    [@loginame =] 'login'
    登录的名称。login 的数据类型为 sysname，没有默认设置。
    
    [@passwd =] 'password'
    登录密码。password 的数据类型为 sysname，默认设置为 NULL。sp_addlogin 执行后，password 被加密并存储在系统表中。
    
    [@defdb =] 'database'
    登录的默认数据库（登录后登录所连接到的数据库）。database 的数据类型为 sysname，默认设置为 master。
    
    [@deflanguage =] 'language'
    用户登录到 SQL Server 时系统指派的默认语言。language 的数据类型为 sysname，默认设置为 NULL。如果没有指定 language，那么 language 被设置为服务器当前的默认语言（由 sp_configure 配置变量 default language 定义）。更改服务器的默认语言不会更改现有登录的默认语言。language 保持与添加登录时所使用的默认语言相同。
    
    [@sid =] sid
    安全标识号 (SID)。sid 的数据类型为 varbinary(16)，默认设置为 NULL。如果 sid 为 NULL，则系统为新登录生成 SID。尽管使用 varbinary 数据类型，非 NULL 的值也必须正好为 16 个字节长度，且不能事先存在。SID 很有用，例如，如果要编写 SQL Server 登录脚本，或要将 SQL Server 登录从一台服务器移动到另一台，并且希望登录在服务器间具有相同的 SID 时。
    
    [@encryptopt =] 'encryption_option'
    指定当密码存储在系统表中时，密码是否要加密。encryption_option 的数据类型为 varchar(20)，可以是下列值之一。
    值					描述 
    ------------------- ------------------------------------------------------------
    NULL				加密密码。这是默认设置。 
    skip_encryption		密码已加密。SQL Server 应该存储值而且不用重新对其加密。 
    skip_encryption_old	已提供的密码由 SQL Server 较早版本加密。SQL Server 应该存储值而且不用重新对其加密。此选项只供升级使用。 
    
    
    ▲▲二、SP_GRANTDBACCESS
    为 Microsoft SQL Server 登录或 Microsoft Windows NT 用户或组在当前数据库中添加一个安全帐户，并使其能够被授予在数据库中执行活动的权限。
    
    语法:
    sp_grantdbaccess [@loginame =] 'login'
        [,[@name_in_db =] 'name_in_db' [OUTPUT]]
    
    参数:
    [@loginame =] 'login'
    当前数据库中新安全帐户的登录名称。Windows NT 组和用户必须用 Windows NT 域名限定，格式为"域\用户"，例如 LONDON\Joeb。登录不能使用数据库中已有的帐户作为别名。login 的数据类型为 sysname，没有默认值。
    
    [@name_in_db =] 'name_in_db' [OUTPUT]
    数据库中帐户的名称。name_in_db 是 sysname 类型的 OUTPUT 变量，默认值为 NULL。如果没有指定，则使用 login。如果将其指定为 NULL 值的 OUTPUT 变量，则设置 @name_in_db 为 login。当前数据库不必存在 name_in_db。
    
    
    ▲▲三、SP_DROPLOGIN
    删除 Microsoft SQL Server 登录，以阻止使用该登录名访问 SQL Server。
    
    语法
    sp_droplogin [ @loginame = ] 'login'
    
    参数
    [@loginame =] 'login'
    将被删除的登录。login 的数据类型为 sysname，没有默认值。login 必须已经存在于 SQL Server 中。
    
    
    ▲▲四、SP_REVOKEDBACCESS
    从当前数据库中删除安全帐户。
    
    语法
    sp_revokedbaccess [ @name_in_db = ] 'name'
    
    参数
    [@name_in_db =] 'name'
    是要删除的帐户名。name 的数据类型为 sysname，无默认值。name 可以是 Microsoft? SQL Server? 用户名或 Microsoft Windows NT? 用户名或组名，而且必须存在于当前数据库中。当指定 Windows NT 用户或组时，请指定该 Windows NT 用户或组在数据库中可被识别的名称（即用 sp_grantdbaccess 添加的名称）。
    
    
    ▲▲五、GRANT
    在安全系统中创建项目，使当前数据库中的用户得以处理当前数据库中的数据或执行特定的 Transact-SQL 语句。
    
    语法
    语句权限：
    
    GRANT { ALL | statement [ ,...n ] } 
    TO security_account [ ,...n ]
    
    对象权限：
    
    GRANT
        { ALL [ PRIVILEGES ] | permission [ ,...n ] }
        { 
            [ ( column [ ,...n ] ) ] ON { table | view }
            | ON { table | view } [ ( column [ ,...n ] ) ]
            | ON { stored_procedure | extended_procedure }
            | ON { user_defined_function }
        } 
    TO security_account [ ,...n ] 
    [ WITH GRANT OPTION ] 
    [ AS { group | role } ]
    
    
    ▲▲六、REVOKE
    删除以前在当前数据库内的用户上授予或拒绝的权限。
    
    语法
    语句权限：
    REVOKE { ALL | statement [ ,...n ] } 
    FROM security_account [ ,...n ]
    
    对象权限：
    
    REVOKE [ GRANT OPTION FOR ]
        { ALL [ PRIVILEGES ] | permission [ ,...n ] }
        { 
            [ ( column [ ,...n ] ) ] ON { table | view }
            | ON { table | view } [ ( column [ ,...n ] ) ]
            | ON { stored_procedure | extended_procedure }
            | ON { user_defined_function }
        } 
    { TO | FROM }
        security_account [ ,...n ] 
    [ CASCADE ] 
    [ AS { group | role } ] 
    
    
    ▲▲SP_PASSWORD
    添加或更改 Microsoft? SQL Server? 登录的密码。
    
    语法
    sp_password [ [ @old = ] 'old_password' , ]
        { [ @new =] 'new_password' }
        [ , [ @loginame = ] 'login' ]
    
    参数
    [@old =] 'old_password'
    是旧密码。old_password 为 sysname 类型，其默认值为 NULL。
    
    [@new =] 'new_password'
    是新密码。new_password 为 sysname 类型，无默认值。如果没有使用命名参数，就必须指定 old_password。
    
    [@loginame =] 'login'
    是受密码更改影响的登录名。login 为 sysname 类型，其默认值为 NULL。login 必须已经存在，并且只能由 sysadmin 固定服务器角色的成员指定。
    
    　 
    =============================================================================
    应用实例： 
    
    --添加
    --添加用户:
    exec sp_addlogin '用户名','密码','默认数据库名'
    
    --添加到数据库
    exec sp_grantdbaccess '用户名','数据库名'
    
    --分本权限
    grant insert,select,update,delete on table1 to public 
    
    --删除         
    --删除权限
    revoke insert,delete on table1 from public 
    
    --删除数据库中的用户
    exec sp_revokedbaccess '用户名'
    
    --删除用户
    exec sp_droplogin '用户名'
    
    
    --示例2
    
    --创建仅对某个数据库具有所有权限的用户
    --切换到你新增的用户要控制的数据库
    use 你的库名
    go
    
    --新增用户
    exec sp_addlogin 'test'	--添加登录
    exec sp_grantdbaccess N'test'	--使其成为当前数据库的合法用户
    exec sp_addrolemember N'db_owner', N'test'	--授予对自己数据库的所有权限
    
    --这样创建的用户就只能访问自己的数据库,及数据库中包含了guest用户的公共表
    go
    
    --删除测试用户
    exec sp_revokedbaccess N'test'	--移除对数据库的访问权限
    exec sp_droplogin N'test'	--删除登录