环境搭建
环境依赖 libevent
  
说明
    

     
libevent是一个事件通知库，适用于windows、linux、bsd等多种平台，内部使用select、epoll、kqueue、IOCP等系统调用管理事件机制。著名分布式缓存软件memcached也是基于libevent，而且libevent在使用上可以做到跨平台，而且根据libevent官方网站上公布的数据统计，似乎也有着非凡的性能。
coturn 的底层网络部分依赖libevent. 所以需要先安装libevent2
    
下载地址 http://libevent.org/
安装
    

     
./configure make make install
    
turnserver 搭建
  
说明
    

     
由于国内通讯都被监控，所以WebRTC是不能直接P2P的，都需要中继服务作为转发的，所以在国内使用WebRTC是需要搭建中继服务。
谷歌推荐的开源穿透服务器，包含trun和stun服务。
    
下载地址 http://turnserver.open-sys.org/downloads/
安装
    

     
./configure
make
make install
cp turnserver-4.5.0.6/examples/etc /etc/turnserver.conf
    
目录结构
bin
  

   
turnadmin turnutils_peer turnutils_stunclient
turnserver turnutils_rfc5769check turnutils_uclient
turnserver 就是我们需要的服务器.
turnadmin 用来管理账户.
turnutils_stunclient 用于测试stun服务
turnutils_uclient 用于测试turn服务. 模拟多个UDP,TCP,TLS or DTLS 类型的客户端
  
example 目录主要是示范如何配置和使用turn. 包含一些测试用例.
  

   
example/etc 下是pem证书和conf配置文件
example/var/db 下是sqlite的db库. 用于示范数据库的格式.
turnadmin turnutils_peer turnutils_stunclient
coturn 支持三种配置. 命令行, conf文件和数据库. 数据库支持sqlite, MySQL, postgresql, MongoDB, Redis.
  
examples\scripts 下一些测试用例:
  

   
loadbalance 示范如何进行负载均衡. 设置一个master turn server 然后配置若干个slave turn server.
longtermsecure 示范如何使用long-term 验证
longtermsecuredb 与 longtermsecure 类似, 不过是从数据库配置
shorttermsecure 示范如何使用short-term验证.
restapi 示范了web方面的使用.
  
配置文件说明

 
阅读地址

启动
配置
  

   
listening-ip=外网IP
listening-port=3478
tls-listening-port=5349
relay-ip=外网IP
external-ip=外网IP
relay-threads=50
lt-cred-mech
user=veln:密码 sqlite配置时不需要该配置
userdb=/etc/turnuserdb.conf sqlite配置时配置db路径
pidfile=/var/run/turnserver.pid
log-file=/var/run/turnserver.log
  
密码生成方式
  

   
sqlite 存储密码 turnadmin -k -u -r -p //turnadmin -k -a -u veln -r veln.cn -p veln-bek-pass
  
  

   
生成密码 turnuserdb.conf
这个文件可以用作长期用户帐户存储凭证机制。
钥匙必须由turnadmin实用程序生成。键值取决于用户名称、领域和密码:
turnadmin -k -u ninefingers -r north.gov -p youhavetoberealistic
输出是: 0xbc807ee29df3c9ffa736523fb2c4e8ee ('0x'开始的关键是区分从密码的关键。如果它有0x,那么它是一个关键,否则这是一个密码)。
相应的用户帐号在userdb文件中是：ninefingers:0xbc807ee29df3c9ffa736523fb2c4e8ee 或者是这样(不安全的):ninefingers:youhavetoberealistic
  
启动
  

   
turnserver -L 外网IP -c /etc/turnserver.conf -o -a -b /etc/turnuserdb.conf -f -r veln.cn
sqlite 启动 turnserver -L 外网IP -c /etc/turnserver.conf -o -a -f -r veln.cn
服务器针对turn端口要开发三种端口协议：tcp、udp、sctp
http://外网IP:3479 访问出现 turn server 表示暂时安装成功，但不一定代表就可以使用 真正测试地址 测试成功后，表示turnserver 搭建成功了
  
注意

 
由于启动时候需要绑定外网IP指定端口(3478)，所以不能用内网服务器进行外网IP地址端口转发，这样无法启动成功

 
 SQL Server auditing has gone from a nice to have to a legal requirement, especially following new legislation like HIPAA and GDPR. Organizations are now tasked with auditing access to records, reporting suspicious and potentially malicious activity, forensically auditing data changes, as well are tracking login attempts, security changes and much more. 
 
 SQL Server审核已经从一个好的要求变成了一个法律要求，尤其是在遵循诸如HIPAA和GDPR之类的新法规之后。 现在，组织的任务是审核对记录的访问，报告可疑和潜在的恶意活动，对数据更改进行取证审核以及跟踪登录尝试，安全性更改等等。 
 
 
 SQL Server auditing can be broken into several techniques: 
 
 
 SQL Server审核可以分为几种技术： 
 
 
Manual auditing – this might involve a set of queries and possibly reports to track activity per table, transactions by users, recent changes to sensitive tables etc. But, in addition to being time consuming, it will be virtually impossible to scale this to all possible auditing events 
 
 
 手动审核 -这可能涉及一组查询和可能的报告，以跟踪每个表的活动，用户的交易，对敏感表的最新更改等。但是，除了耗时之外，几乎不可能将其扩展到所有可能审核事件 
 
 
SQL Server Extended Events – as the ultimate replacement for SQL Server profiler and traces, extended events offer several advantages including built in GUI tools and potentially better performance. Extended events can audit a wide range of actions, but suffers from some deficiencies like not being able to provide information on what was deleted or inserted. Also, there is no means for before-and-after auditing to compare new and old values for updates. 
 
 
 SQL Server扩展事件 –作为SQL Server事件探查器和跟踪的最终替代，扩展事件具有多个优点，包括内置的GUI工具和可能更好的性能。 扩展事件可以审核各种各样的操作，但是存在一些缺陷，例如无法提供有关已删除或插入的内容的信息。 同样，也没有办法进行前后审核来比较新旧值之间的更新。 
 
 
