AUD$ 引起的SYSTEM表空间过大

oracle 11g 默认审计开启DB,数据库在实际运行中,会将审计放入aud SYSTEMsystem(1)truncatetableaud ; 释放SYSTEM表空间的大小
或者
参考如下:
一. 官网说明
1.1 Oracle 11gR2 concepts
From:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/ds_concepts003.htm#ADMIN12108
1.1.1 Database Auditing
Databaseauditing is the monitoring and recording of selected user database actions. You can use standard auditing to audit SQL statements,privileges, schemas, objects, and network and multitier activity.Alternatively, you can use fine-grained auditingto monitor specific database activities, such as actions on a database table ortimes that activities occur. For example, you can audit a table accessed after9:00 p.m.
Reasons for using auditing include:
(1)Enabling future accountability for current actions
(2)Deterring users (or others, such as intruders) from inappropriateactions based on their accountability
(3)Investigating, monitoring, and recording suspicious activity
(4)Addressing auditing requirements for compliance
1.1.2 Oracle Audit Vault
OracleAudit Vault enables you to consolidate, report, and configure alerts foraudited data. You can consolidate audit data generated by Oracle Database andother relational databases. You can also use Oracle Audit Vault to monitoraudit settings on target databases.
1.2 Oracle 10gR2 concept
From:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#i12374
1.2.1 Overview of Database Auditing
Auditingis the monitoring and recording of selected user database actions. It can bebased on individual actions, such as the type of SQL statement run, or oncombinations of factors that can include name, application, time, and so on.Security policies can cause auditing when specified elements in an Oracledatabase are accessed or altered, including content.
Auditing is generally used to:
(1)Enable future accountability for current actions taken in aparticular schema, table, or row, or affecting specific content
(2)Investigate suspicious activity. For example, if an unauthorizeduser is deleting data from tables, then the security administrator could auditall connections to the database and all successful and unsuccessful deletionsof rows from all tables in the database.
(3)Monitor and gather data about specific database activities. Forexample, the database administrator can gather statistics about which tablesare being updated, how many logical I/Os are performed, or how many concurrentusers connect at peak times.
Youcan use Enterprise Manager to view and configure audit-related initializationparameters and administer audited objects for statement auditing and schemaobject auditing. For example, Enterprise Manager shows the properties forcurrent audited statements, privileges, and objects. You can view theproperties of each object, and you can search audited objects by theirproperties. You can also turn on and turn off auditing on objects, statements,and privileges.
1.2.2 Types and Records of Auditing
Oracle allows audit options to be focused or broad. You can audit:
(1)Successful statement executions, unsuccessful statement executions,or both
(2)Statement executions once in each user session or once every timethe statement is run
(3)Activities of all users or of a specific user
Oracle auditing enablesthe use of several different mechanisms, with the following features:
Table 20-1 Types of Auditing
Type of Auditing
Meaning/Description
Statement auditing
Audits SQL statements by type of statement, not by the specific schema objects on which they operate. Typically broad, statement auditing audits the use of several types of related actions for each option. For example, AUDIT TABLE tracks several DDL statements regardless of the table on which they are issued. You can also set statement auditing to audit selected users or every user in the database.
Privilege auditing
Audits the use of powerful system privileges enabling corresponding actions, such as AUDIT CREATE TABLE. Privilege auditing is more focused than statement auditing because it audits only the use of the target privilege. You can set privilege auditing to audit a selected user or every user in the database.
Schema object auditing
Audits specific statements on a particular schema object, such as AUDIT SELECT ON employees. Schema object auditing is very focused, auditing only a specific statement on a specific schema object. Schema object auditing always applies to all users of the database.
Fine-grained auditing
Audits data access and actions based on content. Using DBMS_FGA, the security administrator creates an audit policy on the target table. If any rows returned from a DML statement block match the audit condition, then an audit event entry is inserted into the audit trail.
1.2.3Audit Records and the Audit Trails
Audit records includeinformation such as the operation that was audited, the user performing theoperation, and the date and time of the operation. Audit records can be storedin either a data dictionary table, called the databaseaudit trail, or in operating system files, calledan operating system audit trail.
1.2.3.1 DatabaseAudit Trail
The database audit trail is a single table named SYS.AUD intheSYSschemaofeachOracledatabasesdatadictionary.Severalpredefinedviewsareprovidedtohelpyouusetheinformationinthistable.Audittrailrecordscancontaindifferenttypesofinformation,dependingontheeventsauditedandtheauditingoptionsset.Thefollowinginformationisalwaysincludedineachaudittrailrecord,iftheinformationismeaningfultotheparticularauditaction:1Username2Instancenumber3Processidentifier4Sessionidentifier5Terminalidentifier6Nameoftheschemaobjectaccessed7Operationperformedorattempted8Completioncodeoftheoperation9Dateandtimestamp10Systemprivilegesused1.2.3.2AuditinginaDistributedDatabaseAuditingissiteautonomous.Aninstanceauditsonlythestatementsissuedbydirectlyconnectedusers.AlocalOraclenodecannotauditactionsthattakeplaceinaremotedatabase.Becauseremoteconnectionsareestablishedthroughtheuseraccountofadatabaselink,statementsissuedthroughthedatabaselinksconnectionareauditedbytheremoteOraclenode.1.2.3.3OperatingSystemAuditTrailOracleallowsaudittrailrecordstobedirectedtoanoperatingsystemaudittrailiftheoperatingsystemmakessuchanaudittrailavailabletoOracle.Ifnot,thenauditrecordsarewrittentoafileoutsidethedatabase,withaformatsimilartootherOracletracefiles.Oracleallowscertainactionsthatarealwaysauditedtocontinue,evenwhentheoperatingsystemaudittrail(ortheoperatingsystemfilecontainingauditrecords)isunabletorecordtheauditrecord.Theusualcauseofthisisthattheoperatingsystemaudittrailorthefilesystemisfullandunabletoacceptnewrecords.Systemadministratorsconfiguringoperatingsystemauditingshouldensurethattheaudittrailorthefilesystemdoesnotfillcompletely.Mostoperatingsystemsprovideadministratorswithsufficientinformationandwarningtoensurethisdoesnotoccur.Note,however,thatconfiguringauditingtousethedatabaseaudittrailremovesthisvulnerability,becausetheOracledatabaseserverpreventsauditedeventsfromoccurringiftheaudittrailisunabletoacceptthedatabaseauditrecordforthestatement.1.2.3.4OperatingSystemAuditRecordsTheoperatingsystemaudittrailisencoded,butitisdecodedindatadictionaryfilesanderrormessages.1Actioncodedescribestheoperationperformedorattempted.TheAUDITACTIONSdatadictionarytabledescribesthesecodes.2Privilegesuseddescribesanysystemprivilegesusedtoperform.theoperation.TheSYSTEMPRIVILEGEMAPtabledescribesallofthesecodes.3Completioncodedescribestheresultoftheattemptedoperation.Successfuloperationsreturnavalueofzero,andunsuccessfuloperationsreturntheOracleerrorcodedescribingwhytheoperationwasunsuccessful.1.2.3.5RecordsAlwaysintheOperatingSystemAuditTrailSomedatabaserelatedactionsarealwaysrecordedintotheoperatingsystemaudittrailregardlessofwhetherdatabaseauditingisenabled:1Atinstancestartup,anauditrecordisgeneratedthatdetailstheoperatingsystemuserstartingtheinstance,theusersterminalidentifier,thedateandtimestamp,andwhetherdatabaseauditingwasenabledordisabled.Thisinformationisrecordedintotheoperatingsystemaudittrail,becausethedatabaseaudittrailisnotavailableuntilafterstartuphassuccessfullycompleted.Recordingthestateofdatabaseauditingatstartupalsoactsasanauditingflag,inhibitinganadministratorfromperformingunauditedactionsbyrestartingadatabasewithdatabaseauditingdisabled.2Atinstanceshutdown,anauditrecordisgeneratedthatdetailstheoperatingsystemusershuttingdowntheinstance,theusersterminalidentifier,thedateandtimestamp.3Duringconnectionswithadministratorprivileges,anauditrecordisgeneratedthatdetailstheoperatingsystemuserconnectingtoOraclewithadministratorprivileges.Thisrecordprovidesaccountabilityregardingusersconnectedwithadministratorprivileges.OnoperatingsystemsthatdonotmakeanaudittrailaccessibletoOracle,theseaudittrailrecordsareplacedinanOracleaudittrailfileinthesamedirectoryasbackgroundprocesstracefiles.1.2.3.6WhenAreAuditRecordsCreated?Anyauthorizeddatabaseusercansethisownauditoptionsatanytime,buttherecordingofauditinformationisenabledordisabledbythesecurityadministrator.Whenauditingisenabledinthedatabase,anauditrecordisgeneratedduringtheexecutephaseofstatementexecution.SQLstatementsinsidePL/SQLprogramunitsareindividuallyaudited,asnecessary,whentheprogramunitisrun.Thegenerationandinsertionofanaudittrailrecordisindependentofauserstransactionbeingcommitted.Thatis,evenifauserstransactionisrolledback,theaudittrailrecordremainscommitted.Statementandprivilegeauditoptionsineffectatthetimeadatabaseuserconnectstothedatabaseremainineffectforthedurationofthesession.Settingorchangingstatementorprivilegeauditoptionsinasessiondoesnotcauseeffectsinthatsession.Themodifiedstatementorprivilegeauditoptionstakeeffectonlywhenthecurrentsessionisendedandanewsessioniscreated.Incontrast,changestoschemaobjectauditoptionsbecomeeffectiveforcurrentsessionsimmediately.OperationsbytheSYSuserandbyusersconnectedthroughSYSDBAorSYSOPERcanbefullyauditedwiththeAUDITSYSOPERATIONSinitializationparameter.SuccessfulSQLstatementsfromSYSareauditedindiscriminately.TheauditrecordsforsessionsestablishedbytheuserSYSorconnectionswithadministrativeprivilegesaresenttoanoperatingsystemlocation.SendingthemtoalocationseparatefromtheusualdatabaseaudittrailintheSYSschemaprovidesforgreaterauditingsecurity..Audit2.1Audit):systemSYS.AUD 表中,可通过视图dba_audit_trail查看)或操作系统审计记录中(默认位置为 ORACLEBASE/admin/ ORACLE_SID/adump/).。默认情况下审计是没有开启的。
当数据库的审计是使能的,在语句执行阶段产生审计记录。审计记录包含有审计的操作、用户执行的操作、操作的日期和时间等信息。
不管你是否打开数据库的审计功能,以下这些操作系统会强制记录:用管理员权限连接Instance;启动数据库;关闭数据库。
2.1.1 Oracle审计功能
审计是对选定的用户动作的监控和记录,通常用于:
审查可疑的活动。例如:数据被非授权用户所删除,此时安全管理员可决定对该数据库的所有连接进行审计,以及对数据库的所有表的成功地或不成功地删除进行审计。
监视和收集关于指定数据库活动的数据。例如:DBA可收集哪些被修改、执行了多少次逻辑的I/O等统计数据。
2.1.2 ORACLE所允许的审计选择限于下列方面:
审计语句的成功执行、不成功执行,或者其两者。
对每一用户会话审计语句执行一次或者对语句每次执行审计一次。
对全部用户或指定用户的活动的审计。
2.1.3 审计相关的表安装
SQLPLUS> connect / AS SYSDBA
SQLPLUS> select * from sys.aud ;SQLPLUS>selectfromdbaaudittrail;SQLPLUS>connect/assysdbaSQLPLUS>@ ORACLE_HOME/rdbms/admin/cataudit.sql
审计表安装在SYSTEM表空间。所以要确保SYSTEM表空间又足够的空间存放审计信息。
安装后要重启数据库
2.1.4 将审计相关的表移动到其他表空间
由于AUD SYSTEMSYSTEMAUD 移动到其他的表空间上。可以使用下面的语句来进行移动:
sql>connect / as sysdba;
sql>alter table aud movetablespace;sql>alterindexIaud1rebuildonlinetablespace;SQL>altertableaudit move tablespace;
SQL> alter index i_audit rebuild onlinetablespace ;
SQL> alter table audit_actions movetablespace ;
SQL> alter index i_audit_actions rebuildonline tablespace ;
2.1.5 truncate 或者 delete sys.aud deleteaud 表exp备份一下,注意,不要直接exp,先创建一张临时表,然后将临时表exp。
sql>createtable audit_record tablespace users as select * from sys.aud$;

