|
关于DBA直接操作用户的ORA-28000和ORA-28001:
用PROFILE自动产生:
- select u.username,
- u.account_status,
- u.lock_date,
- u.expiry_date,
- u.created,
- u.profile,
- u.initial_rsrc_consumer_group
- from cdb_users u
- where u.username like 'USER%';
-
- select sysdate+180 from dual;
-
- alter user user1 account lock;
-
- alter user user2 password expire;
-
-
- select * from cdb_profiles p where p.resource_type='KERNEL';
-
- select p.resource_name , p.limit
- from cdb_profiles p where
- p.profile='DEFAULT' and
- p.resource_type='PASSWORD';
复制代码 LOCKED TIMED:
- create profile profile1 limit
- FAILED_LOGIN_ATTEMPTS 1
- PASSWORD_LOCK_TIME 0.000694444;
-
- alter user user3 profile profile1;
复制代码
EXPIRED(GRACE) ORA-28002:
第一次expiry_date显示:创建时间+1分钟 (看不清楚, 从现在开始等过1分钟左右,会得到ORA-28002,意味着进入宽限期,再等1分钟在字典中EXPIRED(GRACE) 变成EXPIRED进入第二次)
第二次expiry_date显示当前时间+1分钟,意味着这个时间之后进入宽限期,可以不登录,但是一旦登录还有一分钟,逼着该密码。如果不改密码,expiry_date显示的时间不会变。
口令历史(最好使其过宽限期之后,再来改口令历史):
ALTER PROFILE "ROFILE1" LIMIT PASSWORD_reuse_time 0.013888889
PASSWORD_reuse_max 1;
在根容器上,挪动AUD$:
- create tablespace tbsaudit datafile '/u01/app/oracle/oradata/cdb2/pdb2_1/tbsaudit.dbf'
- size 30M autoextend on;
-
- alter table aud$ move tablespace tbsaudit;
复制代码
- select * from cdb_audit_trail;
- select * from dict where table_name like '%AUDIT%OPT%';
- select * from cdb_OBJ_AUDIT_OPTS o where o.owner='HR';
- audit update on hr.employees whenever successful;
- select * from cdb_PRIV_AUDIT_OPTS;
- select * from cdb_STMT_AUDIT_OPTS;
- select c.EXTENDED_TIMESTAMP, c.AUDIT_TYPE ,
- c.DB_USER , c.ACTION ,
- c.OBJECT_SCHEMA , c.OBJECT_NAME , c.SQL_TEXT
- from cdb_common_audit_trail c
- order by 1 desc ;
-
- select * from v$xml_audit_trail
- order by 6 desc;
复制代码- noaudit update on hr.employees ;
复制代码
权限审计:
DDL型的权限不可能做by session审计:
audit drop any table by hr by session;
- select c.EXTENDED_TIMESTAMP, c.AUDIT_TYPE ,
- c.DB_USER , c.ACTION ,
- c.OBJECT_SCHEMA , c.OBJECT_NAME , c.SQL_TEXT
- from cdb_common_audit_trail c
- where db_user = 'HR'
- order by 1 desc ;
-
- select * from cdb_PRIV_AUDIT_OPTS o where o.USER_NAME='HR';
-
- grant drop any table to hr;
- audit drop any table by hr ;
-
- select * from cdb_stmt_AUDIT_OPTS o where o.USER_NAME='HR';
-
- audit table by hr;
-
-
复制代码 注意:语句级审计要在设置之后的新会话中生效:
注意:11g默认有大量(可能是没有必要的)标准审计。
- select * from dba_priv_audit_opts;
- select * from dba_stmt_audit_opts;
- select count(*) from aud$;
- select tablespace_name from dba_Tables t where t.table_name='AUD'
- ;
复制代码- select * from cdb_audit_policies;
- create table taudit_fga ( a varchar2(200) ) ;
- CREATE OR REPLACE PROCEDURE proc_fga
- ( object_schema VARCHAR2, object_name VARCHAR2, policy_name
- VARCHAR2 ) AS
- begin
- insert into taudit_fga values
- (sys_context('userenv','os_user')||
- sys_context('userenv','current_user')||
- sys_context('userenv','session_user')||
- to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||
- sys_context('userenv','ip_address')||
- ' fga ' );
- end;
- select * from user_errors;
- begin
- dbms_fga.add_policy(
- object_schema => 'HR',
- object_name => 'EMPLOYEES',
- policy_name => 'POLICY1',
- audit_condition => 'department_id=90',
- audit_column => 'SALARY,COMMISSION_PCT',
- handler_schema => 'SYS',
- handler_module => 'PROC_FGA',
- statement_types => 'SELECT,UPDATE',
- audit_trail => dbms_fga.XML+dbms_fga.EXTENDED,
- audit_column_opts => dbms_fga.ANY_COLUMNS);
- end;
-
- ---
- select c.EXTENDED_TIMESTAMP, c.AUDIT_TYPE ,
- c.DB_USER , c.ACTION ,
- c.OBJECT_SCHEMA , c.OBJECT_NAME , c.SQL_TEXT
- from cdb_common_audit_trail c
- where db_user = 'HR'
- order by 1 desc ;
-
- select * from taudit_fga;
-
-
复制代码
基于值的审计:
- create table taudit_value ( a varchar2(200) ) ;
- CREATE OR REPLACE trigger trg_fga
- after update of salary on hr.employees
- referencing new as new old as old
- for each row
- begin
- if :old.salary != :new.salary then
- insert into taudit_value values
- (sys_context('userenv','os_user')||
- sys_context('userenv','current_user')||
- sys_context('userenv','session_user')||
- to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||
- sys_context('userenv','ip_address')||
- ' old value '||:old.salary||' new value '||:new.salary );
- end if;
- end;
- select * from user_errors;
- select * from user_triggers t where t.trigger_name='TRG_FGA';
- select * from taudit_value;
复制代码
用控制文件换外部表:
- sqlldr hr/oracle_4U@pdb2 control=/home/oracle/tpasswd.ctl external_table=GENERATE_ONLY
复制代码 passwd.dat:
- root:x:0:0:root:/root:/bin/bash
- bin:x:1:1:bin:/bin:/sbin/nologin
- daemon:x:2:2:daemon:/sbin:/sbin/nologin
- adm:x:3:4:adm:/var/adm:/sbin/nologin
- lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
- sync:x:5:0:sync:/sbin:/bin/sync
- shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
- halt:x:7:0:halt:/sbin:/sbin/halt
- mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
- uucp:x:10:14:uucp:/var/spool/uucp:/sbin/nologin
- operator:x:11:0:operator:/root:/sbin/nologin
- games:x:12:100:games:/usr/games:/sbin/nologin
- gopher:x:13:30:gopher:/var/gopher:/sbin/nologin
- ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
- nobody:x:99:99:Nobody:/:/sbin/nologin
- dbus:x:81:81:System message bus:/:/sbin/nologin
- usbmuxd:x:113:113:usbmuxd user:/:/sbin/nologin
- vcsa:x:69:69:virtual console memory owner:/dev:/sbin/nologin
- avahi-autoipd:x:170:170:Avahi IPv4LL Stack:/var/lib/avahi-autoipd:/sbin/nologin
- rpc:x:32:32:Rpcbind Daemon:/var/cache/rpcbind:/sbin/nologin
- rtkit:x:499:499:RealtimeKit:/proc:/sbin/nologin
- abrt:x:498:498::/etc/abrt:/sbin/nologin
- saslauth:x:497:495:"Saslauthd user":/var/empty/saslauth:/sbin/nologin
- postfix:x:89:89::/var/spool/postfix:/sbin/nologin
- rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
- nfsnobody:x:65534:65534:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
- haldaemon:x:68:68:HAL daemon:/:/sbin/nologin
- qemu:x:107:107:qemu user:/:/sbin/nologin
- apache:x:48:48:Apache:/var/www:/sbin/nologin
- avahi:x:70:70:Avahi mDNS/DNS-SD Stack:/var/run/avahi-daemon:/sbin/nologin
- ntp:x:38:38::/etc/ntp:/sbin/nologin
- pulse:x:496:494:PulseAudio System Daemon:/var/run/pulse:/sbin/nologin
- gdm:x:42:42::/var/lib/gdm:/sbin/nologin
- sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
- tcpdump:x:72:72::/:/sbin/nologin
- oracle:x:500:500::/home/oracle:/bin/bash
- mysql:x:27:27:MySQL Server:/var/lib/mysql:/sbin/nologin
- oradev:x:513:500::/home/oradev:/bin/bash
- vtl:x:514:514::/home/vtl:/bin/bash
- orawb:x:512:500::/home/orawb:/bin/bash
- radvd:x:75:75:radvd user:/:/sbin/nologin
- named:x:25:25:Named:/var/named:/sbin/nologin
- webalizer:x:67:67:Webalizer:/var/www/usage:/sbin/nologin
- ldap:x:55:55:LDAP User:/var/lib/ldap:/sbin/nologin
- vncviewer:x:2022:2022::/home/vncviewer:/bin/bash
- sanlock:x:179:179:sanlock:/var/run/sanlock:/sbin/nologin
- XXXX:x:179:179:sanlock:/var/run/sanlock:/sbin/nologin
- dovecot:x:97:97:Dovecot IMAP server:/usr/libexec/dovecot:/sbin/nologin
- dovenull:x:495:487
复制代码
tpasswd.ctl:
- LOAD DATA
- infile '/home/oracle/passwd.dat'
- APPEND
- INTO TABLE HR.TPASSWD
- FIELDS TERMINATED BY ':' OPTIONALLY ENCLOSED BY '"'
- trailing nullcols
- (
- I_USERNAME CHAR,
- I_PASSWORD CHAR,
- I_UID INTEGER EXTERNAL,
- I_GID INTEGER EXTERNAL,
- I_COMMENT char,
- I_HOME CHAR,
- I_SHELL CHAR
- )
复制代码
tpasswd.sql:
- CREATE TABLE tpasswd
- (
- "I_USERNAME" VARCHAR2(100),
- "I_PASSWORD" CHAR(1),
- "I_UID" NUMBER,
- "I_GID" NUMBER,
- "I_COMMENT" VARCHAR2(100),
- "I_HOME" VARCHAR2(80),
- "I_SHELL" VARCHAR2(60)
- )
- ORGANIZATION external
- (
- TYPE oracle_loader
- DEFAULT DIRECTORY dir1
- ACCESS PARAMETERS
- (
- RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
- BADFILE dir1:'passwd_%a_%p.bad'
- LOGFILE dir1:'tpasswd_%a_%p.log'
- READSIZE 1048576
- FIELDS TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"' LDRTRIM
- MISSING FIELD VALUES ARE NULL
- REJECT ROWS WITH ALL NULL FIELDS
- (
- "I_USERNAME" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "I_PASSWORD" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "I_UID" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "I_GID" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "I_COMMENT" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "I_HOME" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
- "I_SHELL" CHAR(255)
- TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"'
- )
- )
- location
- (
- 'passwd.dat'
- )
- )REJECT LIMIT UNLIMITED;
复制代码
tdate.dat:
- 1,2018-06-03:01:01:01
- 2,2018-06-03:02:02:02
复制代码
tdate.ctl:
- LOAD DATA
- infile '/home/oracle/dir1/tdate.dat'
- APPEND
- INTO TABLE HR.Tdate
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
- trailing nullcols
- (
- Id integer external(1),
- mydate date(19) "YYYY-MM-DD:HH24:MI:SS"
- )
复制代码
tdate.sql:
- CREATE TABLE hr.tdate
- (
- "ID" NUMBER,
- "MYDATE" DATE
- )
- ORGANIZATION external
- (
- TYPE oracle_loader
- DEFAULT DIRECTORY dir1
- ACCESS PARAMETERS
- (
- RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
- BADFILE 'tdate_%a_%p.bad'
- LOGFILE 'tdate_%a_%p.log'
- READSIZE 1048576
- FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
- MISSING FIELD VALUES ARE NULL
- REJECT ROWS WITH ALL NULL FIELDS
- (
- "ID" CHAR(1)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
- "MYDATE" CHAR(19)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
- DATE_FORMAT DATE MASK "YYYY-MM-DD:HH24:MI:SS"
- )
- )
- location
- (
- 'tdate.dat'
- )
- )REJECT LIMIT UNLIMITED
- ;
复制代码
LOADER外部表:
- select * from cdb_directories;
- create directory dir1 as '/home/oracle/dir1';
- create directory dir2 as '/home/oracle/dir2';
- grant read,write on directory dir1 to hr;
- grant read,write on directory dir2 to hr;
- CREATE TABLE hr.extab
- (employee_id NUMBER(4),
- first_name VARCHAR2(20),
- last_name VARCHAR2(25),
- hire_date DATE)
- ORGANIZATION EXTERNAL
- ( TYPE ORACLE_LOADER DEFAULT DIRECTORY dir1
- ACCESS PARAMETERS
- ( records delimited by newline
- badfile dir1:'extab%a_%p.bad'
- logfile dir1:'extab%a_%p.log'
- fields terminated by ','
- missing field values are null
- ( employee_id char , first_name char , last_name char ,
- hire_date char date_format date mask "dd-mon-yyyy"))
- LOCATION (dir1:'extab1.dat', dir2:'extab2.dat') )
- PARALLEL 4 REJECT LIMIT UNLIMITED;
- select * from hr.extab;
- select * from dba_tables t where t.table_name='EXTAB';
- select * from dba_external_tables t;
复制代码 |
|