Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Организация доступа пользователей к БД на MS SQL  [new]
novexelf
Member

Откуда: this
Сообщений: 184
Разрабатывая некоторую БД появилось масса вопросов по организации доступа пользователей к БД на MS SQL, в частности:

1. Для создания новых пользователей создал хранимую процедуру
Create Procedure prc_CreateUser
	@UserName varchar(200),
	@UserLogin varchar(100),
	@UserPassword varchar(300),
	@DefDBName varchar(200) = 'ATOL',
	@DefRole varchar(100) = 'public'
As
	set nocount on
	
	If (@UserName Is Null) Or (@UserName = '') Or 
		(@UserLogin Is Null) Or (@UserLogin = '') Or 
		(@UserPassword Is Null) Or (@UserPassword = '') 
		Return (1)				-- free input param
		
	if ((IS_MEMBER('db_securityadmin') < 1) Or (IS_MEMBER('db_accessadmin') < 1))
		Return (2)				-- access denied
	
	--CREATE LOGIN @UserLogin 
	--	WITH PASSWORD=@UserPassword, 
	--	DEFAULT_DATABASE=@DefDBName, 
	--	CHECK_EXPIRATION=OFF, 
	--	CHECK_POLICY=OFF
	
	exec sp_addlogin @UserLogin, @UserPassword, @DefDBName
	
	If @@error <> 0 
		Return (3)				-- Error sp_addlogin
		
	exec sp_adduser @UserLogin --, @UserName, @DefRole
	
	--CREATE USER @UserName FOR LOGIN @UserLogin

	If @@error <> 0 
		Return (4)				-- Error sp_adduser 
		
	-- Insert into ATOL table
	Insert Into T_Users(UserLogin, UserName) Values (@UserLogin, @UserName)

	If @@error <> 0 
		Return (5)				-- Error Insert into ATOL table

	-- RETURN SUCCESS --
	Return (0)					-- prc_CreateUser
Go

1.1. в процедуре используются sp_addlogin и sp_adduser, в MSDN пишут, что вместо них необходимо использовать Create Login и Create User, но у меня почему-то из процедуры не получилось, хотя из скрипта генерирующего БД аналогичные конструкции отработали без ошибок, собственно вопрос что я делаю не так?

1.2. права на выполнение процедуры даются группе public, а непосредственно в процедуре проверяется наличие прав у пользователя - это допустимо или нужно создать свои роли и раздать им права?

1.3. пробовал запустить процедуру от имени пользователя имеющего db_securityadmin, db_accessadmin и securityadmin, выполнение завершилось с ошибкой, дал пользователю еще и sysadmin, соответственно вопрос, какой минимум необходим, чтобы создать логин ms sql и пользователя БД?

1.4. до изменений процедуры строка
exec sp_adduser @UserLogin --, @UserName, @DefRole
имела вид
exec sp_adduser @UserLogin, @UserName, @DefRole

и при запуске процедуры с параметрами prc_CreateUser 'Иванов Иван Иванович', 'ivanovii', 'mypassword'

в итоге получил:
на ms sql создался login: ivanovii

в БД создался пользователь: Иванов Иван Иванович, имеющий имя входа: ivanovii и схему по умолчанию: Иванов Иван Иванович.
Откуда взялась схема, явно она ни где не указывалась, и главное не ясно правильно ли это, и если не правильно, то как указать что нужен например dbo, у других пользователей созданных через EM схема dbo.

1.5. Не будет ли проблем если пользователь БД будет иметь наименование на крилице?

2. Создал представление для получения списка пользователей БД:
-- Список пользователей базы данных
Create View vw_UserList
As
	Select 
		tu.UserName UserName, 
		sl.LoginName LoginName, 
		sl.DenyLogin DenyLogin, 
		sl.HasAccess HasAccess, 
		sl.IsNtName IsNtName, 
		sl.IsNtGroup IsNtGroup, 
		sl.IsNtUser IsNtUser, 
		sl.SysAdmin SysAdmin,
		sl.SecurityAdmin SecurityAdmin,
		sl.ServerAdmin ServerAdmin,
		sl.SetupAdmin SetupAdmin,
		sl.ProcessAdmin ProcessAdmin,
		sl.DiskAdmin DiskAdmin,
		sl.DbCreator DbCreator,
		sl.BulkAdmin BulkAdmin,
		LoginIsLock = 
			Case sl.DenyLogin 
				When 1 Then 'Блокирован' 
				Else ''
			End
	From sys.syslogins sl, T_Users tu
	Where (Upper(sl.dbname) = 'ATOL') And 
		(sl.LoginName = tu.UserLogin) And
		(IS_MEMBER('db_securityadmin') = 1) And
		(IS_MEMBER('db_accessadmin') = 1)

Go

2.1. права на выборку из представления даны группе public, в самом представлении проверяется наличие у пользователя ролей db_securityadmin и db_accessadmin - это допустимо или нужно создать свои роли и раздать им права?

2.2. не ясна разница между полями DenyLogin и HasAccess, какое из них показывает, что доступа нет?

3. На сервере действует политика безопасности органичивающая срок жизни паролей, соответственно вопрос, как при подключении из разрабатываемого клиентского приложения понять, что срок жизни пароля истек? Приложение на С#.

4. Как дать возможность пользователю из разрабатываемого клиенсткого приложения произвести смену личного пароля, причем как по истечении срок жизни пароля, так самостоятельно инициированную смену? интересуют серверные средства, понятно, что на клиенте нужно создать форму для ввода паролей и что-то после вызвать для проведения смены, вопрос что?

5. Возможно ли с помощью процедур sp_DenyLogin / sp_GrantLogin запрещать/предоставлять доступ к БД, или нужно использовать другие средства? Тогда какие? имеется ввиду временное прекращение доступа и последующие его предоставление.

Благодарю всех участвующих в обсуждении.
10 июн 11, 12:29    [10795126]     Ответить | Цитировать Сообщить модератору
 Re: Организация доступа пользователей к БД на MS SQL  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
ДЛя начала нужно прочитать документацию по используемым Вами ролям и системным процедурам - там найдете ответы на многие вопросы, а некоторые - и задавать после станете. ;)
10 июн 11, 13:53    [10796049]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить