Bo's Oracle Station

查看: 2607|回复: 0

课程第54/55次(2018-06-03星期日上下午)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-6-3 09:51:13 | 显示全部楼层 |阅读模式
关于DBA直接操作用户的ORA-28000和ORA-28001:
a.png

用PROFILE自动产生:
  1. select  u.username,
  2.             u.account_status,
  3.             u.lock_date,
  4.             u.expiry_date,
  5.             u.created,
  6.             u.profile,
  7.             u.initial_rsrc_consumer_group            
  8.   from cdb_users u
  9.   where u.username like 'USER%';
  10.   
  11.   select  sysdate+180 from dual;
  12.   
  13.   alter user user1 account lock;
  14.   
  15.   alter user user2 password expire;
  16.   
  17.    
  18.   select  * from cdb_profiles  p where  p.resource_type='KERNEL';
  19.   
  20.   select  p.resource_name ,  p.limit  
  21.   from cdb_profiles  p where
  22.    p.profile='DEFAULT' and
  23.    p.resource_type='PASSWORD';
复制代码
LOCKED TIMED:

  1.    create profile profile1 limit
  2.    FAILED_LOGIN_ATTEMPTS 1   
  3.    PASSWORD_LOCK_TIME 0.000694444;
  4.    
  5.    alter user user3 profile profile1;
复制代码

b.png



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$:

  1. create tablespace tbsaudit datafile  '/u01/app/oracle/oradata/cdb2/pdb2_1/tbsaudit.dbf'
  2. size 30M autoextend on;

  3. alter table aud$ move tablespace tbsaudit;
复制代码

  1. select  * from cdb_audit_trail;

  2. select  * from dict where table_name like '%AUDIT%OPT%';

  3. select  * from cdb_OBJ_AUDIT_OPTS o where o.owner='HR';

  4. audit update on hr.employees  whenever successful;

  5. select  * from cdb_PRIV_AUDIT_OPTS;

  6. select  * from cdb_STMT_AUDIT_OPTS;

  7. select  c.EXTENDED_TIMESTAMP,   c.AUDIT_TYPE    ,
  8. c.DB_USER , c.ACTION ,  
  9. c.OBJECT_SCHEMA  ,  c.OBJECT_NAME , c.SQL_TEXT
  10.   from cdb_common_audit_trail c
  11.   order by 1 desc ;
  12.   
  13.   select  * from v$xml_audit_trail
  14.   order by 6 desc;
复制代码
  1.   noaudit update on hr.employees ;
复制代码

权限审计:
DDL型的权限不可能做by session审计:

audit  drop any table by hr by session;
a.png

  1. select  c.EXTENDED_TIMESTAMP,   c.AUDIT_TYPE    ,
  2. c.DB_USER , c.ACTION ,  
  3. c.OBJECT_SCHEMA  ,  c.OBJECT_NAME , c.SQL_TEXT
  4.   from cdb_common_audit_trail c
  5.   where db_user = 'HR'
  6.   order by 1 desc ;
  7.   
  8.   select  * from cdb_PRIV_AUDIT_OPTS o where o.USER_NAME='HR';
  9.   
  10.   grant drop any table to hr;

  11.   audit  drop any table by hr ;
  12.   
  13.   select  * from cdb_stmt_AUDIT_OPTS o where o.USER_NAME='HR';
  14.   
  15.   audit table by hr;
  16.   
  17.   
复制代码
注意:语句级审计要在设置之后的新会话中生效:

注意:11g默认有大量(可能是没有必要的)标准审计。

  1. select  * from dba_priv_audit_opts;

  2. select  * from dba_stmt_audit_opts;

  3. select count(*) from aud$;

  4. select  tablespace_name from dba_Tables t where t.table_name='AUD'
  5. ;
