概要文件
SQL>desc dba_profiles
SQL>select distinct profile from dba_profiles;
SQL>select count(*) from dba_profiles;
SQL>select resource_name,limit from dba_profiles where profile='DEFAULT'--查看概要文件的详细信息
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER 3
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
SQL>create profile p1 limit ---命令行创建概要文件时,我们用复制的方法
2 COMPOSITE_LIMIT UNLIMITED
3 SESSIONS_PER_USER 3 ---用户会话数
4 CPU_PER_SESSION UNLIMITED
5 CPU_PER_CALL UNLIMITED
6 LOGICAL_READS_PER_SESSION UNLIMITED
7 LOGICAL_READS_PER_CALL UNLIMITED
8 IDLE_TIME UNLIMITED ---空闲时间,自动清理连接
9 CONNECT_TIME UNLIMITED
10 PRIVATE_SGA UNLIMITED
11 FAILED_LOGIN_ATTEMPTS 10 ---连接失败超过限制,用户被锁定
12 PASSWORD_LIFE_TIME 180 ---密码有效的时间(查过时间提示你修改密码)
13 PASSWORD_REUSE_TIME UNLIMITED ---同样的密码是否可以再用
14 PASSWORD_REUSE_MAX UNLIMITED ---
15 PASSWORD_VERIFY_FUNCTION NULL ---复杂性设定
16 PASSWORD_LOCK_TIME 1 ---锁定时间
17 PASSWORD_GRACE_TIME 7
SQL>select username,profile from dba_users where username ='SCOTT';---查看用户对应的概要文件
SQL>alter user scott profile p1; ----将用户的概要文件修改为
三个用户(sessions_per_ue设置为3,)连接地4个用户就会报错
SQL>show parameter resource_limit
SQL>alter system set resource_limit=ture;-----使概要文件生效
SQL>alter profile p1 limit sessions_per_user 3; ------------ 修改概要文件
SQL>alter profile p1 limit password_lock_time unlimited;
SQL>alter profile p1 limit failed_login_attempt 5;
密码输入超过5次就会没锁定
SQL>alter user scott account unlock;
密码的复杂性验证
[oracle@oel ~]$ cd $ORACLE_HOME/rdbms/admin ----下面有很多脚本
SQL>@?/rdbms/admin/utlpwdmg ---执行了utlpwdmg这个脚本
Function created.
Profile altered.
Function created.
SQL> select RESOURCE_NAME,LIMIT from dba_profiles where profile='P1';
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER 3
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
SQL> alter profile p1 limit password_verify_function verify_function_11g;----启用复杂性密码
Profile altered.
SQL> alter user scott identified by aaaa1234;
SQL> alter profile p1 limit password_verify_function null;关闭复杂性密码
SQL> alter user scott identified by tiger;
SQL> drop function verify_function_11g;----删除
SQL> drop function verify_function;
概要设置限制cpu,会话使用的块等时要慎重,不然会影响数据库的性能
高与平均值限制你,低于平均值不限制你,白天限制你,晚上不限制你等,,
概要文件中time都是天
概要文件dba_profiles
最新推荐文章于 2025-05-21 09:23:28 发布