SQL Server triggers – these have been a staple for years. They can be set up easily and track a variety of information. Triggers lend themselves to full customization allowing users to build their own auditing information repositories. Triggers are an intrusive technology and can throw errors to your client applications when they break. They aren’t recommended for high throughput or bulk insert tables/operations and maintenance of a trigger based layer can be time consuming. 
 
 
 SQL Server触发器 –多年来一直是主要触发器 。 可以轻松设置它们并跟踪各种信息。 触发器可以进行完全自定义，从而允许用户构建自己的审核信息存储库。 触发器是一种侵入性技术，在中断客户端应用程序时会引发错误。 不建议将它们用于高吞吐量或批量插入表/操作，并且基于触发器的层的维护可能很耗时。 
 
 
 Note: To automatically add template based trigger based DML (and DDL) auditing to SQL Server databases – see ApexSQL Trigger 
 
 
 注意：要将基于模板的基于触发器的DML（和DDL）审核自动添加到SQL Server数据库，请参阅ApexSQL触发器 
 
 
SQL Server transaction logs – The transaction log in SQL Server is like the black box of an airplane. It will record everything that occurs, which lends itself well to purposes like auditing. There is no additional overhead as this is already a built in process in SQL Server. But log files are notoriously difficult to read and even when they can be the data isn’t organized for easy consumption and much of it is in hexadecimal format. See the article Read a transaction log, for more information on various solutions 
 
 
 SQL Server事务日志 – SQL Server中的事务日志就像飞机的黑匣子。 它将记录所有发生的事情，非常适合审计之类的目的。 没有额外的开销，因为这已经是SQL Server中的内置过程。 但是众所周知，日志文件很难读取，即使它们是可以理解的，也不是为了方便使用而组织的数据，而且大多数文件都是十六进制格式的。 请参阅文章阅读事务日志 ，以获取有关各种解决方案的更多信息。 
 
 
SQL Server Profiler and SQL Server traces – this has been the go to technology for auditing in SQL Server for years but it tends to produce voluminous amounts of information and is slated to be deprecated. Read more about this here Is this the end of SQL Profiler? 
 
 
 SQL Server Profiler和SQL Server跟踪 -多年来，这一直是SQL Server中用于审核的技术，但它往往会产生大量信息，并且已被弃用。 在这里阅读有关此内容的更多信息SQL Profiler的结尾了吗？ 
 
 
SQL Server Audit – this feature has been around since SQL Server 2008 and offers rudimentary, “aggregate” auditing capabilities like who made a change and when, but doesn’t offer other information, which today would be considered essential, like what was actually changes 
 
 
 SQL Server审核–此功能自SQL Server 2008起就存在，并提供基本的“汇总”审核功能，例如谁进行了更改以及何时进行更改，但不提供其他信息，而今天这些信息被认为是必不可少的，例如实际更改了什么。 
 
 
Change tracking – this SQL Server feature is a step above SQL Server Audit but requires some experience and understanding of the underlying table structures and only tracks changes to the primary key, making it not so useful for anything other than very superficial audits 
 
 
 变更跟踪–此SQL Server功能是SQL Server审核之上的步骤，但需要一定的经验和对底层表结构的理解，并且仅跟踪对主键的更改，因此除非常肤浅的审核外，它没有其他用途 
 
 
Change data capture (aka CDC) – this is SQL Server’s improvement on Change tracking and is available in the Standard edition since SQL Server 2016. CDC offers much better information and auditing capabilities than Change tracking but lacks a user interface to make viewing and processing the information easy. T-SQL knowledge will be required to query the tables and pull information 
 
 
 变更数据捕获（aka CDC） –这是SQL Server对变更跟踪的改进，自SQL Server 2016起在标准版中提供。CDC比变更跟踪提供更好的信息和审核功能，但缺少用于查看和处理变更的用户界面。信息容易。 需要具备T-SQL知识才能查询表和提取信息 
 
 
Temporal tables – This is another feature SQL Server has introduced that offers a complimentary solution to CDC. With temporal tables you can see a full history of changes and it can also be used for recovery purposes. The feature does require the creation of history tables for each auditing table and requires interaction with T-SQL to view results. See Concept and basics of Temporal tables in SQL Server 2016 for more information on this feature
 
 
 时态表–这是SQL Server引入的另一个功能，它为CDC提供了一个免费的解决方案。 使用临时表，您可以查看更改的完整历史记录，也可以将其用于恢复目的。 该功能确实需要为每个审核表创建历史表，并且需要与T-SQL交互才能查看结果。 有关此功能的更多信息，请参见SQL Server 2016中的时态表的概念和基础。 
 
 
 Which solution or combination of solutions is appropriate? It depends on your auditing requirements, time for setting up and maintain audit trails, as well as storage, security and reporting requirements 
 
 
 哪种解决方案或解决方案组合合适？ 这取决于您的审核要求，建立和维护审核记录的时间以及存储，安全性和报告要求 
 
 
Manual auditing 
 
 
 人工审核 
 
 
 Running scripts to audit for certain events or activity is something most DBAs have done, at one time or another. The collection of ad hoc scripts can build over time to a powerful toolkit especially combined with native functionality like CDC and Temporal tables. Many such open source scripts can be found on the internet. But in general such solutions are free only if your DBA works on a Pro Bono basis. Otherwise, they can be time consuming and costly to produce and maintain, especially if the owner of this solution leaves the company or changes to another position. So we’ll forgo discussions of such manual solutions. 
 
 
 大多数DBA一次或多次运行脚本来审核某些事件或活动。 临时脚本的集合可以随着时间的推移构建为功能强大的工具包，尤其是与CDC和Temporal表之类的本机功能结合在一起。 在互联网上可以找到许多这样的开源脚本。 但是通常，仅当您的DBA在Pro Bono基础上工作时，此类解决方案才是免费的。 否则，生产和维护它们可能会很耗时且成本很高，尤其是如果此解决方案的所有者离开公司或调任另一个职位时，尤其如此。 因此，我们将不再讨论此类手动解决方案。 
 
 
