成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

Oracle標(biāo)準(zhǔn)數(shù)據(jù)庫審核

Oracle標(biāo)準(zhǔn)數(shù)據(jù)庫審核可以對一般用戶（不包括SYS）的各種權(quán)限操作進(jìn)行審核和跟蹤。

十余年的應(yīng)城網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。成都營銷網(wǎng)站建設(shè)的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整應(yīng)城建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)公司從事“應(yīng)城網(wǎng)站設(shè)計(jì)”,“應(yīng)城網(wǎng)站推廣”以來，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

一、標(biāo)準(zhǔn)數(shù)據(jù)庫審核的基本方法

1、開啟標(biāo)準(zhǔn)數(shù)據(jù)庫審核

初始化參數(shù)audit_trail是一個(gè)靜態(tài)參數(shù)，該參數(shù)確定如何啟用審核，不同的值表示是否開啟審核以及如何記錄審核。

該參數(shù)可以設(shè)定為如下的值：

none或flase（10g為默認(rèn)）：不審核；

db或true（11g為默認(rèn)）：審核結(jié)果記錄到數(shù)據(jù)庫表sys.aud$，可以通過視圖dba_audit_trail來查看結(jié)果；

os：審核結(jié)果記錄到操作系統(tǒng)文件中，Unix在audit_file_dest參數(shù)中指定，Windows則在應(yīng)用程序日志中（事件查看器eventvwr）；

db_extended：與db大致相同，但審核結(jié)果包含了具有綁定變量的SQL語句；

xml：與os大致相同，但使用xml來標(biāo)記；

xml_extended：與xml大致相同，但審核結(jié)果包含了具有綁定變量的SQL語句。

2、指定審核選項(xiàng)

使用audit命令可以配置數(shù)據(jù)庫審核，標(biāo)準(zhǔn)數(shù)據(jù)庫審核包含以下幾類：

1）系統(tǒng)權(quán)限審核

審核系統(tǒng)權(quán)限的操作，如

audit create any table;

audit create any trigger;

審核某用戶的系統(tǒng)權(quán)限操作，如

audit select any table by scott;

訪問自己的表時(shí)不會做審核。

審核用戶的創(chuàng)建和刪除

audit create user, drop user;

查開啟的系統(tǒng)權(quán)限審核，通過數(shù)據(jù)字典dba_priv_audit_opts，11g默認(rèn)會開啟以下審核

col user_name for a20

col proxy_name for a20

col privilege for a30

col success for a20

col failure for a20

select * from dba_priv_audit_opts;

USER_NAME       PROXY_NAME      PRIVILEGE                                SUCCESS    FAILURE

--------------- --------------- ---------------------------------------- ---------- ----------

                                CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS

                                CREATE ANY JOB                           BY ACCESS  BY ACCESS

                                GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS

                                EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS

                                CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS

                                GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS

                                DROP PROFILE                             BY ACCESS  BY ACCESS

                                ALTER PROFILE                            BY ACCESS  BY ACCESS

                                DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS

                                ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS

                                CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS

                                ALTER DATABASE                           BY ACCESS  BY ACCESS

                                GRANT ANY ROLE                           BY ACCESS  BY ACCESS

                                CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS

                                DROP ANY TABLE                           BY ACCESS  BY ACCESS

                                ALTER ANY TABLE                          BY ACCESS  BY ACCESS

                                CREATE ANY TABLE                         BY ACCESS  BY ACCESS

                                DROP USER                                BY ACCESS  BY ACCESS

                                ALTER USER                               BY ACCESS  BY ACCESS

                                CREATE USER                              BY ACCESS  BY ACCESS

                                CREATE SESSION                           BY ACCESS  BY ACCESS

                                AUDIT SYSTEM                             BY ACCESS  BY ACCESS

                                ALTER SYSTEM                             BY ACCESS  BY ACCESS

2）對象權(quán)限審核

對所有用戶（不包括sys，sys是不審核的），如

aduit alter,delete,drop,insert onscott.emp;

對某個(gè)用戶，如

audit select on hr.employees by scott;

對所有操作，如

audit all on hr.employees;

查開啟的對象審核，通過數(shù)據(jù)字典dba_obj_audit_opts，默認(rèn)是都沒有開啟

select * from dba_obj_audit_opts;