然后exp:
exptables=AUDIT_RECORD file=audit_record.dmp

最后delete 数据:
sql>delete from sys.aud ;sql>deletefromsys.aud whereobjname=’&table_nmae’;  
注意,delete 不会释放system表空间。 可以使用truncate table:  
sql>truncate table sys.aud

2.2 和审计相关的两个主要参数
2.2.1 Audit_sys_operations
AUDIT_SYS_OPERATIONSenables or disables the auditing of top-level operations, which are SQL statementsdirectly issued by users when connecting with SYSDBA or SYSOPER privileges.(SQL statements run from within PL/SQL procedures or functions are notconsidered top-level.) The audit records are written to the operating system’saudit trail. The audit records will be written in XML format if the AUDIT_TRAILinitialization parameter is set to xml or xml, extended.
OnUNIX platforms, if the AUDIT_SYSLOG_LEVEL parameter has also been set, then itoverrides the AUDIT_TRAIL parameter and SYS audit records are written to thesystem audit log using the SYSLOG utility.
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams015.htm#REFRN10005
默认为false,当设置为true时,所有sys用户(包括以sysdba, sysoper身份登录的用户)的操作都会被记录,audit trail不会写在aud aud 不可用,那么像conn /as sysdba这样的连接信息,只能记录在其它地方。如果是windows平台,audti trail会记录在windows的事件管理中,如果是linux/unix平台则会记录在audit_file_dest参数指定的文件中。
SYS@dave2(db2)> show parameteraudit_file_dest
NAME TYPE VALUE