复制代码
  1. select  * from cdb_audit_policies;

  2. create table taudit_fga ( a  varchar2(200)  ) ;

  3. CREATE OR REPLACE PROCEDURE proc_fga
  4.   ( object_schema VARCHAR2, object_name VARCHAR2, policy_name
  5. VARCHAR2 ) AS
  6. begin
  7.   insert into  taudit_fga  values
  8.    (sys_context('userenv','os_user')||
  9.     sys_context('userenv','current_user')||
  10.     sys_context('userenv','session_user')||
  11.     to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||
  12.     sys_context('userenv','ip_address')||
  13.       ' fga '        );
  14. end;

  15. select  * from user_errors;

  16. begin
  17.   dbms_fga.add_policy(
  18.   object_schema => 'HR',
  19.   object_name => 'EMPLOYEES',
  20.   policy_name => 'POLICY1',
  21.   audit_condition => 'department_id=90',
  22.   audit_column => 'SALARY,COMMISSION_PCT',
  23.   handler_schema => 'SYS',
  24.   handler_module => 'PROC_FGA',
  25.   statement_types => 'SELECT,UPDATE',
  26.   audit_trail => dbms_fga.XML+dbms_fga.EXTENDED,
  27.   audit_column_opts => dbms_fga.ANY_COLUMNS);
  28. end;

  29. ---
  30. select  c.EXTENDED_TIMESTAMP,   c.AUDIT_TYPE    ,
  31. c.DB_USER , c.ACTION ,  
  32. c.OBJECT_SCHEMA  ,  c.OBJECT_NAME , c.SQL_TEXT
  33.   from cdb_common_audit_trail c
  34.   where db_user = 'HR'
  35.   order by 1 desc ;
  36.   
  37.   select  * from taudit_fga;
  38.   
  39.   
复制代码

基于值的审计:
  1. create table taudit_value ( a  varchar2(200)  ) ;

  2. CREATE OR REPLACE  trigger trg_fga
  3. after update of salary on  hr.employees
  4. referencing new as new old as old
  5. for each row
  6. begin
  7.    if :old.salary != :new.salary then
  8.   insert into  taudit_value  values
  9.    (sys_context('userenv','os_user')||
  10.     sys_context('userenv','current_user')||
  11.     sys_context('userenv','session_user')||
  12.     to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||
  13.     sys_context('userenv','ip_address')||
  14.       ' old value '||:old.salary||' new value '||:new.salary    );
  15.      end if;
  16. end;

  17. select  * from user_errors;

  18. select  * from user_triggers t where t.trigger_name='TRG_FGA';

  19. select * from taudit_value;
复制代码

用控制文件换外部表:
  1. sqlldr  hr/oracle_4U@pdb2 control=/home/oracle/tpasswd.ctl external_table=GENERATE_ONLY
