-- Інформація про об'єкти БД
SELECT 
    name AS [Назва об'єкта],
    type_desc AS [Тип],
    create_date AS [Дата створення],
    modify_date AS [Дата зміни]
FROM sys.objects
WHERE is_ms_shipped = 0  -- Виключити системні об'єкти
ORDER BY type_desc, name;

-- Перегляд тексту збережених процедур і функцій
SELECT 
    o.name AS [Назва],
    o.type_desc AS [Тип],
    m.definition AS [Код]
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE o.is_ms_shipped = 0;

-- Інформація про тригери
SELECT 
    name AS [Тригер],
    parent_class_desc AS [Область],
    is_disabled AS [Відключений],
    is_instead_of_trigger AS [INSTEAD OF]
FROM sys.triggers
WHERE is_ms_shipped = 0;

-- Зміна пароля
ALTER LOGIN AppUser 
WITH PASSWORD = 'NewP@ssw0rd456';

-- Розблокування та зміна пароля
ALTER LOGIN AppUser 
WITH PASSWORD = 'NewP@ssw0rd456' UNLOCK;

-- Відключення логіна (не видаляючи)
ALTER LOGIN AppUser DISABLE;

-- Увімкнення логіна
ALTER LOGIN AppUser ENABLE;

-- Перейменування логіна
ALTER LOGIN AppUser WITH NAME = NewAppUser;

-- Видалення логіна
DROP LOGIN AppUser;

-- Список всіх логінів
SELECT 
    name AS [Login],
    type_desc AS [Тип],
    create_date AS [Створено],
    is_disabled AS [Відключено]
FROM sys.sql_logins
ORDER BY name;

-- Системна процедура
EXEC sp_helplogins;
EXEC sp_helplogins 'AppUser';  -- Детальна інформація про конкретний логін

USE MyDatabase;
GO

-- Створення користувача для SQL-логіна
CREATE USER AppUser FOR LOGIN AppUser;

-- Створення користувача для Windows-логіна
CREATE USER [DOMAIN\UserName] FOR LOGIN [DOMAIN\UserName];

-- Користувач без логіна (для certificates або asymmetric keys)
CREATE USER GuestUser WITHOUT LOGIN;

-- Користувач з default schema
CREATE USER DevUser FOR LOGIN DevUser
WITH DEFAULT_SCHEMA = dbo;

-- Зміна схеми за замовчуванням
ALTER USER AppUser WITH DEFAULT_SCHEMA = Sales;

-- Перейменування користувача
ALTER USER AppUser WITH NAME = NewAppUser;

USE MyDatabase;
GO

DROP USER AppUser;

-- Список користувачів поточної БД
SELECT 
    name AS [Користувач],
    type_desc AS [Тип],
    authentication_type_desc AS [Автентифікація],
    default_schema_name AS [Схема за замовчуванням],
    create_date AS [Створено]
FROM sys.database_principals
WHERE type IN ('S', 'U', 'G')  -- S=SQL user, U=Windows user, G=Windows group
ORDER BY name;

ALTER SERVER ROLE securityadmin ADD MEMBER [DOMAIN\SecurityAdmin];

USE MyDatabase;
GO

-- Створення власної ролі
CREATE ROLE SalesTeam;

-- Надання прав ролі
GRANT SELECT, INSERT, UPDATE ON Sales.Orders TO SalesTeam;
GRANT SELECT ON Sales.Customers TO SalesTeam;
GRANT EXECUTE ON dbo.GetSalesReport TO SalesTeam;

-- Додавання користувачів до ролі
ALTER ROLE SalesTeam ADD MEMBER SalesUser1;
ALTER ROLE SalesTeam ADD MEMBER SalesUser2;

-- Видалення користувача з ролі
ALTER ROLE SalesTeam DROP MEMBER SalesUser1;

-- Видалення ролі
DROP ROLE SalesTeam;

-- ЗАСТАРІЛО: Додавання до серверної ролі
EXEC sp_addsrvrolemember 'AppUser', 'dbcreator';

-- ЗАСТАРІЛО: Видалення із серверної ролі
EXEC sp_dropsrvrolemember 'AppUser', 'dbcreator';

-- ЗАСТАРІЛО: Додавання до ролі БД
EXEC sp_addrolemember 'db_datareader', 'AppUser';

-- ЗАСТАРІЛО: Видалення з ролі БД
EXEC sp_droprolemember 'db_datareader', 'AppUser';

-- Серверні ролі
SELECT 
    r.name AS [Роль],
    m.name AS [Член]
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
ORDER BY r.name, m.name;

-- Ролі БД
SELECT 
    r.name AS [Роль],
    m.name AS [Член]
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
ORDER BY r.name, m.name;

-- Створення Application Role
CREATE APPLICATION ROLE AppRole
WITH PASSWORD = 'SecureP@ssw0rd!',
     DEFAULT_SCHEMA = dbo;

-- Надання прав
GRANT SELECT, INSERT ON dbo.Orders TO AppRole;
GRANT EXECUTE ON dbo.ProcessOrder TO AppRole;

-- Активація ролі з додатка
DECLARE @cookie VARBINARY(8000);
EXEC sp_setapprole 
    @rolename = 'AppRole', 
    @password = 'SecureP@ssw0rd!',
    @fCreateCookie = 1,
    @cookie = @cookie OUTPUT;

-- Виконання операцій під роллю
SELECT * FROM dbo.Orders;

-- Повернення до попереднього контексту
EXEC sp_unsetapprole @cookie;

-- Зміна пароля
ALTER APPLICATION ROLE AppRole
WITH PASSWORD = 'NewP@ssw0rd!';

-- Зміна схеми за замовчуванням
ALTER APPLICATION ROLE AppRole
WITH DEFAULT_SCHEMA = Sales;

-- Видалення
DROP APPLICATION ROLE AppRole;

USE MyDatabase;
GO

-- Надати права на читання таблиці
GRANT SELECT ON dbo.Employees TO AppUser;

-- Надати права на конкретні стовпці
GRANT SELECT ON dbo.Employees(FirstName, LastName, Email) TO AppUser;

-- Надати права на оновлення
GRANT UPDATE ON dbo.Products TO SalesUser;

-- Надати права на виконання процедури
GRANT EXECUTE ON dbo.GetSalesReport TO ReportUser;

-- Надати права на схему
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO SalesTeam;

-- Надати права з можливістю передачі (WITH GRANT OPTION)
GRANT SELECT ON dbo.Orders TO ManagerUser WITH GRANT OPTION;

-- Явно заборонити читання
DENY SELECT ON dbo.Salaries TO RegularUser;

-- Заборонити видалення
DENY DELETE ON dbo.Orders TO AppUser;

-- Заборонити всі DML операції
DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecurityAudit TO AppUser;

-- Відкликати права на читання
REVOKE SELECT ON dbo.Employees FROM AppUser;

-- Відкликати права на виконання
REVOKE EXECUTE ON dbo.ProcessOrder FROM AppUser;

-- Відкликати GRANT OPTION (але залишити саме право)
REVOKE GRANT OPTION FOR SELECT ON dbo.Orders FROM ManagerUser;

-- Права на об'єкти поточної БД
SELECT 
    dp.name AS [Principal],
    dp.type_desc AS [Principal Type],
    o.name AS [Object],
    p.permission_name AS [Permission],
    p.state_desc AS [State]
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects o ON p.major_id = o.object_id
WHERE dp.name = 'AppUser'
ORDER BY o.name, p.permission_name;

-- Права на рівні сервера
SELECT 
    sp.name AS [Principal],
    p.permission_name AS [Permission],
    p.state_desc AS [State]
FROM sys.server_permissions p
JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id
WHERE sp.name = 'AppUser';

-- Увімкнути політику паролів
ALTER LOGIN AppUser 
WITH CHECK_POLICY = ON, CHECK_EXPIRATION = ON;

-- Вимоги до пароля:
-- - Мінімум 8 символів
-- - Містить великі та малі літери
-- - Містить цифри
-- - Містить спеціальні символи

-- Створення DDL тригера для відстеження створення логінів
CREATE TRIGGER trg_AuditLoginCreation
ON ALL SERVER
FOR CREATE_LOGIN
AS
BEGIN
    DECLARE @EventData XML = EVENTDATA();
    
    INSERT INTO SecurityDB.dbo.LoginAudit (
        EventType,
        LoginName,
        CreatedBy,
        EventDate
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)'),
        GETDATE();
END;
GO

-- Створення LOGON тригера
CREATE TRIGGER trg_LoginFailureTracking
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    -- Перевірка кількості невдалих спроб
    DECLARE @LoginName NVARCHAR(128) = ORIGINAL_LOGIN();
    DECLARE @FailedAttempts INT;
    
    SELECT @FailedAttempts = COUNT(*)
    FROM SecurityDB.dbo.FailedLogins
    WHERE LoginName = @LoginName
      AND AttemptTime > DATEADD(MINUTE, -15, GETDATE());
    
    -- Якщо більше 5 невдалих спроб за 15 хвилин
    IF @FailedAttempts >= 5
    BEGIN
        ROLLBACK;
        RAISERROR('Забагато невдалих спроб входу. Акаунт тимчасово заблоковано.', 16, 1);
    END
END;
GO