audit_file_dest string /u01/app/oracle/admin/dave2/adump
2.2.2 Audit_trail
AUDIT_TRAIL enables or disables databaseauditing.
Values:
(1)none:Disables standard auditing. This value is thedefault if the AUDIT_TRAIL parameter was not set in the initializationparameter file or if you created the database using a method other thanDatabase Configuration Assistant. If you created the database using DatabaseConfiguration Assistant, then the default is db.
(2)os:Directs all audit records to an operating system file. Oraclerecommends that you use the os setting, particularly if you are using anultra-secure database configuration.
(3)db:Directs audit records to the database audit trail (the SYS.AUD table),exceptforrecordsthatarealwayswrittentotheoperatingsystemaudittrail.Usethissettingforageneraldatabaseformanageability.IfthedatabasewasstartedinreadonlymodewithAUDITTRAILsettodb,thenOracleDatabaseinternallysetsAUDITTRAILtoos.Checkthealertlogfordetails.4db,extendedPerformsallactionsofAUDITTRAIL=db,andalsopopulatestheSQLbindandSQLtextCLOBtypecolumnsoftheSYS.AUD table, when available. These two columns are populated only when this parameteris specified.
If the database was started in read-onlymode with AUDIT_TRAIL set to db, extended, then Oracle Database internally setsAUDIT_TRAIL to os. Check the alert log for details.
(5)xml:Writes to the operating system audit record file in XML format.Records all elements of the AuditRecord node except Sql_Text and Sql_Bind tothe operating system XML audit file.
(6)xml, extended:Performs all actions of AUDIT_TRAIL=xml,and populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$table, wherever possible. These columns are populated only when this parameteris specified.
Youcan use the SQL AUDIT statement to set auditing options regardless of thesetting of this parameter.
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams017.htm#REFRN10006
None:是默认值,不做审计;
DB:将audit trail 记录在数据库的审计相关表中,如aud DB,ExtendedOSaudittrailauditfiledestXML10gstatic2.3Statement()Privilegeobject2.3.1StatementSQLaudittablecreatetable,droptable,truncatetablealtersessionbycmycmy2.3.2Privilege使grantselectanytabletoaauditselectanytablea访bselectfromb.tselectanytable访2.3.3Object.onaduitalter,delete,drop,insertoncmy.tbyscott;cmyt使byscottOracleschemaOracleondefaultauditdropondefaultbyaccess;dropdefaulttriggerschemaDDL2.42.4.1byaccess/bysessionbyaccessaudittrailbysessionaudittrailbysession2.4.2whenever[not]successfulwheneversuccessful(dbaaudittrailreturncode0),whenevernotsuccessful2.52.5.1dbaaudittrailaudittrailaud 的视图。其它的视图dba_audit_session,dba_audit_object,dba_audit_statement都只是dba_audit_trail的一个子集。
2.5.2 dba_stmt_audit_opts:可以用来查看statement审计级别的audit options,即数据库设置过哪些statement级别的审计。dba_obj_audit_opts,dba_priv_audit_opts视图功能与之类似
2.5.3 all_def_audit_opts:用来查看数据库用on default子句设置了哪些默认对象审计。
2.6取消审计
将对应审计语句的audit改为noaudit即可,
如audit sessionwhenever successful
对应的取消审计语句为noauditsession whenever successful;
三. Fine-grainedauditing(FGA) 细粒度审计
细粒度审计(FGA):精细审计 ,是在 Oracle 9i 中引入的,能够记录 SCN 号和行级的更改以重建旧的数据,但是它们只能用于 select 语句,而不能用于 DML ,如 update 、insert 和delete 语句。因此,对于 Oracle 数据库 10g 之前的版本,使用触发器虽然对于以行级跟踪用户初始的更改是没有吸引力的选择,但它也是唯一可靠的方法。 10g 之后版本可以audit 所有DML。FGA的实现基于DBMS_FGA包。它属于SYS用户。
3.1 增加 FGA 策略