OWNER      OBJECT_NAME     OBJECT_TYPE     ALT   AUD   COM   DEL   GRA   IND   INS   LOC   REN   SEL   UPD   REF EXE   CRE   REA   WRI   FBK

---------- --------------- --------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --- ----- ----- ----- ----- -----

3）語句審核

如審核表的所有DDL操作

audit table;

查開啟的語句審核，通過數(shù)據(jù)字典dba_stmt_audit_opts，11g默認(rèn)會開啟以下審核，其中也包含了上述屬于系統(tǒng)權(quán)限的審核

col user_name for a20

col proxy_name for a20

col audit_option for a30

col success for a20

col failure for a20

select * from dba_stmt_audit_opts;

USER_NAME       PROXY_NAME      AUDIT_OPTION                             SUCCESS    FAILURE

--------------- --------------- ---------------------------------------- ---------- ----------

                                ALTER SYSTEM                             BY ACCESS  BY ACCESS

                                SYSTEM AUDIT                             BY ACCESS  BY ACCESS

                                CREATE SESSION                           BY ACCESS  BY ACCESS

                                CREATE USER                              BY ACCESS  BY ACCESS

                                ALTER USER                               BY ACCESS  BY ACCESS

                                DROP USER                                BY ACCESS  BY ACCESS

                                PUBLIC SYNONYM                           BY ACCESS  BY ACCESS

                                DATABASE LINK                            BY ACCESS  BY ACCESS

                                ROLE                                     BY ACCESS  BY ACCESS

                                PROFILE                                  BY ACCESS  BY ACCESS

                                CREATE ANY TABLE                         BY ACCESS  BY ACCESS

                                ALTER ANY TABLE                          BY ACCESS  BY ACCESS

                                DROP ANY TABLE                           BY ACCESS  BY ACCESS

                                CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS

                                GRANT ANY ROLE                           BY ACCESS  BY ACCESS

                                SYSTEM GRANT                             BY ACCESS  BY ACCESS

                                ALTER DATABASE                           BY ACCESS  BY ACCESS

                                CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS

                                ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS

                                DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS

                                ALTER PROFILE                            BY ACCESS  BY ACCESS

                                DROP PROFILE                             BY ACCESS  BY ACCESS

                                GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS

                                CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS

                                EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS

                                GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS

                                CREATE ANY JOB                           BY ACCESS  BY ACCESS

                                CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS

4）其它審核配置

審核會話登錄

audit session;

這與審核create session權(quán)限的使用效果相同。

取消審核，通過noaudit命令指定

noaudit session;

noaudit all on scott.emp;

審核成功的操作，通過whenever successful選項(xiàng)指定，如審核表的成功插入

audit insert on scott.emp whenever successful;

審核不成功的操作，通過whenever not successful選項(xiàng)指定，如審核失敗的會話登錄

audit session whenever not successful;

默認(rèn)情況下是審核所有的操作，不論成功與否。

會話級別上的審核，通過by session選項(xiàng)指定

audit update on scott.emp by session;

操作級別上的審核，通過by access選項(xiàng)指定

audit update on scott.emp by access;

對象權(quán)限審核默認(rèn)是by session

系統(tǒng)權(quán)限審核默認(rèn)是by access

3、查看審核記錄

如果審核針對數(shù)據(jù)庫（audit_trail=db或db_extended），則審核記錄寫入數(shù)據(jù)字典表sys.aud$中，雖然可以直接查看，但通過建立在其上的視圖來查看將更加方便。

常用的視圖是dba_audit_trail，其常用列的解釋如下：

os_username：執(zhí)行操作的用戶的操作系統(tǒng)用戶名

username：執(zhí)行操作的用戶的Oracle用戶名

userhost：運(yùn)行用戶進(jìn)程的計(jì)算機(jī)名稱

timestamp：審核事件的發(fā)生時(shí)間

owner，obj_name：受影響對象的模式和名稱

action，action_name：審核的操作，操作代碼action的對照含義可查數(shù)據(jù)字典表audit_actions

priv_used：使用的系統(tǒng)權(quán)限

sql_text：執(zhí)行的語句

如果沒有表aud$及視圖dba_audit_trail，則需要執(zhí)行審核相關(guān)的數(shù)據(jù)字典表的安裝腳本，安裝后要重啟數(shù)據(jù)庫，安裝腳本位于

%ORACLE_HOME%\rdbms\admin\cataudit.sql

