다양한 데이터베이스가 작동하기 때문에 각각의 권한들을 설정하기위해
Login -> system - > user -> Database
시스템에 접근하기위해선 로그인계정
데이터베이스에 접근하기위해선 사용자계정
Authentication (identification / password)
Authorization 시스템의 권한부여 Privileges
9-6
일반적으로 커버러스방식을 사용함
9-15
로그인 정보를 유저에 매핑을 해야 디비를 사용할 수 있다
9-16
디비의 소유주를 바꿀땐 alter authorization을 사용한다
9-18
사용자를 추가하려고 하면 권한이 없단다
시스템 사용자를 먼저 추가해준다
보안-로그인-우클릭-사용자추가 에서 고급-지금찾기를 하면 시스템 아이디들이 나오지만 실습예제를 따라 이렇게 추가하지는 않고..
다음과같이 sql서버인증으로 사용자를 추가해준다
그럼 이제 로그인-유저 매핑이 된다
9-20
웹서버에서 아디/패스로 로그인 impersonates
웹서버에서 디비서버로 접근할때는 delegation(권한위임) -> 이게 안되기 때문에
이와같은 더블홉 문제를 해결하기위해 ad (커버로스 프로토콜 = 인증서버)
9-24
새로운 아이디가 생길때마다 고유식별자sid를 발급받는다
리스토어,복원,디태취,어태취할때 sid가 달라지게 된다. sid mis-matched
해결방법
1 해당 로그인이 만들어질때 sid를 지정 (best option)
2 resotore할때 수정할 수 있음
*미스매치 해결방법 실습
세번째칸 sid가 다른것을 확인
sid를 맞추는 쿼리
-사용자 계정으로 접속하는 방법을테스트 인스턴스1에서 확인을하면 windows 인증모드만 되어있다
새로운 ssms에서 아이디로 접속하려고하면 sql server 인증으로 접근이 안된다
인스턴스1에서 sql server 인증으로 바꿔보자
그리고 재시작을 해줘야한단다
이제 ssms2에서 접근이 된다.
Module 10. Assigning Server and Database Roles
10-4
권한을 부여할때는 :GRANT
접근을 불가할때는 :DENY
권한을 회수할때는 :REVOKE
10-6
권한 할당을 보려면 SYS.SERVER_PERMISSIONS VIEW 쿼리하라
10-7 SERVER FIXED ROLES
보안-sa-우클릭속성에서 역할을 확인하자
10-9 PUBLIC SERVER ROLE
1 디비를 볼 수 있는 권한
2 커넥트 퍼미션
MOD10이라는 사용자는 BULK작업을 수행하게 하는 실습
보안 - 서버역할 사용자 추가에서 -추가 - 찾기 에서 지정한 사용자 선택
추가한 사용자에서 역할지정
-overview of fixed database roles실습
타겟이 되는 사용자의 속성에서 어떤 역할을 줄지 지정한다
데이터를 읽고 쓰는 권한을 받았기 때문에 셀렉트가 된다
10-15
sa는 sql를 설치한 어드미니스레이터가 갖는 최고 권한
자동으로 sysadmin 역할을 갖고, dbo 계정에 자동 맵핑이 된다
dbo는 계정이다
10-21 실습 user-defined database roles
11-8 SECURING TABLES AND VIEWS
관계를 맺은 테이블간의 참조무결성 , 참조(REFERENCES)
11-9 COLUMN LEVEL SECURITY
컬럼(열)만 나와 지게 권한 설정을 하는 설정
11-10 WITH GRANT OPTION
다른 사용자에게 권한을 줄 수있는 옵션
REVOKE CASCADE 명령은 B가 C에게 준 권한도 뺐는다
11-20
프로시저 - 익스큐트
11-22
사용자는 스키마와 동일하게 보시면 됩니다.
사용자 dbo / guest / sys /information_schema
server.database.owner.object (owner -> schemas)
-기본 요약
db를 쓸려면
1 시스템을 통과
2 데이터베이스
3 스키마(table,view,function,procedure,queue,synonym,type,xml schema collection)
9,10,11 은.. 시스템에 접근하기위해 login하고 db에 접근하기위해 user로 접근하고 필요하다면 데이터베이스 단위 테이블 단위 스키마 단위로 권한을 설정할 수 있다
Module12 Auditing SQL Server Environments
12-3
DB를 사용하는 것에 대한 감사를 할수 있다.
12-5 C2 Audit Mode
TCSEC - > Common Criteria 로 대체
sql은 C2 레벨의 감사를 해준다
12-7
common criteria , iso 표준
12-8
트리거를 이용해서 db에 무슨짓을 하는지 잡아낼수있다.
문제
1 퍼포먼스가 떨어질수있다
2 셀렉트하는것은 트리거가 잡을 수 없다
3 실무에서는 트리거를 많이 사용하진 않는다
12-9
sql trace정도만 사용해도된다
12-14 sql server audit
2008부터 나온기능
뭔가 db에 이벤트가 일어나면 시스템 기반의 로깅 트래킹
12-16 audit actions
뭔가 이슈가 생겼을대 내가 하고싶은 작업을 묶은것을 말한다
12-17
윈도우 어플리케이션 이벤트 로그
윈도우 시큐리티 이벤트 로그에 남길 수 있음
12-24
-- 6231B Demonstration 2A (Using SQL Server Audit)
-- Step 1: Use the master database
USE master;
GO
-- Step 2: Create a SQL Server Audit and define its target as the Windows
-- application log
CREATE SERVER AUDIT MarketDevLog
TO APPLICATION_LOG
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO
-- Step 3: Change to the MarketDev database
USE MarketDev;
GO
-- Step 4: Create a database audit specification for SELECT
-- activity on the Marketing schema
CREATE DATABASE AUDIT SPECIFICATION MarketingSelectSpec
FOR SERVER AUDIT MarketDevLog
ADD (SELECT ON SCHEMA::Marketing BY public);
GO
-- Step 5: Query the sys.server_audits system view.
-- Scroll to the right and note the available columns. In
-- particular, note the is_state_enabled column.
SELECT * FROM sys.server_audits;
GO
-- Step 6: Change to the master database
USE master;
GO
-- Step 7: Enable the server audit
ALTER SERVER AUDIT MarketDevLog WITH (STATE = ON);
GO
-- Step 8: Change to the MarketDev database
USE MarketDev;
GO
-- Step 9: Enable the MarketingSelectSpec audit specification
ALTER DATABASE AUDIT SPECIFICATION MarketingSelectSpec
WITH (STATE = ON);
GO
-- Step 10: Query the sys.server_audits and
-- sys.database_audit_specifications and
-- sys.database_audit_specification_details system views.
-- Note that the audit is now started and scroll to
-- see the details provided for the audit specification.
SELECT * FROM sys.server_audits;
SELECT * FROM sys.database_audit_specifications;
SELECT * FROM sys.database_audit_specification_details;
GO
-- Step 11: Generate an auditable event by querying a table
-- in the Marketing schema. Also execute a query
-- that should not be audited.
SELECT * FROM Marketing.PostalCode;
GO
SELECT * FROM DirectMarketing.City;
GO
12-29 감사 기록 남기가
-- 6231B Demonstration 3A (Viewing the output of a file-based audit)
-- Step 1: Use the master database
USE master;
GO
-- Step 2: Open Windows Explorer and create a new folder L:\SQLAudit\AuditLog
-- Step 3: Create a SQL Server Audit and define its target as the folder
-- created in step 2.
//파일저장 공간
CREATE SERVER AUDIT MarketDevLogToFile
TO FILE (FILEPATH = 'c:\mssql');
GO
-- Step 4: Enable the server audit
//서버감사 활성화
ALTER SERVER AUDIT MarketDevLogToFile WITH (STATE = ON);
GO
-- Step 5: Change to the MarketDev database
USE MarketDev;
GO
-- Step 6: Create a database audit specification for SELECT
-- activity on the DirectMarketing schema
CREATE DATABASE AUDIT SPECIFICATION DirectMarketingSelectSpec
FOR SERVER AUDIT MarketDevLogToFile
ADD (SELECT ON SCHEMA::Marketing BY public);
GO
-- Step 7: Enable the DirectMarketingSelectSpec audit specification
ALTER DATABASE AUDIT SPECIFICATION DirectMarketingSelectSpec
WITH (STATE = ON);
GO
-- Step 8: Generate an auditable event by querying a table
-- in the DirectMarketing schema. Also execute a query
-- that should not be audited.
SELECT * FROM DirectMarketing.City;
GO
SELECT * FROM Marketing.PostalCode;
GO
-- Step 9: Query the contents of the audit file
SELECT * FROM
sys.fn_get_audit_file('c:\mssql\*',
NULL,NULL);
GO
Module13. Automating SQL Server 2008 R2 Management
13-3
자동화기법
13-4
왜 자동화?반복되는 작업들을 줄이기위해
proactive management(전방위적 관리)
1 모니터링 퍼포먼스
2 문제가 발생할 가능성을 줄인다
13-11 job step types
-including
1 transact -sql
2 command line
3 powershell script
-주기
1 한번/2 반복/3 에이전트 시작될때
13-17 scripting jobs
13-19 잡 히스토리보기 msdb에 저장되어있음. 그래서 msdb(작업)도 백업을 하라는 것임
시스템(master db)
13-22 트러블슈팅 잡
1 서비스 세팅값 확인
2 msdb 확인
3 잡 히스토리 리뷰
4 잡이 활성화인가?
5 스케줄이 제대로 걸렸는가?
6 스케줄이 활성화인가?
필요하다면 오브젝트에 종속적인 내용도 확인해야한다.
-mssql 테이블의 타입
1 clustered index table
2 non-clustered index table