Utilizing SQL Server Extended Events 
 
 
 利用SQL Server扩展事件 
 
 
 SQL Server Audit is a SQL Server feature, first introduced in the version 2008 that uses SQL Server Extended Events to audit SQL Server actions. It enables auditing different actions, providing much granularity in the setup process and covering a wide range of the SQL Server activity 
 
 
 SQL Server审核是一项SQL Server功能，在2008版中首次引入，该功能使用SQL Server扩展事件来审核SQL Server操作。 它使您可以审计不同的操作，在设置过程中提供很多粒度，并涵盖了广泛SQL Server活动 
 
 
 To create a new SQL Server Audit object: 
 
 若要创建一个新SQL Server审核对象： 
 
 
 Expand Security and right-click Audits in SSMS 
 展开“安全性”，然后右键单击“ SSMS”中的“审核” 
Select New Audit
 
 选择新审核 
 
 
   

    
   
 
 You will need to create a name for the audit, and then indicate whether to store the audit data in an application security event log, event log or a file. Finally, indicate a location for the audit file
 
 您将需要为审核创建一个名称，然后指示是将审核数据存储在应用程序安全事件日志，事件日志还是文件中。 最后，指定审核文件的位置 
 
 
   

    
   
 
 Click OK and your audit will appear in the Audits node of the Object Explorer 
 单击“确定”，您的审核将出现在“对象资源管理器”的“审核”节点中 
 By default, it’s disabled. The disabled status is indicated by a red arrow. Right-click and select Enable Audit, to enable
 
 默认情况下，它是禁用的。 禁用状态由红色箭头指示。 右键单击并选择启用审核 ，以启用 
 
 
   

    
   
 
 Depending on whether you want to audit the activity on an entire SQL Server instance or just a particular database you will choose between Server Audit Specification or Database Audit Specification 
 根据是要审核整个SQL Server实例上的活动还是只审核特定数据库上的活动，您可以在服务器审核规范或数据库审核规范之间进行选择。 
 For a Database Audit Specification, expand the node of the database to audit, go to Security, right-click Database Audit Specifications and select New Database Audit
 
 对于数据库审核规范，展开要审核的数据库节点，转到“ 安全性” ，右键单击“ 数据库审核规范”，然后选择“ 新建数据库审核”。 
 
 
   

    
   
 
 In the Create Database Audit Specification dialog, indicate the specification name, associate the specification with the audit object created in the previous step, specify the activity to audit in the Audit Action Type. For auditing a particular database, indicate the database, object, or schema as an Object Class, the name of the audited object, and the audited login
 
 在“创建数据库审核规范”对话框中，指定规范名称，将规范与上一步中创建的审核对象相关联，在“审核操作类型”中指定要审核的活动。 要审核特定的数据库，请将该数据库，对象或模式指示为“对象类”，被审核对象的名称以及被审核登录名 
 
 
   

    
   
 
  
 
 
 In the drop-down list for Audit Action Type, you can see all actions that can be audited using SQL Server Auditing 
 
 
 在“审核操作类型”下拉列表中，您可以查看可以使用SQL Server审核进行审核的所有操作。 
 
 
 
 

  
 
 
 
 In this dialog, you will specify the user accounts to be monitored. 
 
 
 在此对话框中，您将指定要监视的用户帐户。 
 
 
 Database audit specifications are disabled, by default. To enable them, select this option in the context menu 
 
 
 默认情况下，数据库审核规范是禁用的。 要启用它们，请在上下文菜单中选择此选项。 
 
 
 
 

  
 
 
 
 Now, all DELETE statements executed against the Person.BusinessEntityAddress will be audited and inserted into files the names of which start with Audit-, such as Audit-AW2012Test_9D93CA4A-8B90-40B8-8B0B-FCBDA77B431D_0_130161593310500000.sqlaudit, and stored in E:\ 
 
 
 现在，将审核对Person.BusinessEntityAddress执行的所有DELETE语句，并将其插入以Audit-开头的名称的文件中，例如Audit-AW2012Test_9D93CA4A-8B90-40B8-8B0B-FCBDA77B431D_0_13016159331050000000000.sqlaudit，并将其存储在E：\ 
 
 
 For high volume databases, it is considered a good practice to save audited info to a file. This file can’t be opened directly though, even with a hex editor. To view it, use fn_get_audit_file 
 
 
 对于高容量的数据库，将审核的信息保存到文件中是一种好习惯。 即使使用十六进制编辑器，也无法直接打开此文件。 要查看它，请使用fn_get_audit_file 
 
 
 For example: 
 
 
 例如： 
 
 