– 审计表
SQL>grant resource,connect to bank identified by bank;

create table bank.accounts
(
acct_no number primary key,
cust_id number not null ,
balance number(15,2) null
);
insert into bank.accounts values(1,1,10000);
insert into bank.accounts values(2,2,20000);
commit;

Begin
dbms_fga.drop_policy (
object_schema=>’BANK’,
object_name=>’ACCOUNTS’,
policy_name=>’ACCOUNTS_ACCESS’);

dbms_fga.add_policy (
object_schema=>’BANK’,
object_name=>’ACCOUNTS’,
policy_name=>’ACCOUNTS_ACCESS’);
end;
/

select * from bank.accounts;
select timestamp, db_user,os_user,object_schema,object_name,sql_text from dba_fga_audit_trail;

– 审计列和审计条件, 在add_policy中加入
– audit_column => ‘BALANCE’
– audit_condition => ‘BALANCE >=11000’

Begin
dbms_fga.drop_policy (
object_schema=>’BANK’,
object_name=>’ACCOUNTS’,
policy_name=>’ACCOUNTS_ACCESS’);

dbms_fga.add_policy (
object_schema=>’BANK’,
object_name=>’ACCOUNTS’,
audit_column => ‘BALANCE’,
audit_condition => ‘BALANCE >=11000’,
policy_name=>’ACCOUNTS_ACCESS’);
end;
/