复制代码
passwd.dat:

  1. root:x:0:0:root:/root:/bin/bash
  2. bin:x:1:1:bin:/bin:/sbin/nologin
  3. daemon:x:2:2:daemon:/sbin:/sbin/nologin
  4. adm:x:3:4:adm:/var/adm:/sbin/nologin
  5. lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
  6. sync:x:5:0:sync:/sbin:/bin/sync
  7. shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
  8. halt:x:7:0:halt:/sbin:/sbin/halt
  9. mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
  10. uucp:x:10:14:uucp:/var/spool/uucp:/sbin/nologin
  11. operator:x:11:0:operator:/root:/sbin/nologin
  12. games:x:12:100:games:/usr/games:/sbin/nologin
  13. gopher:x:13:30:gopher:/var/gopher:/sbin/nologin
  14. ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
  15. nobody:x:99:99:Nobody:/:/sbin/nologin
  16. dbus:x:81:81:System message bus:/:/sbin/nologin
  17. usbmuxd:x:113:113:usbmuxd user:/:/sbin/nologin
  18. vcsa:x:69:69:virtual console memory owner:/dev:/sbin/nologin
  19. avahi-autoipd:x:170:170:Avahi IPv4LL Stack:/var/lib/avahi-autoipd:/sbin/nologin
  20. rpc:x:32:32:Rpcbind Daemon:/var/cache/rpcbind:/sbin/nologin
  21. rtkit:x:499:499:RealtimeKit:/proc:/sbin/nologin
  22. abrt:x:498:498::/etc/abrt:/sbin/nologin
  23. saslauth:x:497:495:"Saslauthd user":/var/empty/saslauth:/sbin/nologin
  24. postfix:x:89:89::/var/spool/postfix:/sbin/nologin
  25. rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
  26. nfsnobody:x:65534:65534:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
  27. haldaemon:x:68:68:HAL daemon:/:/sbin/nologin
  28. qemu:x:107:107:qemu user:/:/sbin/nologin
  29. apache:x:48:48:Apache:/var/www:/sbin/nologin
  30. avahi:x:70:70:Avahi mDNS/DNS-SD Stack:/var/run/avahi-daemon:/sbin/nologin
  31. ntp:x:38:38::/etc/ntp:/sbin/nologin
  32. pulse:x:496:494:PulseAudio System Daemon:/var/run/pulse:/sbin/nologin
  33. gdm:x:42:42::/var/lib/gdm:/sbin/nologin
  34. sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
  35. tcpdump:x:72:72::/:/sbin/nologin
  36. oracle:x:500:500::/home/oracle:/bin/bash
  37. mysql:x:27:27:MySQL Server:/var/lib/mysql:/sbin/nologin
  38. oradev:x:513:500::/home/oradev:/bin/bash
  39. vtl:x:514:514::/home/vtl:/bin/bash
  40. orawb:x:512:500::/home/orawb:/bin/bash
  41. radvd:x:75:75:radvd user:/:/sbin/nologin
  42. named:x:25:25:Named:/var/named:/sbin/nologin
  43. webalizer:x:67:67:Webalizer:/var/www/usage:/sbin/nologin
  44. ldap:x:55:55:LDAP User:/var/lib/ldap:/sbin/nologin
  45. vncviewer:x:2022:2022::/home/vncviewer:/bin/bash
  46. sanlock:x:179:179:sanlock:/var/run/sanlock:/sbin/nologin
  47. XXXX:x:179:179:sanlock:/var/run/sanlock:/sbin/nologin
  48. dovecot:x:97:97:Dovecot IMAP server:/usr/libexec/dovecot:/sbin/nologin
  49. dovenull:x:495:487
复制代码

tpasswd.ctl:
  1. LOAD DATA
  2. infile '/home/oracle/passwd.dat'
  3. APPEND
  4. INTO TABLE HR.TPASSWD
  5. FIELDS TERMINATED BY ':' OPTIONALLY ENCLOSED BY '"'
  6. trailing nullcols
  7. (
  8.   I_USERNAME CHAR,
  9.   I_PASSWORD CHAR,
  10.   I_UID INTEGER EXTERNAL,
  11.   I_GID INTEGER EXTERNAL,
  12.   I_COMMENT char,
  13.   I_HOME CHAR,
  14.   I_SHELL CHAR
  15. )
复制代码

tpasswd.sql:
  1. CREATE TABLE tpasswd
  2. (
  3.   "I_USERNAME" VARCHAR2(100),
  4.   "I_PASSWORD" CHAR(1),
  5.   "I_UID" NUMBER,
  6.   "I_GID" NUMBER,
  7.   "I_COMMENT" VARCHAR2(100),
  8.   "I_HOME" VARCHAR2(80),
  9.   "I_SHELL" VARCHAR2(60)
  10. )
  11. ORGANIZATION external
  12. (
  13.   TYPE oracle_loader
  14.   DEFAULT DIRECTORY dir1
  15.   ACCESS PARAMETERS
  16.   (
  17.     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
  18.     BADFILE dir1:'passwd_%a_%p.bad'
  19.     LOGFILE dir1:'tpasswd_%a_%p.log'
  20.     READSIZE 1048576
  21.     FIELDS TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"' LDRTRIM
  22.     MISSING FIELD VALUES ARE NULL
  23.     REJECT ROWS WITH ALL NULL FIELDS
  24.     (
  25.       "I_USERNAME" CHAR(255)
  26.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
  27.       "I_PASSWORD" CHAR(255)
  28.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
  29.       "I_UID" CHAR(255)
  30.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
  31.       "I_GID" CHAR(255)
  32.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
  33.       "I_COMMENT" CHAR(255)
  34.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
  35.       "I_HOME" CHAR(255)
  36.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"',
  37.       "I_SHELL" CHAR(255)
  38.         TERMINATED BY ":" OPTIONALLY ENCLOSED BY '"'
  39.     )
  40.   )
  41.   location
  42.   (
  43.     'passwd.dat'
  44.   )
  45. )REJECT LIMIT UNLIMITED;