SELECT event_time,action_id, statement, database_name, server_principal_name
  FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT);

 
 shows the following results: 
 
 
 显示以下结果： 
 
 
 
 

  
 
 
 
 The results will include columns but not the actual deleted data. The user, who made the deletion, and when the deletion was made are the main audit elements here, which is a disadvantage for users who need more comprehensive audit data. 
 
 
 结果将包括列，但不包括实际删除的数据。 进行删除的用户以及进行删除的时间是此处的主要审核元素，对于需要更全面审核数据的用户而言，这是不利的。 
 
 
 Other disadvantages include: 
 
 
 其他缺点包括： 
 
 
 SQL Server Audit uses the resources of the audited SQL Server itself, which can degrade performance 
 SQL Server审核使用审核SQL Server本身的资源，这可能会降低性能 
 It is difficult to comprehensively manage multiple instances and consolidate the audit data. 
 难以全面管理多个实例并合并审核数据。 
 There is a lot of wet-work involved in managing, analyzing and archiving audit data, whether in a file or log, and necessitates manual effort for importing, archiving and reporting. 
 管理，分析和归档审核数据（无论是文件还是日志）涉及很多工作，并且需要人工来进行导入，归档和报告。 
 This feature isn’t available in the standard version of SQL Server until SQL Server version 2016. 
 在SQL Server 2016版之前，此功能在SQL Server的标准版本中不可用。 
 
 使用SQL Server触发器 (Using SQL Server triggers )
 
 
 SQL Server triggers are perhaps as old as SQL Server itself (don’t quote me on that). As per their name, they are artifacts in the SQL Server engine that “fire” on a particular even such as the insertion of a new record. Triggers exist for both DML (data) and DDL (schema) operations and because they are T-SQL based can be fully customized and integrated into your database directly. 
 
 
 SQL Server触发器可能与SQL Server本身一样古老（请不要在此引用我）。 就像它们的名字一样，它们是SQL Server引擎中的构件，即使在插入新记录等操作时也会“触发”特定条件。 DML（数据）和DDL（架构）操作都存在触发器，并且由于它们基于T-SQL，因此可以完全自定义并直接集成到数据库中。 
 
 
 A trigger can exist for only one table, so you will need to create and maintain at least one trigger for each “sensitive” table you wish to audit. As triggers are user-defined objects T-SQL must be written to specify what data to capture and ultimately what to do with it. A table or tables will need to be created, to which the triggers will point and deliver the audited information they have captured. Then this audit trail information can be queried and reported on. 
 
 
 一个触发器只能用于一个表，因此您需要为要审核的每个“敏感”表创建并维护至少一个触发器。 由于触发器是用户定义的对象，因此必须编写T-SQL来指定要捕获的数据以及最终要使用的数据。 将需要创建一个或多个表，触发器将指向该表并传递它们已捕获的审核信息。 然后可以查询并报告此审计跟踪信息。 
 
 
 To illustrate we’ll use an example. In our case, a trigger that is fired after a record was inserted into the Person.Person table inserts a table name, time and date when the record was inserted and the user name used to insert the record into a dbo.Repository table should look like this: 
 
 
 为了说明，我们将使用一个示例。 在我们的例子中，将记录插入Person.Person表后触发的触发器将插入表名，插入记录的时间和日期以及用于将记录插入dbo的用户​​名。像这样： 
 
 
CREATE TRIGGER PersonPerson_I
ON Person.Person
AFTER INSERT 
AS
   INSERT INTO dbo.repository (
TABLE_NAME,
		TABLE_SCHEMA,
		AUDIT_ACTION_ID,
		MODIFIED_BY,
		MODIFIED_DATE,
		[DATABASE]
	)
	values(
		'Person',
		'Person',
		'Insert',			
		SUSER_SNAME(),
		GETDATE(),
		'AdventureWorks2012'
	) GO

 
 Although built-in and effective, triggers are difficult and time consuming to create, as you may need hundreds or more, and to maintain, as they will need to be updated each time the underlying table is changed. The use of 3rd party tools to automate the creation of triggers based on templates can help 
 
 
 尽管触发器是内置的且有效的，但创建触发器却很困难且耗时，因为您可能需要数百个甚至更多，并且要维护触发器，因为每次更改基础表时都需要更新触发器。 采用第三方工具来自动化基于模板可以帮助创建触发器 
 
 
ApexSQL Trigger is a SQL Server database auditing tool for capturing data and schema changes  including who made the change, which objects were affected, when the change was made as well as the information on the SQL login, application and host used to make the change. It stores all captured information in a central repository table. Audit data can be reported on and exported. Triggers are based on templates that can be customized. Once done, each trigger will be created perfectly, with no errors and thousands of triggers can be created in seconds. If the underlying database changes, affected triggers can easily be refactored. 
 
 
 ApexSQL Trigger是一个SQL Server数据库审核工具，用于捕获数据和架构更改，包括进行更改的人员，受影响的对象，进行更改的时间以及用于进行更改SQL登录名，应用程序和主机的信息。 它将所有捕获的信息存储在中央存储库表中。 审计数据可以报告和导出。 触发器基于可以自定义的模板。 完成后，将完美无误地创建每个触发器，并且可以在几秒钟内创建成千上万个触发器。 如果基础数据库发生更改，则可以轻松地重构受影响的触发器。 
 
 
 To create a SQL Server database trigger based audit trail with ApexSQL Trigger, follow these steps 
 
 若要使用ApexSQL触发器创建基于SQL Server数据库触发器的审核跟踪，请按照下列步骤操作 
 
 
ApexSQLApexSQL  Trigger 触发器 
 Connect to the database to audit
 
 连接到数据库进行审核 
 
 
   

    
   
 
 In the main grid, select the table(s) to audit
 
 在主网格中，选择要审核的表 
 
 
   

    
   
 
  
 
 In the Columns pane, select the column(s) to audit
 
 在“列”窗格中，选择要审核的列 
 
 
   

    
   
 
 Check the transactions to audit – including Insert, Update and/or Delete 
 检查要审核的事务–包括插入，更新和/或删除 
 Repeat the steps 3 to 5 for all tables you want to audit 
 对要审核的所有表重复步骤3至5 