select BALANCE from bank.accounts;
select timestamp, db_user,os_user,object_schema,object_name,sql_text from dba_fga_audit_trail;

3.2 管理 FGA 策略

–要删除策略,您可以使用以下语句:
begin
dbms_fga.drop_policy (
object_schema => ‘BANK’,
object_name => ‘ACCOUNTS’,
policy_name => ‘ACCOUNTS_ACCESS’
);
end;
/

– 对于更改策略而言,没有随取随用的解决方案。要更改策略中的任何参数,必须删除策略,再使用更改后的参数添加策略。

– 需要临时禁用审计收集
例如,如果您希望将线索表移动到不同的表空间或者要删除线索表。您可以按如下方法禁用 FGA 策略:
begin
dbms_fga.enable_policy (
object_schema => ‘BANK’,
object_name => ‘ACCOUNTS’,
policy_name => ‘ACCOUNTS_ACCESS’,
enable => FALSE );
end;
/
– 重新启用很简单 enable =>TRUE;

–演示何时审计操作以及何时不审计操作的各种情况 SQL 语句审计状态:

select balance from bank.accounts;
进行审计。用户选择了在添加策略时所指定的审计列 BALANCE。

select * from bank.accounts;
进行审计。即使用户没有明确指定列 BALANCE,* 也隐含地选择了它。

select cust_id from bank.accounts where balance < 10000;
进行审计。即使用户没有明确指定列 BALANCE,where 子句也隐含地选择了它。