复制代码

tdate.dat:
  1. 1,2018-06-03:01:01:01
  2. 2,2018-06-03:02:02:02
复制代码

tdate.ctl:

  1. LOAD DATA
  2. infile '/home/oracle/dir1/tdate.dat'
  3. APPEND
  4. INTO TABLE HR.Tdate
  5. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  6. trailing nullcols
  7. (
  8.   Id  integer external(1),
  9.   mydate date(19)  "YYYY-MM-DD:HH24:MI:SS"
  10. )
复制代码

tdate.sql:

  1. CREATE TABLE hr.tdate
  2. (
  3.   "ID" NUMBER,
  4.   "MYDATE" DATE
  5. )
  6. ORGANIZATION external
  7. (
  8.   TYPE oracle_loader
  9.   DEFAULT DIRECTORY dir1
  10.   ACCESS PARAMETERS
  11.   (
  12.     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
  13.     BADFILE 'tdate_%a_%p.bad'
  14.     LOGFILE 'tdate_%a_%p.log'
  15.     READSIZE 1048576
  16.     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
  17.     MISSING FIELD VALUES ARE NULL
  18.     REJECT ROWS WITH ALL NULL FIELDS
  19.     (
  20.       "ID" CHAR(1)
  21.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  22.       "MYDATE" CHAR(19)
  23.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
  24.         DATE_FORMAT DATE MASK "YYYY-MM-DD:HH24:MI:SS"
  25.     )
  26.   )
  27.   location
  28.   (
  29.     'tdate.dat'
  30.   )
  31. )REJECT LIMIT UNLIMITED
  32. ;
复制代码








LOADER外部表:
  1. select  * from cdb_directories;

  2. create directory dir1 as '/home/oracle/dir1';

  3. create directory dir2 as '/home/oracle/dir2';

  4. grant read,write on directory dir1 to hr;

  5. grant read,write on directory dir2 to hr;

  6. CREATE TABLE hr.extab
  7.                   (employee_id       NUMBER(4),
  8.                    first_name        VARCHAR2(20),
  9.                     last_name         VARCHAR2(25),
  10.                      hire_date         DATE)
  11. ORGANIZATION EXTERNAL
  12.      ( TYPE ORACLE_LOADER DEFAULT DIRECTORY dir1
  13.        ACCESS PARAMETERS
  14.        ( records delimited by newline
  15.          badfile dir1:'extab%a_%p.bad'
  16.          logfile dir1:'extab%a_%p.log'
  17.          fields terminated by ','
  18.          missing field values are null
  19.      ( employee_id  char , first_name  char , last_name  char ,
  20.       hire_date char date_format date mask "dd-mon-yyyy"))
  21.      LOCATION (dir1:'extab1.dat', dir2:'extab2.dat') )
  22.      PARALLEL  4 REJECT LIMIT UNLIMITED;


  23. select  * from hr.extab;

  24. select * from dba_tables t where t.table_name='EXTAB';

  25. select  * from dba_external_tables  t;


复制代码
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 07:53 , Processed in 0.030927 second(s), 27 queries .

快速回复 返回顶部 返回列表