Create triggers 创建触发器 
 The script that generates the specified triggers is shown in the Script dialog. Check it out and press F5 to execute it against the database to create the triggers.
 
 “脚本”对话框中显示了生成指定触发器的脚本。 签出并按F5对数据库执行以创建触发器。 
 
 
   

    
   
 
  
 
 
 Once the triggers are created, they will be fired for every subsequent INSERT, DELETE and UPDATE executed against the table, from that point on, and the details of the operation are stored into pre-built user-defined tables. 
 
 
 创建触发器之后，从该点开始，将针对针对该表执行的每个后续INSERT，DELETE和UPDATE触发该触发器，并将操作的详细信息存储到预先构建的用户定义表中。 
 
 
 Reporting is easy with built-in reports, and the audit tables can be queried directly as well 
 
 
 内置的报告使报告变得容易，审核表也可以直接查询 
 
 
 
 

  
 
 
 
 
 
  
 
 
 Triggers are very powerful building blocks of a good SQL Server audit solution but downsides include the amount of time to create and maintain them. For high transaction tables, triggers can also add unneeded performance overhead. 
 
 
 触发器是好SQL Server审核解决方案的非常强大的构建块，但是缺点包括创建和维护它们的时间量。 对于高事务表，触发器还会增加不必要的性能开销。 
 
 
 读取交易记录 (Reading transaction logs)
 
 
 If you ever wanted a one-stop-shop for comprehensive SQL Server transaction auditing information, the transaction log would probably be the first place you would look. By definition, it must maintain a history of everything executed against SQL Server as every data and schema change is automatically added to the online transaction log. Simply gaining access to this information can act as an audition solution in and of itself. 
 
 
 如果您想要一站式服务以获取全面SQL Server事务审核信息，则事务日志可能是您首先要查找的地方。 根据定义，它必须保留对SQL Server执行的所有操作的历史记录，因为每个数据和架构更改都会自动添加到联机事务日志中。 仅仅获得对该信息的访问本身就可以充当试听解决方案。 
 
 
 The challenge is that the transaction log was never meant for human eyes. Open transaction log data sources like the online log, detached logs or log backups is challenging but once opened, reading them is even harder. Several options exist to read the transaction log including fn_dblog, fn_dump_dblog, and DBCC PAGE. For more information on these options see the article: Read a transaction log 
 
 
 面临的挑战是，交易日志从来就不是人眼所见。 开放的事务日志数据源（例如联机日志，分离的日志或日志备份）具有挑战性，但是一旦打开，读取它们就更加困难。 存在几种读取事务日志的选项，包括fn_dblog，fn_dump_dblog和DBCC PAGE。 有关这些选项的更多信息，请参见文章： 阅读事务日志 
 
 
 Each of these solutions have some pretty severe limitations in that the information they display is unorganized and difficult to process. Some information is presented as hexadecimal, which is hard/impossible to read and auditing for things like Updates and Blogs is also very challenging 
 
 
 这些解决方案中的每一个都有一些非常严重的局限性，因为它们显示的信息是无组织的并且难以处理。 有些信息以十六进制表示，很难/不可能读取，对于诸如更新和博客之类的内容的审核也非常具有挑战性 
 
 
 Fortunately, there is a 3rd party tool that was created to abstract the complexity of decrypting log files, from users, as it can read the files and convert it into helpful and easy to read information, presented in a grid that allows for additional manipulation 
 
 
 幸运的是，这是为了解密日志文件，从用户，因为它可以读取这些文件，并将其转换成有用的，易于阅读的信息，在网格中提出，允许额外的操作的抽象复杂的第三方工具 
 
 
 To read transaction logs, use a SQL Server transaction log reader such as ApexSQL Log. It audits, reverts or replays data and object changes that have affected a database, including those that have occurred before ApexSQL Log installation. It also captures information on the user, application and host used to make each change 
 
 要读取事务日志，请使用SQL Server事务日志读取器，例如ApexSQL Log 。 它审核，还原或重播影响数据库的数据和对象更改，包括在ApexSQL Log安装之前发生的更改。 它还捕获有关用于进行每个更改的用户，应用程序和主机的信息 
 
 
ApexSQLApexSQL  Log 日志 
 Connect to the database you want to audit
 
 连接到要审核的数据库 
 
 
   

    
   
 
  
 
 In the Select SQL logs to analyze step, add the transaction log backups and detached transaction logs you want to read. Note that they have to form a full chain in order to provide successful auditing
 
 在“ 选择要分析SQL日志”步骤中，添加要读取的事务日志备份和分离的事务日志。 请注意，他们必须形成完整的链条才能提供成功的审核 
 
 
   

    
   
 
  
 
 Use the Filter setup options to narrow down the result set using the time, operation type, table’s name, user and other filtering options
 使用过滤器设置选项可以使用时间，操作类型，表的名称，用户和其他过滤选项来缩小结果集的范围 
 
   
 
   
 
 
   

    
   
 
  
 
