【SQL Server学习笔记】Windows主体、SQL Server主体、数据库主体

主体就是可以被授予权限，访问特定数据库对象的对象。比如，用户登录，角色，应用程序。

安全对象是可以访问的受到控制的对象。比如表、视图、存储过程、函数、触发器。

权限是独立的，可以被授予给主体，以访问安全对象，或者未被授予，以阻止访问安全对象。

主体可以分为3个不同的范围：

1、Windows级别的主体：基于Windows域用户账号、域用户组、本地用户组的主体。当添加到SQL Server中，并且给予访问对象的权限，那么这类主体将基于Windows身份验证，来获得访问SQL Server所需要的权限。

Windows级别的主体

Windows级别的主体通过Windows身份验证，访问SQL Server实例。

在SQL Server中可以创建基于Windows用户账号、用户组的Windows登录名，它可以属于本地机器，或者属于一个域(Windows登录名可以和域用户、本地用户、用户组进行关联)。当添加一个Windows登录名到SQL Server时，这个windows登录名可以对应Windows中的用户名，也可以对应一个Windows用户组。

添加到SQL Server的windwos登录名不需要独立的密码，就可以登录，登录身份验证的过程是由windows来控制的。

默认情况下SQL SERVER只允许Administrators组的成员可以连接到数据库，也就是不能用Administrators组来创建windows登录名。 

CREATE LOGIN loginName  FROM <sources> 

<sources> ::=
    WINDOWS [ WITH <windows_options>[ ,... ] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name

<windows_options> ::=      
    DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language

--1.1以windows用户创建登录名
create login [PC0627JVC\ggg]     --机器名称\用户名
from windows
with default_database = wc,      --默认数据库
     default_language = 简体中文  --登录名的默认语言


--1.2以windows用户组创建登录名
create login [PC0627JVC\ora_dba] --机器名称\用户组名
from windows
with default_database = wc,      --默认数据库
     default_language = 简体中文  --登录名的默认语言


--2.查看windows登录名
select s.name,
       s.principal_id,
       s.sid,
       s.type_desc,
       s.is_disabled
from sys.server_principals s
where s.type_desc in ('WINDOWS_LOGIN',
                      'WINDOWS_GROUP')


--3.1改变默认语言,默认数据库
alter login [PC0627JVC\ggg]
with default_database = wcc,
     default_language = english


--3.2禁用或启用登录名
alter login [PC0627JVC\ggg]
disable

alter login [PC0627JVC\ggg]
enable


--4.拒绝widnows登录名访问SQL Server
--如果用ggg这个账号登陆windows后,就登不上SQL Server了
deny connect sql to [PC0627JVC\ggg]


--5.删除windows登录名
drop login [PC0627JVC\ora_dba]

drop login [PC0627JVC\ggg]

2、SQL Server级别的主体：是SQL Server级别的登录名和固定的服务器角色。在SQL Server中创建的SQL登录名，拥有独立于任何Windows实体的登录名和密码。固定的服务器角色是SQL Server实例级别权限的分组，其他主体能成为它的成员，而且继承了服务器角色的权限，另外，固定服务器角色之所以是固定的，是因为不能添加服务器角色。  

CREATE LOGIN loginName  WITH <option_list1>   

<option_list1> ::= 
    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
    [ , <option_list2> [ ,... ] ]

<option_list2> ::=  
    SID = sid
    | DEFAULT_DATABASE = database    
    | DEFAULT_LANGUAGE = language
    | CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}
    | CREDENTIAL = credential_name 

--1.1创建sql登录名
create login ggg
with password = '123',
     default_database = wc
     

--1.2创建登录名，同时设置密码策略
create login yg
with password = '123' must_change,  --在第一次登录时会提示修改密码
     
     check_expiration = on,   --对sql登录名强制实施密码过期策略
     check_policy = on        --把Windows密码策略应用到sql登录名上
     

--1.3查看sql登录名
select name,
       principal_id,
       sid,
       type_desc,
       is_disabled
from sys.server_principals 
where type_desc in ('SQL_LOGIN')



--2.1修改登录名的密码
alter login ggg
with password = '123456'
     old_password = '123'


--2.2同时修改登录名和密码
alter login ggg
with name = www,
     password = '12345678'


--2.2修改登录名的默认数据库
alter login www
with default_database = master



--3.1管理登录名的密码
select LOGINPROPERTY('www','IsLocked'),
       LOGINPROPERTY('www','IsExpired'),
       LOGINPROPERTY('www','IsMustChange'),
       LOGINPROPERTY('www','BadPasswordCount'),
       LOGINPROPERTY('www','BadPasswordTime'),
       LOGINPROPERTY('www','HistoryLength'),
       LOGINPROPERTY('www','LockoutTime'),
       LOGINPROPERTY('www','PasswordLastSetTime'),
       LOGINPROPERTY('www','PasswordHash')


--3.2.删除sql登录名
drop login www



--4服务器角色成员
--4.1查看固定服务器角色
select name,
       principal_id,
       sid,
       type_desc,
       is_disabled
from sys.server_principals 
where type_desc = 'SERVER_ROLE'


--显示固定服务器角色
exec sp_helpsrvrole


