•        口令安全和资源的管理

  • 查看当前profile文件信息:
    select * from dba_profiles order by 1;

    口令管理风格:
    FAILED_LOGIN_ATTEMPTS PASSWORD 10 --指定在锁定帐户之前试图登录用户帐户的失败次数
    PASSWORD_LIFE_TIME PASSWORD UNLIMITED --生存期,过期失效 2/1440
    PASSWORD_REUSE_TIME PASSWORD UNLIMITED --许多系统不许用户重新启用过去用过的口令。该资源项设定了一个失效口令要经过多少天,用户才可以重新使用该口令!
    PASSWORD_REUSE_MAX PASSWORD UNLIMITED --指定需要更改口令的次数想设置此参数需将PASSWORD_REUSE_TIME置为UNLIMITED
    PASSWORD_VERIFY_FUNCTION PASSWORD NULL
    PASSWORD_LOCK_TIME PASSWORD UNLIMITED --登录失败后被锁定的天数
    PASSWORD_GRACE_TIME PASSWORD UNLIMITED --宽限期

    grant connect,resource to u1 identified by u1;
    select username,account_status from dba_users where username='U1';
    select profile from dba_users where username='U1';
    create profile p1 limit FAILED_LOGIN_ATTEMPTS 2;
    select * from dba_profiles where profile='P1';
    修改用户使用指定的配置文件:
    alter user u1 profile p1;
    alter profile p1 limit PASSWORD_LOCK_TIME 1/1440;

    alter profile p1 limit
    PASSWORD_LIFE_TIME 1/1440
    PASSWORD_GRACE_TIME 1/1440;

    CREATE OR REPLACE FUNCTION vf
    (username varchar2,
    password varchar2,
    old_password varchar2)
    RETURN boolean IS
    BEGIN

    IF NLS_LOWER(password) = NLS_LOWER(username) THEN
    dbms_output.put_line('ok1');
    raise_application_error(-20001, 'Password same as or similar to username');
    END IF;

    IF length(password) < 4 THEN
    raise_application_error(-20002, 'Password length less than 4');
    END IF;

    IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
    raise_application_error(-20003, 'Password too simple');
    END IF;
    RETURN(TRUE);
    END;
    /

    alter profile p1 limit PASSWORD_VERIFY_FUNCTION vf;

    资源管理:
    COMPOSITE_LIMIT KERNEL UNLIMITED --混合资源累计值(CPU_PER_SESSION & LOGICAL_READS_PER_SESSION & CONNECT_TIME & PRIVATE_SGA)达到该参数值时会话即被终止
    SESSIONS_PER_USER KERNEL UNLIMITED --每个用户名所允许的并发会话数
    CPU_PER_SESSION KERNEL UNLIMITED --会话持续期间可以占用的CPU时间总量(厘秒)
    CPU_PER_CALL KERNEL UNLIMITED --每次SQL调用可以使用的CPU时间(厘秒)
    LOGICAL_READS_PER_SESSION KERNEL UNLIMITED --会话期间能够进行的逻辑读的oracle块的数量
    LOGICAL_READS_PER_CALL KERNEL UNLIMITED --每条SQL可以读取的oracle块的数量
    IDLE_TIME KERNEL UNLIMITED --非活动时间长度 (以分钟为单位)
    CONNECT_TIME KERNEL UNLIMITED --用户可以和数据库连接的时间(分钟)
    PRIVATE_SGA KERNEL UNLIMITED --表示用户私有的SQL区的大小(以块为单位)

    想使资源配置生效要修改初始化参数: resource_limit=true
    alter system set resource_limit=true;
    alter profile p1 limit SESSIONS_PER_USER 2;
    alter profile p1 limit IDLE_TIME 1;

    select 'kill -9 '||spid
    from v$process p,v$session s
    where p.addr=s.paddr
    and s.status='SNIPED';

    删除配置文件:
    drop profile p1 cascade;
    alter user u1 profile default;

    SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。
  • 超级用户的审计:
    audit_file_dest='/u1/oracle/admin/updb/adump' --审计线索保存的位置
    audit_sys_operations=true --起用对SYSDBA和SYSOPER的附加审计信息

    普通用户的审计:
    审计信息的保存位置:
    audit_trail={ none | os | db | db,extended | xml | xml,extended }
    None :是默认值,不做审计;
    os : 将审计信息记录于操作系统!windows记录于事件查看器,unxi记录于audit_file_dest所指定路径
    DB :将审计线索记录在数据库中,aud$
    db,extended :extended选项会在审计中增加SQLBIND 和 SQLTEXT的信息
    xml :将审计线索记录在xml文件中audit_file_dest参数所指定的目录

    语句审计:跟踪会话的连接和注销
    audit session by scott; -- 审计指定用户的连接或断开连接操作
    noaudit session by scott;
    audit session; --审计用户连接或断开连接操作
    noaudit session;

    查看审计ACTION对应的编码:
    select name from AUDIT_ACTIONS where ACTION=100;
    select name from AUDIT_ACTIONS where ACTION=101;

    语句审计(审计create table,drop table,truncate table):
    audit table by scott; --成功与否都生成审计线索
    audit table by scott whenever successful; --只有成功生成审计线索
    audit table by scott whenever not successful; --只有失败生成审计线索
    noaudit table by scott;

    audit table;
    audit table whenever successful;
    audit table whenever not successful;
    noaudit table;

    语句审计(审计DML语句):
    audit insert table by scott;
    audit insert table by scott whenever successful;
    audit insert table by scott whenever not successful;
    audit insert table by scott by access;
    audit insert table by scott by access whenever successful;
    audit insert table by scott by access whenever not successful;
    noaudit insert table by scott;
    (BY ACCESS每次都生成一条审计记录,
    BY SESSION相同操作只生成一条审计记录)

    查看用户被启用的语句审计的选项:
    col AUDIT_OPTION for a20
    select audit_option,failure,success,user_name from dba_stmt_audit_opts;

    特权审计(使用了特定的系统权限才会触发审计线索):
    audit create any table;
    audit create any table by scott;
    audit drop any table by scott;
    audit select any table by scott by session;

    查看用户被启用的特权审计的选项:
    select privilege,user_name from dba_priv_audit_opts order by user_name;

    对象审计(跟踪指定对象权限的操作):
    audit delete on scott.e;
    audit delete on scott.e by session;
    audit delete on scott.e by session whenever successful;
    audit delete on scott.e by session whenever not successful;

    audit select on scott.emp by access;
    audit delete on scott.e by access whenever successful;
    audit delete on scott.e by access whenever not successful;

    noaudit delete on scott.e;

    查看用户被启用的对象审计的选项:
    col OWNER for a10
    col OBJECT_NAME for a15
    col OBJECT_TYPE for a15
    select * from dba_obj_audit_opts where owner='SCOTT';

    successful /not successful
    S --> session
    A --> access

    -- 取消所有statement审计
    noaudit all;
    -- 取消所有权限审计
    noaudit all privileges;
    -- 取消所有对象审计
    noaudit all on default;

    将审计线索保存到数据库:
    alter system set audit_trail=db scope=spfile;
    aud$

    col OBJ$CREATOR for a10
    col USERID for a10
    col USERHOST for a22
    col OBJ$NAME for a10
    col NTIMESTAMP# for a28
    col COMMENT$TEXT for a30

    --audit session by scott;
    select USERID,USERHOST,ACTION#,RETURNCODE,COMMENT$TEXT,NTIMESTAMP#,PROCESS# from aud$ where userid='SCOTT';

    ps -ef | grep PROCESS#

    --audit table by scott;
    select USERID,USERHOST,ACTION#,OBJ$CREATOR,OBJ$NAME,NTIMESTAMP#,PROCESS#,XID from aud$ where userid='SCOTT';

    --audit insert table by scott;
    select USERID,USERHOST,ACTION#,OBJ$CREATOR,OBJ$NAME,SES$ACTIONS,NTIMESTAMP#,PROCESS#,XID from aud$ where userid='SCOTT';

    select UNDO_SQL from flashback_transaction_query where xid='03000B0048010000';

    --audit create any table by scott;
    select USERID,USERHOST,ACTION#,OBJ$CREATOR,OBJ$NAME,NTIMESTAMP#,PROCESS# from aud$ where userid='SCOTT';

    --audit delete on scott.e;
    select USERID,USERHOST,ACTION#,OBJ$CREATOR,OBJ$NAME,SES$ACTIONS,NTIMESTAMP#,PROCESS#,XID from aud$ where userid='SCOTT';

    --audit select on scott.emp by access;
    select USERID,USERHOST,ACTION#,OBJ$CREATOR,OBJ$NAME,NTIMESTAMP#,PROCESS# from aud$ where userid='SCOTT';

    如果审计信息记录在数据库中怎样清除审计信息
    delete from sys.aud$;
    delete from sys.aud$ where obj$name='EMP';
    commit;

    细颗粒审计:
    alter syste set audit_trail=os scope=spfile;

    begin
    DBMS_FGA.ADD_POLICY (
    object_schema => 'scott',
    object_name => 'emp',
    policy_name => 'mypolicy1',
    audit_condition => 'sal < 100',
    audit_column => 'comm,sal',
    handler_schema => NULL,
    handler_module => NULL,
    enable => TRUE,
    statement_types => 'INSERT, UPDATE',
    audit_trail => DBMS_FGA.XML + DBMS_FGA.EXTENDED,
    audit_column_opts => DBMS_FGA.ANY_COLUMNS);
    end;
    /

    begin
    DBMS_FGA.DISABLE_POLICY (
    object_schema => 'scott',
    object_name => 'emp',
    policy_name => 'mypolicy1');
    end;
    /

    begin
    DBMS_FGA.ENABLE_POLICY (
    object_schema => 'scott',
    object_name => 'emp',
    policy_name => 'mypolicy1',
    enable => TRUE);
    end;
    /

    begin
    DBMS_FGA.DROP_POLICY (
    object_schema => 'scott',
    object_name => 'emp',
    policy_name => 'mypolicy1');
    end;
    /


扫码关注我们
微信号:SRE实战
拒绝背锅 运筹帷幄