Open 打开 
 The results are shown in the main grid and you can easily create undo and redo scripts, or export them into CSV, HTML, XML or SQL files and save on the hard disk
 
 结果显示在主网格中，您可以轻松创建撤消和重做脚本，或将它们导出为CSV，HTML，XML或SQL文件并保存在硬盘上 
 
 
   

    
   
 
  
 
 
 The advantages of using the SQL Server transaction log as an auditing mechanism include: 
 
 
 使用SQL Server事务日志作为审核机制的优点包括： 
 
 
 no overhead, since there are no additional processes for capturing the audit information that can affect SQL Server performance. 
 没有开销，因为没有额外的过程来捕获可能影响SQL Server性能的审核信息。 
 an audit can be performed for a period of time even before the tool was installed 
 甚至可以在安装工具之前进行一段时间的审核 
 the tool can be set up to run unattended, and nightly to capture a continuous record of auditing events directly back into a SQL Server table for direct querying 
 该工具可以设置为在无人值守的情况下运行，并且每晚都可以将审核事件的连续记录直接捕获回SQL Server表中以进行直接查询 
 many value added features like filtering, sorting, reporting, exporting etc 
 许多增值功能，例如过滤，排序，报告，导出等 
 
 The disadvantages are: 
 
 
 缺点是： 
 
 
 a database has to be in the full recovery model, and a full chain of transaction logs must exist 
 数据库必须处于完整恢复模型中，并且必须存在完整的事务日志链 
 databases that use TDE or AlwaysEncrypted can’t be read 
 无法读取使用TDE或AlwaysEncrypted的数据库 
 not all actions that a user might want to audit are stored in a transaction log. For example, SELECT statements 
 并非用户可能要审核的所有操作都存储在事务日志中。 例如，SELECT语句 
 
 使用SQL Server Profiler和跟踪 (Using SQL Server Profiler and tracing)
 
 
 Technology exists in SQL Server to provide a running audit of every operation performed and event that occurs, in the form of SQL Server traces. Setting up the SQL Server Profiler to create such traces is a viable method to audit your databases but do the fact that the amount of data produced can be voluminous and that this technology will be deprecated by Microsoft, it isn’t a good long term solution 
 
 
 SQL Server中存在用于以SQL Server跟踪的形式对执行的每个操作和发生的事件进行运行审核的技术。 设置SQL Server Profiler来创建此类跟踪是审核数据库的一种可行方法，但事实是生成的数据量可能非常庞大，并且Microsoft将弃用该技术，这不是一个好的长期解决方案 
 
 
 Extended events are the successor to SQL Server profiling and promises a more viable technology, one that Microsoft has committed to for the future. Extended Events cover all of the events found by profiling but without producing GBs of audit files that must be processed 
 
 
 扩展事件是SQL Server配置文件的继任者，它承诺将提供一种更可行的技术，Microsoft对该技术进行了承诺。 扩展事件涵盖了通过分析发现的所有事件，但未产生必须处理的GB审计文件 
 
 
 The challenge is to cover SQL Servers which only support profiling and also those that only cover extended events, although there is an overlap of versions. A solution meant for profiling will have to be abandoned, as a company transitions to Extended events. Also, there is a lack of value added functionality like alerts, reporting, exports, customization, interface with productivity features etc that necessitates a lot of time to turn these baseline technologies into an enterprise auditing solution. 
 
 
 挑战是要覆盖仅支持概要分析SQL Server，以及仅覆盖扩展事件SQL Server，尽管版本存在重叠。 随着公司过渡到扩展事件，必须放弃用于分析的解决方案。 而且，缺少诸如警报，报告，导出，自定义，与生产力功能的接口等增值功能，这需要大量时间才能将这些基准技术转变为企业审核解决方案。 
 
 
 Fortunately, there is a 3rd party tool that can bridge the gap between profiling and extended events and puts a thick layer of value added features and functionality on these underlying technologies to provide a turn-key auditing solution, right out of the box 
 
 
 幸运的是，有一个第三方的工具，可以弥合剖析和扩展事件和看跌期权的增值功能厚厚的一层和功能上的这些底层技术之间的差距提供了交钥匙的审计解决方案，盒子的右出 
 
 
ApexSQL Audit is a SQL Server auditing tool built on SQL Server traces and extended events that provides “who saw what, when” type information. The ApexSQL Audit auditing ecosystem also includes fault tolerant auditing, centralized reporting, user friendly interface for setting auditing on more than 230 operations, and a temper-proof centralized repository for storing audit records and configuration safely. It configures traces according to configurable settings but can commence auditing immediately after install using its default configuration that covers most common auditing requests 
 
 ApexSQL Audit是基于SQL Server跟踪和扩展事件构建SQL Server审核工具，可提供“谁看到了什么，何时看到了”类型信息。 ApexSQL Audit审计生态系统还包括容错审计，集中报告，用于对230多个操作进行审计的用户友好界面以及用于安全存储审计记录和配置的防暴集中式存储库。 它根据可配置的设置配置跟踪，但是可以在安装后立即使用其默认配置（涵盖大多数常见审核请求）开始审核 
 
 
ApexSQL Audit ApexSQL审核 
 Click the ‘Add server’ in the Configure tab to select a server for audit
 
 单击“ 配置”选项卡中的“添加服务器”以选择要审核的服务器 
 
 
   

    
   
 
 Click the ‘Add database’ button to select a database for auditing, and select server or database operations you want to audit.
 
 单击“添加数据库”按钮以选择要审核的数据库，然后选择要审核的服务器或数据库操作。 
 
 
   

    
   
 
 
 Another option is by using the Advanced filter type: 
 
 
 另一种选择是使用“高级”过滤器类型： 
 
 
 
 

  
 
 
 
 Now, whenever any operation you selected is performed on audited SQL Server, a record will be saved in the central repository database 
 
 
 现在，无论何时在审核SQL Server上执行您选择的任何操作，记录都将保存在中央存储库数据库中 
 
 
 To see auditing records, you can use a built-in local reports 
 
 
 要查看审核记录，可以使用内置的本地报告 
 
 
 
 

  
 
 
 
 
 
  
 
 
ApexSQL Audit is truly an Apex predator in the auditing ecosystem. It offers a fully functional and feature rich interface that abstracts DBAs from extensive setup, configuration, maintenance and reporting requirements. It offers key enterprise features like fault tolerance and tamper resistance that allows you to meet stringent auditing requirements. With web based reporting, even remote users can view audit reports and participate in compliance audits. 
 
 ApexSQL Audit确实是审核生态系统中的Apex捕食者。 它提供了功能齐全且功能丰富的界面，可将DBA从广泛的设置，配置，维护和报告要求中抽象出来。 它提供了关键的企业功能，例如容错和防篡改，可满足严格的审核要求。 使用基于Web的报告，甚至远程用户也可以查看审核报告并参与合规性审核。 
 
 
