主体就是可以被授予权限,访问特定数据库对象的对象。比如,用户登录,角色,应用程序。
安全对象是可以访问的受到控制的对象。比如表、视图、存储过程、函数、触发器。
权限是独立的,可以被授予给主体,以访问安全对象,或者未被授予,以阻止访问安全对象。
主体可以分为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