select cust_id from bank.accounts;
不进行审计。用户没有选择列 BALANCE。
select count(*) from bank.accounts;
不进行审计。用户没有明确或隐含地选择列 BALANCE。

3.3 处理器模块

FGA 的功能不只是记录审计线索中的事件;FGA 还可以任意执行过程.过程可以执行一项操作,比如当用户从表中选择特定行时向审计者发送电子邮件警告,或者可以写到不同的审计线索中。这种存储代码段可以是独立的过程或者是程序包中的过程,称为策略的处理器模块。
实际上由于安全性原因,它不必与基表本身处于同一模式中,您可能希望特意将它放置在不同的模式中。由于只要 SELECT 出现时过程就会执行,非常类似于 DML 语句启动的触发器,您还可以将其看作 SELECT 语句触发器。

– 以下参数指定将一个处理器模块指定给策略:
(1)handler_schema 拥有数据过程的模式
(2)handler_module 过程名称
(3)处理器模块还可以采用程序包的名称来代替过程名称。在这种情况下,参数handler_module 在package.procedure 的格式中指定。

3.4 FGA 数据字典视图

FGA 策略的定义位于数据字典视图 DBA_AUDIT_POLICIES 中。
审计线索收集在 SYS 拥有的表 FGA_LOG$ 中。对于 SYS 拥有的任何原始表,此表上的某些视图以对用户友好的方式显示信息。DBA_FGA_AUDIT_TRAIL是该表上的一个视图。
一个重要的列是 SQL_BIND,它指定查询中使用的绑定变量的值,这是显著增强该工具功能的一项信息。
另一个重要的列是 SCN,当发生特定的查询时,它记录系统更改号。此信息用于识别用户在特定时间看到了什么,而不是现在的值,它使用了闪回查询,这种查询能够显示在指定的 SCN 值时的数据。

3.5 视图和 FGA

到目前为止已经讨论了在表上应用 FGA;现在让我们来看如何在视图上使用 FGA。假定在 ACCOUNTS 表上定义视图 VW_ACCOUNTS 如下:

create view bank.vw_accounts as select * from bank.accounts;

select * from bank.vw_accounts;
select timestamp, db_user,os_user,object_schema,object_name,sql_text from dba_fga_audit_trail;

如果您只希望审计对视图的查询而不是对表的查询,可以对视图本身建立策略。通过将视图名称而不是表的名称传递给打包的过程dbms_fga.add_policy 中的参数 object_name,可以完成这项工作。
随后 DBA_FGA_AUDIT_TRAIL 中的 OBJECT_NAME 列将显示视图的名称,并且不会出现有关表访问的附加记录。

3.6 其它用途

除了记录对表的选择访问,FGA 还可用于某些其它情况:
(1)可以对数据仓库使用 FGA,以捕获特定的表、视图或物化视图上发生的所有语句,这有助于计划索引。不需要到 V SQL使SQLV SQL 的期限,在 FGA 审计线索中将会始终提供它。
(2)由于 FGA 捕获绑定变量,它可以帮助您了解绑定变量值的模式,这有助于设计直方图集合等。
(3)处理器模块可以向审计者或DBA 发送警告,这有助于跟踪恶意应用程序。
(4)由于 FGA 可以作为 SELECT 语句的触发器,您可以在需要这种功能的任何时候使用它。

3.7 视图部分字段说明

3.7.1 DBA_AUDIT_POLICIES

OBJECT_SCHEMA 对其定义了 FGA 策略的表或视图的所有者
OBJECT_NAME 表或视图的名称
POLICY_NAME 策略的名称 — 例如,ACCOUNTS_ACCESS
POLICY_TEXT 在添加策略时指定的审计条件 — 例如,BALANCE >;= 11000
POLICY_COLUMN 审计列 — 例如,BALANCE
ENABLED 如果启用则为 YES,否则为 NO
PF_SCHEMA 拥有策略处理器模块的模式(如果存在)
PF_PACKAGE 处理器模块的程序包名称(如果存在)
PF_FUNCTION 处理器模块的过程名称(如果存在)

3.7.2 DBA_FGA_AUDIT_TRAIL