其它審核視圖顯示了dba_audit_trail視圖的一個(gè)子集：

dba_audit_object

dba_audit_statement

dba_audit_session

二、標(biāo)準(zhǔn)數(shù)據(jù)庫審核實(shí)驗(yàn)

1、創(chuàng)建實(shí)驗(yàn)用表

create table scott.emp1 as select * from scott.emp;

grant all on scott.emp1 to hr;

2、啟用審核

audit all on scott.emp1 by access;

audit table;

alter system set audit_sys_operations=true scope=spfile;

alter system set audit_trail='db_extended' scope=spfile;

重啟數(shù)據(jù)庫實(shí)例

shutdown immediate

startup

實(shí)驗(yàn)前可先清除審核結(jié)果表的所有記錄

truncate table sys.aud$;

3、進(jìn)行sysdba的活動(dòng)，并查看審核結(jié)果

以sysdba身份登錄并操作

select * from dba_users;

select * from scott.emp1;

create user audr1 identified by audr1;

drop user audr1;

create table scott.emp2 as select * from scott.emp1;

select * from scott.emp2;

drop table scott.emp2 purge;

查看sysdba的審核結(jié)果，由于開啟了sysdba審核，所以可以在操作系統(tǒng)文件和日志中看到所有操作記錄。Unix查看audit_file_dest指定的目標(biāo)文件，Windows通過事件查看器eventvwr查看應(yīng)用程序日志。另外可以看到SYS管理員的操作不會記入aud$中，視圖dba_audit_trail沒有相關(guān)記錄。

4、用system用戶登錄操作，并查看審核結(jié)果

select * from scott.emp;

select * from scott.emp1;

create user audr1 identified by audr1;

grant connect to audr1;

create table audr1.t1(n number);

select * from audr1.t1;

drop table audr1.t1 purge;

drop user audr1;

退出system的登錄

查看審核結(jié)果

col os_username for a20

col username for a20

col userhost for a20

col owner for a10

col obj_name for a20

col action_name for a20

col priv_used for a20

col sql_text for a50

select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc;

OS_USERNAME          USERNAME             USERHOST             TIMESTAMP           OWNER      OBJ_NAME                 ACTION ACTION_NAME          PRIV_USED            SQL_TEXT

-------------------- -------------------- -------------------- ------------------- ---------- -------------------- ---------- -------------------- -------------------- --------------------------------------------------

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:36:05                                101 LOGOFF

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:59            AUDR1                53 DROP USER            DROP USER            drop user audr1

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:49 AUDR1      T1                   12 DROP TABLE           DROP ANY TABLE       drop table audr1.t1 purge

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:32 AUDR1      T1                    1 CREATE TABLE         CREATE ANY TABLE     create table audr1.t1(n number)

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:24            CONNECT                     114 GRANT ROLE           GRANT ANY ROLE       grant connect to audr1

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:14            AUDR1                51 CREATE USER          CREATE USER          create user audr1 identified by *****

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:04 SCOTT      EMP1                  3 SELECT               SELECT ANY TABLE     select * from scott.emp1

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:34:33                                100 LOGON                CREATE SESSION

由于沒有對表scott.emp加入審核，因此對它的查詢未計(jì)入表中，而會話的登入登出、建表、刪表、授權(quán)是默認(rèn)開啟的系統(tǒng)權(quán)限審核，因此這些操作被記入表中，同樣對表audr1.t1的查詢也不會加入審核。

5、清空aud$記錄，在sys下創(chuàng)建用戶audr1，并分別用audr1和hr用戶登錄操作，查看審核結(jié)果

sys的操作

create user audr1 identified by audr1;

grant connect to audr1;

audr1用戶登錄操作

select * from scott.emp1;

由于沒有給audr1訪問scott.emp1的權(quán)限，因此以上查詢將失敗

audr1用戶退出登錄

hr用戶登錄操作

select * from scott.emp1;

update scott.emp1 set sal=2000 where empno=7369;

commit;

update scott.emp1 set sal=2500 where empno=7369;

rollback;

hr用戶退出登錄

查看審核記錄

col os_username for a20

col username for a20

col userhost for a20

col owner for a10

col obj_name for a20

col action_name for a20

col priv_used for a20

col sql_text for a50

select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc;

OS_USERNAME          USERNAME             USERHOST             TIMESTAMP           OWNER      OBJ_NAME                 ACTION ACTION_NAME          PRIV_USED            SQL_TEXT