Technique 
Advantages 
Disadvantages 
Suits best when 
 Manual auditing 
 Flexibility, customization 
 Coding 
 Development 
 Cost 
 Long implementation 
 Usually undocumented 
 Labor costs 
 Lack of continuity 
 A specific auditing solution is needed and no ready-made tool can be used 
 SQL Server Auditing 
 Flexibility 
 A large number of action types audited 
 Easy to set 
 No additional cost 
 No deleted, inserted, updated records 
 Can affect performance 
 Not available in all SQL Server versions and editions 
 Primitive interface 
 Difficult to configure over multiple tables, databases, instances 
 Enterprise, Developer or Evaluation SQL Server editions, when detailed auditing is not necessary, and no info about the records affected is needed 
 Using SQL Server triggers 
 Easy to set up 
 Customizable 
 Can be integrated into client software 
 Effort to create and maintain triggers 
 Can cause overhead in a high transaction database 
 Not all tables and DML operations need to be audited; auditing data need to be easily accessed and queried 
 Commercial software products that need to be self-audited 
 When auditing needs to be integrated into client apps 
 Reading transaction logs 
 No additional overhead 
 DML and DDL changes can be audited 
 Can show records that were affected 
 Row history and before-and-after 
 Difficult without a log reader 
 Not all actions are audited (security, queries, executes, logins, etc.) 
 High transaction environments with short downtime, where affected records must be seen, and changes rolled back 
 Using SQL Server Profiler and SQL Server traces 
 Flexible 
 Already available in SQL Server 
 Voluminous data store requirements 
 Will be deprecated 
 A wide range of SQL Server database actions must be audited. It’s recommended to have a tool designed to read traces, filter results and generate reports 
 
 
 技术 
 优点 
 缺点 
 最适合 
 人工审核 
 灵活性，定制化 
 
 编码 
 发展历程 
 成本 
 长期实施 
 
 通常无证 
 
 人工成本 
 
 缺乏连续性 
 
 需要特定的审核解决方案，并且不能使用现成的工具 
 SQL Server审核 
 灵活性 
 审核了大量的操作类型 
 容易设定 
 无需额外费用 
 
 没有删除，插入，更新的记录 
 
 会影响性能 
 
 并非在所有SQL Server版本和版本中都可用 
 
 原始介面 
 
 难以配置多个表，数据库，实例 
 
 企业版，开发人员版或评估版SQL Server，不需要进行详细的审核，并且不需要有关受影响的记录的信息 
 使用SQL Server触发器 
 
 易于设置 
 
 可订制 
 
 可以集成到客户端软件中 
 
 
 努力创建和维护触发器 
 
 可能导致高事务数据库中的开销 
 
 
 并非所有的表和DML操作都需要审核。 审核数据需要易于访问和查询 
 
 需要自我审核的商业软件产品 
 
 当审核需要集成到客户端应用程序中时 
 
 读取交易记录 
 
 没有额外的开销 
 
 可以审核DML和DDL更改 
 
 可以显示受影响的记录 
 
 行历史记录和前后 
 
 
 没有日志读取器的困难 
 
 并非所有操作都经过审核（安全性，查询，执行，登录等） 
 
 高事务环境，停机时间短，必须查看受影响的记录，并且回滚更改 
 使用SQL Server Profiler和SQL Server跟踪 
 
 灵活 
 
 在SQL Server中已经可用 
 
 
 大量数据存储需求 
 
 将不推荐使用 
 
 必须审核各种SQL Server数据库操作。 建议使用专门设计的工具来读取轨迹，过滤结果并生成报告 
 
 
 In this article we reviewed a host of SQL Server auditing techniques, described their advantages and disadvantages and provided walk-thrus for some solutions. 
 
 
 在本文中，我们回顾了许多SQL Server审核技术，描述了它们的优缺点，并提供了一些解决方案的介绍。 
 
 
 For several of these solutions, 3rd party solutions exist to add significant value to the option, at times making it more viable to consider. 
 
 
 对于几个这样的解决方案， 第三方解决方案存在显著值添加到选项，有时使它更可行的考虑。 
 
 
 For example, ApexSQL Trigger mitigates one of the main problems with triggers which is how time consuming they can be to create and maintain. 
 
 
 例如， ApexSQL触发器缓解了触发器的主要问题之一，即触发器创建和维护所花费的时间。 
 
 
 ApexSQL Log cuts through the myriad of problems and challenges normally associated with SQL Server transaction log reading. 
 
 
 ApexSQL Log消除了通常与SQL Server事务日志读取相关的众多问题和挑战。 
 
 
 And finally, ApexSQL Audit bridges the gap between SQL Server profiling and extended events, while providing major value add with advanced features and functionality 
 
 
 最后， ApexSQL Audit弥合了SQL Server分析和扩展事件之间的鸿沟，同时提供了具有高级功能的主要增值服务 
 
 
 参考资料 (References)
 
 
What are virtual log files in a SQL Server transaction log? 什么是SQL Server事务日志中的虚拟日志文件？ 
How to implement error handling in SQL Server 如何在SQL Server中实现错误处理 
Reading the SQL Server Transaction Log 读取SQL Server事务日志 
Temporal Table applications in SQL Data Warehouse environments SQL数据仓库环境中的临时表应用程序 
When to Use Temporary Tables vs. Table Variables 何时使用临时表与表变量 
Concept and basics of Temporal tables in SQL Server 2016 SQL Server 2016中的时态表的概念和基础 
Monitoring changes in SQL Server using change data capture 使用更改数据捕获监视SQL Server中的更改 
Logical SQL Server data replication 101 逻辑SQL Server数据复制101 
An overview of SQL Server database migration tools provided by Microsoft Microsoft提供SQL Server数据库迁移工具概述 
 

  
翻译自: https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/
 

原理：https://zhuanlan.zhihu.com/p/71025431
 
搭建Coturn服务器
 
一、生成ssl证书
 
生成自签名ssl证书即可
 
$ apt install openssl
$ cd /
$ mkdir cert
$ cd cert
$ openssl req -x509 -newkey rsa:2048 -keyout key.pem -out /cert/cert.pem -days 99999 -nodes
 
一、安装依赖库–libevent library
 
