【Oracle學(xué)習(xí)16】 Oracle安全性
用戶和模式(schema)相關(guān)，但并不相同。
- 用戶指通過某個(gè)用戶賬戶名建立實(shí)例的會(huì)話的個(gè)人。
- 用戶賬戶定義初始訪問權(quán)限及會(huì)話屬性。
- 模式是用戶賬戶所擁用的一組對(duì)象。創(chuàng)建一個(gè)用戶单绑，實(shí)際上也創(chuàng)建了一個(gè)同名的schema,其包含此賬戶下的對(duì)象。
Database User Accounts
Predefined Administrative Accounts
Administrative Privileges
16.1 創(chuàng)建和管理數(shù)據(jù)庫用戶賬戶
用戶賬戶:
--創(chuàng)建表空間
create tablespace example datafile '/u01/app/oracle/oradata/orcl/example.dbf' size 50m;
select name from v$datafile;
select file_id,file_name,tablespace_name,autoextensible from dba_data_files;
select tablespace_name,block_size,status,contents,logging from dba_tablespaces;
select * from dba_tablespaces where tablespace_name like 'EXAMPLE%';
--創(chuàng)建用戶
alter profile default limit PASSWORD_VERIFY_FUNCTION null;
create user OCP IDENTIFIED by oracle default tablespace EXAMPLE PROFILE default QUOTA 10M on EXAMPLE;
alter user OCP QUOTA UNLIMITED ON EXAMPLE;
grant unlimited tablespace to OCP;
--revoke unlimited tablespace from OCP:
--Profile
select * from dba_users where username='HR';
16.1.1 用戶賬戶屬性
- 用戶名:
- 身份驗(yàn)證方法:
- 默認(rèn)表空間:
- 表空間配額:
- 臨時(shí)表空間:
- 用戶配置文件:
- 賬戶狀態(tài):
1.用戶名
用戶名字母開頭曹宴，不能為關(guān)鍵字询张，會(huì)自動(dòng)轉(zhuǎn)為大寫。 除非使用了雙引號(hào)括起來浙炼。
create user join identified by pwd123; --User JOIN 已創(chuàng)建份氧。
create user "join" identified by pwd123; --User "join" 已創(chuàng)建。
SQL>select username,created from dba_users where lower(username) like 'join%';
USERNAME CREATED
-------------------
join 2020-02-05 13:19:01
JOIN 2020-02-05 13:18:55
2)默認(rèn)表空間
- 創(chuàng)建用戶時(shí)若沒有指定表空間弯屈，則使用默認(rèn)表空間蜗帜。如建庫時(shí)沒有指定默認(rèn)表空間，則會(huì)使用system的設(shè)置(USERS)為默認(rèn)表空間资厉。
- 大多數(shù)據(jù)用戶不需要任何配額(quota)厅缺，因?yàn)樗麄儾粍?chuàng)建對(duì)象。
- 創(chuàng)建段宴偿，用戶必須有create table 權(quán)限湘捎，并且有表空間的配額。
#創(chuàng)建表空間
create tablespace example datafile '/u01/app/oracle/oradata/orcl/example.dbf' size 50m;
select file_id,file_name,tablespace_name,autoextensible from dba_data_files;
select * from dba_tablespaces where tablespace_name like 'EXAMPLE%';
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
..
##
SQL> select tablespace_name,block_size,status,contents,logging from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS LOGGING
------------------------------------------------------------ ---------- ------------------ ------------------------------------------ ------------------
SYSTEM 8192 ONLINE PERMANENT LOGGING
SYSAUX 8192 ONLINE PERMANENT LOGGING
UNDOTBS1 8192 ONLINE UNDO LOGGING
TEMP 8192 ONLINE TEMPORARY NOLOGGING
USERS 8192 ONLINE PERMANENT LOGGING
MYTEST 8192 ONLINE PERMANENT LOGGING
JINLIAN_UNDO 8192 ONLINE UNDO LOGGING
#alter database default tablespace tablespace_name;
SQL> select username,created,default_tablespace,temporary_tablespace from dba_users where username = 'JOIN';
USERNAME CREATED DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------- ------------------------------------------------------------ ------------------------
JOIN 2020-02-05 13:18:55 USERS TEMP
配額(Quota):
Authenticating Users
Administrator Authentication
OS Authentication and OS Groups
Administrative Privileges
1.操作系統(tǒng)及口令文件身份驗(yàn)證
- 只能是管理員使用。
- 口令文件驗(yàn)證: connect username/pwd @db as [SYSOPER|SYSDBA];
- 操作系統(tǒng)身份驗(yàn)證: connect / as [SYSOPER|SYSDBA]; oracle并不存操作系統(tǒng)密碼旦事。
GRANT [SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM] TO username;
#查看SYSDBA/SYSOPER權(quán)限的人
SQL> select username,sysdba,sysoper,sysasm,sysbackup,sysdg,syskm,account_status from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM SYSBACKUP SYSDG SYSKM ACCOUNT_STATUS
---------- ---------- ---------- ---------- ---------- ---------- -------------------------
SYS TRUE TRUE FALSE FALSE FALSE FALSE OPEN
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE OPEN
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE OPEN
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE OPEN
2.口令身份驗(yàn)證
CONNECT username /password [@db_alias];
alter user username IDENTIFIED By password;
3.外部身份驗(yàn)證
- 若是用外部身份創(chuàng)建用戶魁巩，oracle會(huì)將驗(yàn)證委托給外部服務(wù)。如kerberos;
- 若沒有啟用Advanced Security,則唯一可用的外部驗(yàn)證族檬，即是OS驗(yàn)證歪赢。
SQL> select name,value from v$parameter where name = 'os_authent_prefix';
NAME VALUE
------------------------------------------------------------
os_authent_prefix ops$
#創(chuàng)建操作系統(tǒng)用戶可登錄oracle化戳，如已有OS用戶teachertao
SQL> create user ops$teachertao identified externally;
User created.
SQL> grant create session to ops$teachertao;
Grant succeeded.
sqlplus /
16.1.3 創(chuàng)建賬戶
創(chuàng)建賬戶
創(chuàng)建賬戶
創(chuàng)建賬戶:
SQL> create profile developer_profile limit // developer_profile為資源文件名
FAILED_LOGIN_ATTEMPTS 3 //指定鎖定用戶的登錄失敗次數(shù)
PASSWORD_LOCK_TIME 5 //指定用戶被鎖定天數(shù)
PASSWORD_LIFE_TIME 30 //指定口令可用天數(shù)
#
create user alois identified by alois default tablespace users;
create user teachertao identified by teachertao
default tablespace users temporary tablespace temp
quota 100m on users
profile developer_profile
password expire
account unlock;
#修改profile
alter user alois profile default;
#解鎖
alter user alois account unlock; --status將為open
select username,account_status from dba_users where username = 'ALOIS';
#修改密碼
alter user alois identified by oracle;
##
drop user teachertao; --模式為空時(shí)才成功
drop user teachertao cascade; --即使用戶有角色也刪除
16.2 授予和撤消權(quán)限
- 默認(rèn)方式点楼，非授權(quán)用戶不能做oracle任何操作扫尖，甚至不能成功連接。
- 權(quán)限分類: 系統(tǒng)權(quán)限及對(duì)象權(quán)限掠廓。
- 系統(tǒng)權(quán)限的撤消不級(jí)聯(lián),對(duì)象權(quán)限的撤消級(jí)聯(lián)换怖。
權(quán)限分為兩組：
- 系統(tǒng)權(quán)限：用戶可執(zhí)行影響數(shù)據(jù)字典的操作。如創(chuàng)建表蟀瞧、創(chuàng)建索引沉颂、連接實(shí)例等
- 對(duì)象權(quán)限： 用戶執(zhí)行影響數(shù)據(jù)的操作。 如讀取視圖悦污，可更新某些列铸屉、執(zhí)行存儲(chǔ)過程等
Privileges
Revoking System Privilegeswith ADMIN OPTION
Revoking Object Privilegeswith GRANT OPTION
#新建用戶不能做任何操作
SQL> conn teachertao/teachertao
ERROR:
ORA-01045: user TEACHERTAO lacks CREATE SESSION privilege; logon denied
16.2.1 系統(tǒng)權(quán)限
常用的系統(tǒng)權(quán)限：
CREATE SESSION 創(chuàng)建會(huì)話
CREATE SEQUENCE 創(chuàng)建序列
CREATE SYNONYM 創(chuàng)建同名對(duì)象
CREATE TABLE 在用戶模式中創(chuàng)建表
CREATE ANY TABLE 在任何模式中創(chuàng)建表
ALERT DATABASE 允許修改物理結(jié)構(gòu)
DROP TABLE 在用戶模式中刪除表
DROP ANY TABLE 在任何模式中刪除表
CREATE PROCEDURE 創(chuàng)建存儲(chǔ)過程
EXECUTE ANY PROCEDURE 執(zhí)行任何模式的存儲(chǔ)過程
CREATE USER 創(chuàng)建用戶
DROP USER 刪除用戶
CREATE VIEW 創(chuàng)建視圖
SQL> select count(*) from system_privilege_map;
COUNT(*)
----------
256
SQL> grant create session,alter session, create table , create view,create synonym,create cluster,create database link, create sequence,
create trigger,create type,create procedure ,create operator to teachertao;
#
SELECT grantee,privilege,admin_option FROM dba_sys_privs WHERE grantee IN ( 'TEACHERTAO' ) order by grantee;
#with admin option有授權(quán)的權(quán)限，并且撤消不會(huì)級(jí)聯(lián)切端。
SQL> grant create table to teachertao with admin option; --teachertao有授權(quán)的權(quán)限
Grant succeeded.
SQL> conn teachertao/teachertao
Connected.
grant create table to join;
grant select any table to JOIN; --除SYS模式中的表之外
SELECT grantee,privilege,admin_option FROM dba_sys_privs WHERE grantee IN ( 'TEACHERTAO','JOIN' ) order by grantee;
#回收系統(tǒng)權(quán)限
REVOKE {privilege | role} FROM {user_name | role_name | PUBLIC}
16.2.2 對(duì)象權(quán)限
對(duì)象權(quán)限允許對(duì)表和對(duì)象執(zhí)行 彻坛；
- SELECT,INSERT,UPDATE,DELETE,ALERT,EXECUTE權(quán)限。
- all包括所有權(quán)限
語法：
GRANT PRIVILEGE on schema.obj TO username WITH GRANT OPTION;
select, update, insert, alter, index, delete, all //all包括所有權(quán)限
grant select on hr.employees to teachertao;
grant update (salary ) on hr.employees to teachertao; --對(duì)列授權(quán)
grant all on regions to scott; -- 授權(quán) hr.regions 全部對(duì)象權(quán)限給scott;
#
revoke delete on hr.regions from scott;
測(cè)試:
#創(chuàng)建用戶
create user alois identified by alois default tablespace users;
create user anja identified by oracle;
create user alois identified by alois;
grant create session to alois;
SQL> create table t1 (c1 number);
ORA-01031: insufficient privileges
grant create table to alois;
#再試
SQL> create table t1 (c1 date);
Table created.
SQL> drop table t1; --能建就可以DROP ?踏枣？
Table dropped.
SQL> create table t1 (c1 date) segment creation immediate; --error
ORA-01950: no privileges on tablespace 'USERS'
SQL> alter user alois quota 10m on USERS; --增加quota,SYSDBA
SQL> create table t1 (c1 date) segment creation immediate;
Table created.
SQL> grant select on t1 to anja;
##
SQL> select grantee,privilege,grantor,grantable,table_name from dba_tab_privs where owner='ALOIS' and table_name='T1';
GRANTEE PRIVILEGE GRANTOR GRANTABLE TABLE_NAME
-----------------------------------------------------------
ANJA SELECT ALOIS NO T1
SQL> select * from dba_sys_privs where grantee='ALOIS';
GRANTEE PRIVILEGE ADMIN_ COMMON INHERI
-----------------------------------------------------------
ALOIS CREATE TABLE NO NO NO
ALOIS CREATE SESSION NO NO NO
16.3 創(chuàng)建和管理角色
角色：
- 是一組系統(tǒng)權(quán)限或?qū)ο髾?quán)限昌屉，可以作為一個(gè)單元來授予或撤消，可以在會(huì)話中臨時(shí)激活或禁用已授予的權(quán)限茵瀑。若將角色賦給一個(gè)用戶间驮，這個(gè)用戶就擁有了這個(gè)角色中的所有權(quán)限。
- 角色不是模式對(duì)象马昨，不屬于任何人蜻牢，和用戶名同一個(gè)命名空間烤咧，故不能和用戶同名。
- 角色也可以帶有口令抢呆。
Using Roles to Manage Privileges
Assigning Privileges to Roles andAssigning Roles to Users
Predefined Roles
Secure Roles
16.3.1 創(chuàng)建角色并授予角色權(quán)限
- DBA: 擁有全部特權(quán)煮嫌，是系統(tǒng)最高權(quán)限，只有DBA才可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)抱虐。
- RESOURCE:擁有Resource權(quán)限的用戶只可以創(chuàng)建實(shí)體昌阿，不可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)。
- CONNECT:擁有Connect權(quán)限的用戶只可以登錄Oracle恳邀，不可以創(chuàng)建實(shí)體懦冰，不可以創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)。
對(duì)于普通用戶：授予connect, resource權(quán)限谣沸。
對(duì)于DBA管理用戶：授予connect刷钢，resource, dba權(quán)限。
示例:
語法： CREATE ROLE secure_application_role
IDENTIFIED USING <security_procedure_name>;
#
sql>select * from role_sys_privs where role='DBA';
create role hr_junior;
create role hr_senior;
create role hr_manager;
create role hr_role identified by oracle; --角色也可以帶有密碼
create role hr_role identified by oracle;
grant create table to hr_role;
grant select table to hr_role; --無select table 權(quán)限
grant connect to hr_role;
#
SQL> grant create on hr.employees to hr_junior; --error
ORA-02224: EXECUTE privilege not allowed for tables
#
grant select on hr.employees to hr_junior;
grant hr_junior to hr_senior with admin option ; --ok
grant hr_junior to hr_senior with grant option; --error,only the ADMIN OPTION can be specified
grant insert,update,delete on hr.employees to hr_senior;
grant hr_senior to hr_manager;
grant all on hr.employees to hr_manager;
16.3.2 預(yù)定義的角色
常見的角色:
- 1．CONNECT, RESOURCE, DBA
這些預(yù)定義角色主要是為了向后兼容乳附。其主要是用于數(shù)據(jù)庫管理内地。oracle建議用戶自己設(shè)計(jì)數(shù)據(jù)庫管理和安全的權(quán)限規(guī)劃，而不要簡(jiǎn)單的使用這些預(yù)定角色赋除。 - 2．DELETE_CATALOG_ROLE阱缓， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE
這些角色主要用于訪問數(shù)據(jù)字典視圖和包举农。 - 3．EXP_FULL_DATABASE荆针， IMP_FULL_DATABASE
這兩個(gè)角色用于數(shù)據(jù)導(dǎo)入導(dǎo)出工具的使用。 - 4．AQ_USER_ROLE颁糟， AQ_ADMINISTRATOR_ROLE
AQ:Advanced Query航背。這兩個(gè)角色用于oracle高級(jí)查詢功能。 - 5． SNMPAGENT
用于oracle enterprise manager和Intelligent Agent - 6．RECOVERY_CATALOG_OWNER
用于創(chuàng)建擁有恢復(fù)庫的用戶棱貌。關(guān)于恢復(fù)庫的信息玖媚，參考o(jì)racle文檔《Oracle9i User-Managed Backup and Recovery Guide》 - 7．HS_ADMIN_ROLE
A DBA using Oracle’s heterogeneous services feature needs this role to access appropriate tables in the data dictionary.
-8. PUBLIC ： 此角色始終授予每個(gè)數(shù)據(jù)庫用戶賬戶。grant select on hr.regions to public ;所有用戶都將有此select 權(quán)限键畴。
說明：
角色管理:
#查詢用戶擁有哪些權(quán)限：
SQL> select * from dba_role_privs;
SQL> select * from dba_sys_privs;
SQL> select * from role_sys_privs;
1.建一個(gè)角色
sql>create role role1;
2.授權(quán)給角色
sql>grant create any table,create procedure to role1;
3.授予角色給用戶
sql>grant role1 to user1;
4.查看角色所包含的權(quán)限
sql>select * from role_sys_privs;
5.創(chuàng)建帶有口令以角色(在生效帶有口令的角色時(shí)必須提供口令)
sql>create role role1 identified by password1;
6.修改角色：是否需要口令
sql>alter role role1 not identified;
sql>alter role role1 identified by password1;
7.設(shè)置當(dāng)前用戶要生效的角色
(注：角色的生效是一個(gè)什么概念呢最盅？假設(shè)用戶a有b1,b2,b3三個(gè)角色，那么如果b1未生效起惕，則b1所包含的權(quán)限對(duì)于a來講是不擁有的涡贱，只有角色生效了，角色內(nèi)的權(quán)限才作用于用戶惹想，最大可生效角色數(shù)由參數(shù)MAX_ENABLED_ROLES設(shè)定问词；在用戶登錄后，oracle將所有直接賦給用戶的權(quán)限和用戶默認(rèn)角色中的權(quán)限賦給用戶嘀粱。）
sql>set role role1;//使role1生效
sql>set role role,role2;//使role1,role2生效
sql>set role role1 identified by password1;//使用帶有口令的role1生效
sql>set role all;//使用該用戶的所有角色生效
sql>set role none;//設(shè)置所有角色失效
sql>set role all except role1;//除role1外的該用戶的所有其它角色生效激挪。
sql>select * from SESSION_ROLES;//查看當(dāng)前用戶的生效的角色辰狡。
8.修改指定用戶，設(shè)置其默認(rèn)角色
sql>alter user user1 default role role1;
sql>alter user user1 default role all except role1;
詳見oracle參考文檔
9.刪除角色
sql>drop role role1;
角色刪除后垄分，原來擁用該角色的用戶就不再擁有該角色了宛篇，相應(yīng)的權(quán)限也就沒有了。
說明:
1)無法使用WITH GRANT OPTION為角色授予對(duì)象權(quán)限薄湿。
2)可以使用WITH ADMIN OPTION 為角色授予系統(tǒng)權(quán)限叫倍，取消時(shí)不是級(jí)聯(lián)。
16.3.3 啟用角色
在用戶登錄后豺瘤，oracle將所有直接賦給用戶的權(quán)限和用戶默認(rèn)角色中的權(quán)限賦給用戶吆倦。這有一定安全風(fēng)險(xiǎn)，可以通過將角色設(shè)置為非默認(rèn)來修改此行為坐求。
create user JOIN identified by JOIN;
grant hr_senior to JOIN ;
SQL> select * from dba_role_privs where grantee = 'JOIN';
GRANTEE GRANTED_ROLE ADMIN_ DELEGA DEFAUL COMMON INHERI
-----------------------------------------------------------
JOIN HR_SENIOR NO NO YES NO NO
#
alter user JOIN default role none;
grant connect to JOIN;
alter user JOIN default role connect;
select * from dba_role_privs where grantee = 'JOIN'; --sysdba
SQL> conn JOIN/JOIN
Connected.
SQL> set ROLE hr_senior ;
Role set.
16.3.4 權(quán)限分析
重要視圖：
- dba_used_privs
- dba_unused_privs
Privilege Analysis
Privilege Analysis Flow
示例：
create role usr_role;
create role mgr_role;
-- 給角色授權(quán)
grant create session to usr_role;
grant select on alois.t1 to usr_role;
grant usr_role to mgr_role with admin option;
grant all on alois.t1 to mgr_role;
-- 創(chuàng)建用戶
create user afra identified by oracle default tablespace USER quota 10m on USER;
create user anja identified by oracle;
--
grant mgr_role to AFRA; --sysdba
conn afra/oracle;
grant usr_role to anja;
insert into alois.t1 values(sysdate);
commit;
#ANJA可以訪問alois.t1;
SQL> conn anJA/oracle -- 用戶名會(huì)自動(dòng)變?yōu)榇髮慉NJA蚕泽，密碼會(huì)區(qū)分大小寫。
select * from alois.t1;
SQL> insert into alois.t1 values(sysdate);
ORA-01031: insufficient privileges
# 查看
SQL>select * from dba_role_privs where granted_role in ('USR_ROLE','MGR_ROLE');
SQL>select grantee,owner,table_name,grantor,privilege,grantable,type from dba_tab_privs where grantee in ('USR_ROLE');
SQL>select * from dba_sys_privs where grantee in ('USR_ROLE');
16.4 創(chuàng)建和管理配置文件
- 配置文件(profile)可以實(shí)施口令策略桥嗤。
- 每個(gè)用戶都有profile须妻，默認(rèn)是Default Profile ，其包括sys和system砸逊。
- 用戶一次只分配一個(gè)profile璧南。
- Pofile作用： 控制資源消費(fèi)情況掌逛， 管理賬號(hào)狀態(tài)及密碼過期师逸。
Implementing Password Security Features
Creating a Password Profile
Supplied Password Verification Functions
Modifications to the Default Profile
Applying the Principle of Least Privilege
SQL> create profile developer_profile limit // developer_profile為資源文件名
FAILED_LOGIN_ATTEMPTS 3 //指定鎖定用戶的登錄失敗次數(shù)
PASSWORD_LOCK_TIME 5 //指定用戶被鎖定天數(shù)
PASSWORD_LIFE_TIME 30 //指定口令可用天數(shù)
#
create user alois identified by alois profile developer_profile;
SQL>select username,profile from dba_users;
alter user alois profile developer_profile;
16.4.1 口令配置文件的限制
select username,profile from dba_users;
USERNAME PROFILE
------------------------------------------------------
SYS DEFAULT
SYSTEM DEFAULT
SYSBACKUP DEFAULT
...
16.4.2 創(chuàng)建和分配配置文件
utlpwdmg.sql 腳本會(huì)創(chuàng)建一組函數(shù)，來提供各種程度的密碼復(fù)雜性檢查豆混。
#查看腳本
cat $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
#
@/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/utlpwdmg.sql
describe sys.oral2c_verify_function;
##關(guān)閉復(fù)雜密碼認(rèn)證
alter profile default limit PASSWORD_VERIFY_FUNCTION null;
select * from dba_profiles where resource_name = 'PASSWORD_VERIFY_FUNCTION';
#建立profile 出錯(cuò)2次篓像，鎖定10分鐘
create profile two_wrong limit failed_login_attempts 2 password_lock_time 10/1440;
#修改用戶profile
alter user alois profile two_wrong;
SQL> conn alois/cc; --錯(cuò)誤>=2次被鎖
ERROR:
ORA-28000: the account is locked
#
SQL> select username,account_status,lock_date,expiry_date,default_tablespace from dba_users where username = 'ALOIS';
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE
----------------------------------------------------------------------------------------------------------------------
ALOIS LOCKED(TIMED) 2020-02-06 14:16:41 2020-08-03 21:28:36 USERS
#解鎖
alter user alois account unlock; --status將為open
SQL> select username,account_status from dba_users where username = 'ALOIS';
#修改密碼
alter user alois identified by oracle;
select * from dba_profiles ;
drop profile TWO_WRONG ; --error ;has users assigned, cannot drop without CASCADE
drop profile TWO_WRONG cascade ;
select username,account_status,profile from dba_users where username = 'ALOIS'; --profile變?yōu)閐efault
drop user ALOIS cascade;
drop user anja;
16.5 DBA對(duì)安全和審核的責(zé)任
16.5.1 審核的原因
16.5.2 審核技術(shù)
- Audit Vault :
- 標(biāo)準(zhǔn)數(shù)據(jù)庫審核： AUDIT_TRAIL設(shè)置為DB;信息寫入SYS.AUD$中。
- 細(xì)粒度審核(Fine Grained Auditing, FGA)：可跟蹤某些行或列的訪問皿伺。
- 基于值的審核：
- 統(tǒng)一審核：
- 強(qiáng)制審核： 無論是否配置员辩，都會(huì)有的審核操作。如create /drop prolicy鸵鸥，SYSDBA/SYSOPER等管理者的一些操作奠滑。
16.6 啟用標(biāo)準(zhǔn)數(shù)據(jù)庫審核和統(tǒng)一審核
16.6.1 啟用標(biāo)準(zhǔn)審核
SQL> select name,value from v$parameter where name like 'audit%';
NAME VALUE
----------------------------------------------------
audit_sys_operations TRUE
audit_file_dest /u01/app/oracle/admin/orcl/adump
audit_syslog_level (null)
audit_trail DB
audit all statements by HR;
audit select ,insert,update ,delete on hr.employees;
16.6.2 啟用統(tǒng)一審核
**開啟統(tǒng)一審核： **
#檢查是否開啟
SQL> select parameter,value from v$option where parameter='Unified Auditing';
PARAMETER VALUE
--------------------------------------------------------------
Unified Auditing FALSE
#linux環(huán)境
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
結(jié)果：
...
mv /u01/app/oracle/product/12.2.0/db_1/rdbms/lib/oracle /u01/app/oracle/product/12.2.0/db_1/bin/oracle
chmod 6751 /u01/app/oracle/product/12.2.0/db_1/bin/oracle
#重啟偵聽器和數(shù)據(jù)庫實(shí)例
alter system checkpoint;
alter system archive log current;
shutdown normal;
lsnrctl stop ;lsnrctl start
SQL> startup
##
SQL> select parameter,value from v$option where parameter='Unified Auditing';
PARAMETER VALUE
--------------------------------------------------------------
Unified Auditing TRUE
創(chuàng)建策略:
create audit policy privpol privileges select any table,create trigger;
create audit policy actpol actions drop table,alter trigger;
create audit policy rolepol roles dba;
create audit policy mixedpol privileges select any table,create trigger actions drop table,alter trigger roles dba;
#僅用于某個(gè)表
create audit policy emppol actions insert,update,delete on scott.emp;
啟用策略:
SQL> audit policy privpol; --全部啟用
Audit succeeded.
audit policy privpol;
audit policy actpol whenever successful; --成功時(shí)記錄
audit policy rolepol by scott,system; --為scott,system用戶啟用
audit policy mixedpol whenever not successful; --不成功時(shí)記錄
noaudit policy actpol; --禁用此policy
#當(dāng)前SGA記錄轉(zhuǎn)存到數(shù)據(jù)庫審核記錄中。
exec dbms_audit_mgmt.flush_unified_audit_trail;
示例：
audit policy ora_account_mgmt;
create audit policy aud_dba_role roles dba;
audit policy aud_dba_role by system;
select policy_name,enabled_opt,user_name from audit_unified_enabled_policies;
#
create user x identified by y#1234567;
alter system set open_cursors=300 scope=memory;
#查詢審核記錄
exec dbms_audit_mgmt.flush_unified_audit_trail;
select dbusername,sql_text from unified_audit_trail;
#嘗試修改審核記錄
SQL> select table_name from dba_tables where owner='AUDSYS';
TABLE_NAME
--------------------------------------------------------------------------------
AUD$UNIFIED
SQL> delete from audsys.'&table_name'; --會(huì)出錯(cuò)
Enter value for table_name: AUD$UNIFIED
delete from audsys.'AUD$UNIFIED'
ORA-00903: invalid table name
SQL> truncate table audsys.'&table_name'; --會(huì)出錯(cuò)
Enter value for table_name: AUD$UNIFIED
truncate table audsys.'AUD$UNIFIED'
ORA-00903: invalid table name
##清除審核記錄
select count(*) from unified_audit_trail;
execute dbms_audit_mgmt.clean_audit_trial(dbms_audit_mgmt.audit_trail_all,false);
select count(*) from unified_audit_trail;
16.7 總結(jié)
16.8 測(cè)試
角色:
create role hr_role identified by oracle; --角色也可以帶有密碼
create role hr_role identified by oracle;
grant create table to hr_role;
grant select table to hr_role; --無select table 權(quán)限
grant connect to hr_role;