-------------------- -------------------- -------------------- ------------------- ---------- -------------------- ---------- -------------------- -------------------- --------------------------------------------------

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:42:29                                101 LOGOFF

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:45:33 SCOTT      EMP1                  6 UPDATE                                    update scott.emp1 set sal=2500 where empno=7369

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:42:17 SCOTT      EMP1                  6 UPDATE                                    update scott.emp1 set sal=2000 where empno=7369

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:42:09 SCOTT      EMP1                  3 SELECT                                    select * from scott.emp1

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:41:49                                100 LOGON                CREATE SESSION

Administrator        AUDR1                WORKGROUP\MYPC       2017-09-20 10:41:41                                101 LOGOFF

Administrator        AUDR1                WORKGROUP\MYPC       2017-09-20 10:41:29 SCOTT      EMP1                  3 SELECT                                    select * from scott.emp1

Administrator        AUDR1                WORKGROUP\MYPC       2017-09-20 10:41:01                                100 LOGON                CREATE SESSION

由于默認(rèn)是操作不論成功與否都會納入審核，因此audr1用戶失敗的查詢也被記錄，commit和rollback語句并沒有記錄，不管執(zhí)行的語句最后是被提交還是回滾，更新操作總是被審核的。

6、取消對象審核

noaudit all on scott.emp1;

hr用戶再次登錄操作

select * from scott.emp1;

hr用戶退出登錄

查看審核記錄，確認(rèn)審核只有用戶的登入登出，其它已取消。如果要將會話的登入登出記錄也取消，則執(zhí)行noaudit session，但這樣一來，默認(rèn)的系統(tǒng)權(quán)限審核將不再包括該項(xiàng)，除非執(zhí)行audit session重新加入。

7、清空aud$記錄，改為會話級別的審核，并查看結(jié)果

audit all on scott.emp1 by session;

再次以hr用戶登錄并操作

select * from scott.emp1;

update scott.emp1 set sal=800 where empno=7369;

commit;

hr用戶退出登錄

查看審核記錄

col os_username for a20

col username for a20

col userhost for a20

col owner for a10

col obj_name for a20

col action_name for a20

col priv_used for a20

col sql_text for a50

select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc;

OS_USERNAME          USERNAME             USERHOST             TIMESTAMP           OWNER      OBJ_NAME                 ACTION ACTION_NAME          PRIV_USED            SQL_TEXT

-------------------- -------------------- -------------------- ------------------- ---------- -------------------- ---------- -------------------- -------------------- --------------------------------------------------

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:54:55                                101 LOGOFF

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:54:44 SCOTT      EMP1                103 SESSION REC                               update scott.emp1 set sal=800 where empno=7369

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:54:36 SCOTT      EMP1                103 SESSION REC                               select * from scott.emp1

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:54:29                                100 LOGON                CREATE SESSION

比較可知，操作級別的審核明確記錄了action_name為select、update等，而會話級別的審核action_name只標(biāo)明為session rec，但sql_text仍記錄了會話中每一步操作的SQL語句。

8、取消審核，清理恢復(fù)

noaudit all on scott.emp1;

drop user audr1;

drop table scott.emp1 purge;

alter system set audit_trail=false scope=spfile;

alter system set audit_sys_operations=false scope=spfile;

重啟數(shù)據(jù)庫實(shí)例

清空aud$記錄

網(wǎng)頁名稱：Oracle標(biāo)準(zhǔn)數(shù)據(jù)庫審核
本文地址：http://jinyejixie.com/article14/gceede.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)建站、網(wǎng)站收錄、企業(yè)網(wǎng)站制作、營銷型網(wǎng)站建設(shè)、云服務(wù)器、網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

網(wǎng)站優(yōu)化排名
巴东县| 那坡县| 惠东县| 都昌县| 普安县| 塔城市| 宁城县| 三原县| 丹东市| 五大连池市| 七台河市| 宜宾县| 连云港市| 民权县| 确山县| 鹤庆县| 遵义县| 永胜县| 临夏市| 确山县| 卢湾区| 错那县| 武定县| 顺昌县| 通河县| 两当县| 青川县| 吴江市| 文山县| 呼玛县| 南充市| 闻喜县| 巴青县| 淮南市| 禹州市| 那曲县| 股票| 靖安县| 仪陇县| 旬邑县| 喜德县|