--4.2把登录名添加到服务器角色
exec sp_addsrvrolemember 
	@loginame = 'ggg',
	@rolename = 'sysadmin'
	

--4.3删除服务器角色成员,这是就算ggg已经登录到sql server,也会导致ggg无法继续操作
exec sp_dropsrvrolemember 
	@loginame = 'ggg',
	@rolename = 'sysadmin'
	

--4.4显示固定服务器角色的成员
select p.name,          --固定服务器角色
       p.principal_id,
       p.sid,
       
       pp.name,         --成员
       pp.principal_id,
       pp.sid
from sys.server_principals p
inner join sys.server_role_members r	
        on p.principal_id = r.role_principal_id
inner join sys.server_principals pp
        on pp.principal_id = r.member_principal_id        
where p.name = 'sysadmin'


--显示服务器角色的成员
exec sp_helpsrvrolemember
	@srvrolename = 'sysadmin'
	        

3、数据库级别的主体：数据库用户、数据库角色(固定的、用户定义的)、应用程序角色。可以添加数据库角色和应用程序角色。

 需要注意的是，应用程序角色是不包含成员的成组的权限，能够以应用程序角色的身份登录。当使用一个应用程序角色时，它会覆盖登录名的其他所有权限，只授予你应用程序角色的那些权限。 

CREATE USER user_name 
    [ { { FOR | FROM }
      { 
        LOGIN login_name 
        | CERTIFICATE cert_name 
        | ASYMMETRIC KEY asym_key_name
      } 
      | WITHOUT LOGIN
    ] 
    [ WITH DEFAULT_SCHEMA = schema_name ]

--1.1创建数据库用户
create user ggg
for login ggg
with default_schema = ggg  --用户的默认架构，如果不存在，会自动创建


--1.2显示数据库用户的信息
exec sp_helpuser 
	@name_in_db = 'ggg'


select name,
       principal_id,
       type_desc,
       default_schema_name,
       sid
from sys.database_principals
where type_desc = 'SQL_USER'


--1.3修改数据库用户
alter user ggg
with default_schema = dbo   --修改默认架构


alter user ggg
with name = wwwc            --修改数据库用户名


--1.4删除数据库用户名
drop user wwwc


/*=====================================================
1.5修复孤立的数据库用户

登录名有一个安全标示符，能唯一标识出这个登录名。
数据库迁移之后，重新创建登录名，而这个登录名会对应到不同的sid，
也就是登录名和数据库用户名之间的对应关系可能会变化，
才会导致孤立的数据库用户
======================================================*/
select DP.name,
       DP.sid,
       DP.principal_id,
       
       SP.name,
       SP.sid
from sys.database_principals dp
left join sys.server_principals sp
       on dp.sid = sp.sid 
where sp.sid is null
      and dp.type_desc = 'SQL_USER'


--修改用户，重新指定登录名，来更新数据库用户对应的sid
ALTER USER GGG
WITH LOGIN = GGG



--2.固定数据库角色
--2.1显示固定数据库角色
exec sp_helpdbfixedrole 
	--@rolename = 'db_ddladmin'


select name,
       principal_id,
       type_desc,
       default_schema_name ,
       sid,
       
       is_fixed_role
       
from sys.database_principals
where type_desc = 'DATABASE_ROLE'
      and is_fixed_role = 1
      
      
      
--2.2显示固定数据库角色的成员
exec sp_helprolemember 
	--@rolename = 'db_ddladmin'


select dp.name,
       
       dpp.name,
       dpp.sid
from sys.database_role_members rm
inner join sys.database_principals dp
        on dp.principal_id = rm.role_principal_id

inner join sys.database_principals dpp
        on dpp.principal_id = rm.member_principal_id


--2.3.管理固定数据库角色成员
exec sp_addrolemember 
	@rolename = 'db_datawriter',
	@membername = 'ggg'
	

exec sp_droprolemember
	@rolename = 'db_datawriter',
	@membername = 'ggg'



--3.用户定义的数据库角色
--3.1创建
create role wc_role authorization db_owner 


--3.2修改角色名称
alter role wc_role
with name = w_role


--3.3给角色添加用户
exec sp_addrolemember
	@rolename = 'w_role',
	@membername = 'ggg'


--3.4从角色中删除用户
exec sp_droprolemember
	@rolename = 'w_role',
	@membername = 'ggg'


--3.5删除
drop role wc_role
	
	

--4.应用程序角色
--4.1创建
create application role www_c
with password = '123',
     default_schema = dbo
     

--4.2修改
alter application role www_c
with name = wwc,
     password = '12345'


--4.3授予权限
grant select on dbo.wcObjects to wwc


--4.4激活应用程序角色
exec sp_setapprole 
	@rolename = 'wwc',
	@password ='12345'


--4.5查询数据
select * from dbo.wcObjects


--4.6拒绝了对对象 'book' (数据库 'wc'，架构 'dbo')的 SELECT 权限。
select * from dbo.book


--4.7删除应用程序角色
drop application role wwc

查询当前连接的主机的域用户名

select *
from sys.server_principals
where type_desc in ('WINDOWS_LOGIN','WINDOWS_GROUP')
and CHARINDEX('NT',name)=0