$ wget https://github.com/libevent/libevent/releases/download/release-2.1.12-stable/libevent-2.1.12-stable.tar.gz
$ tar -zxvf libevent-2.1.12-stable
$ cd libevent-2.1.12-stable
$ ./config
make
make install
 
二、安装Coturn
 
$ git clone https://github.com/coturn/coturn.git
$ cd coturn
$ ./config
$ make
$ make install
 
三、turnserver数据库配置
 
$ turnadmin -a -u robotai -p robotai -r X.X.X.X //-u 用户名 -p 密码 -r 外网ip
$ turnadmin -l //查看是否添加成功
 
如果添加成功，如下图所示：
 
 
四、修改turnserver配置文件
 
1、查看设备名称及内网ip
 
 $ ifconfig
 
2、在/usr/local/etc目录下有turnserver配置文件模板/usr/local/etc/turnserver.conf.default，配置自己的
 
turnserver.conf ：
 
listening-device=ens1f0 //设备名称
relay-device=ens1f0 //设备名称
listening-ip=#.#.#.# //内网ip
listening-port=3478 //端口
tls-listening-port=9000
relay-ip=#.#.#.# //内网ip
external-ip=X.X.X.X //外网ip
relay-threads=50
min-port=3480
max-port=3500
lt-cred-mech
Verbose
fingerprint
cert=/cert/cert.pem //ssl 公钥
pkey=/cert/key.pem  //ssl 私钥
realm=X.X.X.X //外网ip
no-loopback-peers
no-multicast-peers
mobility
no-cli
 
六、启动turnserver 并测试
 
1、启动
 
$ turnserver -o //-o表示后台运行
 
 
2、测试
 
浏览器打开https://webrtc.github.io/samples/src/content/peerconnection/trickle-ice/
 
输入 turn:ip:port、用户名、密码，点击Add Server，再点击Gather candidates收集候选地址,最后出现Done，表示完成穿透。
 
 
3、如果需要重启，先查看turnserver服务运行PID，然后再kill， 再重启
 
$ netstat -anp|grep turnserver //假设执行后查看到PID为123
$ kill 123
$ turnserver -o

1. 安装
 
> 
 sudo apt-get install coturn
 
2. 配置
 
turnserver.conf
 
路径: 
 > 
 /etc/turnserver.conf
 
内容: 
 > 
 listening-port=3478 
 tls-listening-port=5349 
 > 
 listening-ip=your ip 
 relay-ip=your ip 
 external-ip=your ip 
 > 
 server-name=your domain 
 realm=your domain 
 > 
 lt-cred-mech 
 userdb=/var/lib/turn/turndb
 
 
 
cert=/etc/turn_server_cert.pem
pkey=/etc/turn_server_pkey.pem

no-stdout-log
log-file=/var/tmp/turnserver.log

pidfile="/var/run/turnserver.pid"
 
 
字段含义:
 
  listening-port:  turnserver监听UDP/TCP端口,默认为3478;
  tls-listening-port:  turnserver监听TLS/DTLS端口,默认为5349,
      将TCP/UDP和TLS/DTLS分别定义监听端口是符合RFC5766规范的,但是通过配置两者能使用同一端口,不推荐;
  listening-ip:  中继服务器的监听IP地址,可以配置多个;
  relay-ip:  中继服务器的IP地址;
  external-ip:  外部IP,当中继服务器在NAT网络内部时指定,此处可以不添加;
  server-name:  服务器名称,用于OAuth认证,默认和realm相同;
  realm:  域名;
  userdb:  用于保存用户信息;
  cert/pkey:  自签名证书相关;
 
注意事项:
 
1) coturn使用了SQLlite作为存储,请保证userdb的正确配置(userdb=/var/lib/turn/turndb),不需要添加turnuserdb.conf文件(turnuserdb.conf多用于restund的配置方式);
 
2) cert和pkey配置的自签名证书用Openssl命令生成 
 > 
 sudo openssl req -x509 -newkey rsa:2048 -keyout /etc/turn_server_pkey.pem -out /etc/turn_server_cert.pem -days 99999 -nodes
 
3) 配置成功后生成用户名/密码 
 > 
 turnadmin -k -u -r -p //turnadmin -k -a -u btzh -r ice.btzh.net -p webrtc
 
执行上述命令后,会创建一个用户/密码,并添加到SQLlite数据库中.
 
参数含义请参见 
 > 
 man turnadmin
 
3. 启动服务
 
1) 确保端口开放
 
> 
 iptables -A INPUT -p udp –dport 3478 -j ACCEPT 
 iptables -A INPUT -p udp –dport 5349 -j ACCEPT
 
2) 启动
 
后台启动 
 > 
 service coturn start
 
非后台启动: 
 > 
 turnserver
 
初次运行请使用非后台启动,会显示当前运行状态,如果有错误会直接显示在控制台上.
 
3) 验证监听端口
 
> 
 lsof -n -i4TCP:3478 | grep LISTEN 
 lsof -n -i4TCP:5349 | grep LISTEN
 
或直接查看所有UDP/TCP 端口 
 > 
 netstat -nat | grep LISTEN
 
4. 验证服务
 
1) stun
 
> 
 apt-get install stun-client
 
之后运行 
 > 
 stun domain.com:port //例如: stun ice.btzh.net:3478
 
2) 直接测试
 
浏览器输入 
 > 
 domain/IP:port // 例如 ice.btzh.net:3478
 
出现文字”TURN Server use https connection for the admin session”
 
3) 在线测试(推荐)
 
网址 
 > 
 https://webrtc.github.io/samples/src/content/peerconnection/trickle-ice/
 
测试格式 
 > 
 stun:domain:port // example: stun:ice.btzh.net:3478
 
注意: 不要以此网站测试TURN username/TURN password的正确性(it doesn`t work for username/pwd test).
 
参考链接: https://github.com/coturn/coturn/blob/master/INSTALL