SESSION_ID 审计会话标识符;与 V$SESSION 视图中的会话标识符不同
TIMESTAMP 审计记录生成时的时间标记
DB_USER 发出查询的数据库用户
OS_USER 操作系统用户
USERHOST 用户连接的机器的主机名
CLIENT_ID 客户标识符(如果由对打包过程dbms_session.set_identifier 的调用所设置)
EXT_NAME 外部认证的客户名称,如 LDAP 用户
OBJECT_SCHEMA 对该表的访问触发了审计的表所有者
OBJECT_NAME 对该表的 SELECT 操作触发了审计的表名称
POLICY_NAME 触发审计的策略名称(如果对表定义了多个策略,则每个策略将插入一条记录。在此情况下,该列显示哪些行是由哪个策略插入的。)
SCN 记录了审计的 Oracle 系统更改号
SQL_TEXT 由用户提交的 SQL 语句
SQL_BIND 由 SQL 语句使用的绑定变量(如果存在)
小结:
FGA 在 Oracle 数据库中支持隐私和职能策略。因为审计发生在数据库内部而不是应用程序中,所以无论用户使用的访问方法是什么(通过诸如 SQL*Plus 等工具或者应用程序),都对操作进行审计,允许进行非常简单的设置。
四. 相关示例
4.1 审计功能的参数控制
audit_trail 参数的值可以设置为以下几种
1. NONE:不开启
2. DB:开启审计功能
3. OS:审计记录写入一个操作系统文件。
4. TRUE:与参数DB一样
5. FALSE:不开启审计功能。
这个参数是写道spfile里面的,需要重启数据库
4.2 查看是否审计功能是否启动
SQL> show parameter audit
NAME TYPE VALUE


audit_file_dest string /u01/app/oracle/admin/ORCL/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
4.3 开启审计
SQL> conn /as sysdba
SQL> show parameter audit
NAME TYPE VALUE


audit_file_dest string /u01/app/oracle/admin/ORCL/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
SQL> alter system setaudit_sys_operations=TRUE scope=spfile;
–审计管理用户(以sysdba/sysoper角色登陆)
SQL> alter system setaudit_trail=db,extended scope=spfile;
开启审计要重启实例
SQL> show parameter audit
NAME TYPE VALUE


audit_file_dest string /u01/app/oracle/admin/ORCL/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB, EXTENDED
4.4 关闭审计
SQL> conn /as sysdba
SQL> show parameter audit
SQL> alter system set audit_trail=none;
关闭审计也需要重启实例
4.5 审计实例
4.5.1 激活审计
SQL> conn sys/admin as sysdba
已连接。
SQL> show parameter audit
NAME TYPE VALUE


audit_file_dest string D:\ORACLE\ADMIN\DBA\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
SQL> alter system set audit_sys_operations=TRUEscope=spfile;
–审计管理用户(以sysdba/sysoper角色登陆)
SQL> alter system setaudit_trail=db,extended scope=spfile;
SQL> startup force;
SQL> show parameter audit
NAME TYPE VALUE


audit_file_dest string D:\ORACLE\ADMIN\DBA\ADUMP
audit_sys_operations boolean TRUE
audit_trail string DB, EXTENDED
4.5.2 开始审计
注意:无法对 SYS 用户操作执行 audit 或noaudit 命令
SQL> conn system/admin
SQL> audit all on test;
SQL> commit;
SQL> delete from test;
SQL> commit;
SQL> selectos_username,username,userhost,terminal,timestamp,owner,obj_name,action_name,sessionid,os_process,sql_textfrom dba_audit_trail;
os_user username userhost terminal timestamp owner


user system workgroup\hfcc-hfcc-kf-3068 22-10月-09 system
SQL> audit select table by test byaccess;
如果在命令后面添加by user则只对user的操作进行审计,如果省去by用户,则对系统中所有的用户进行审计(不包含sys用户).
例:
audit delete any table; –审计删除表的操作
audit delete any table whenever notsuccessful; –只审计删除失败的情况
audit delete any table whenever successful;–只审计删除成功的情况
audit delete,update,insert on user.table bysystem; –审计system用户对表user.table的delete,update,insert操作
4.5.3 撤销审计
SQL> noaudit all on t_test;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值