oracle安全参数设置,Oracle安全管理之概要文件(配置文件）

口令配置和资源配置文件一般分开创建

修改配置文件

ALTER PROFILE profile LIMIT

[FAILED_LOGIN_ATTEMPTS max_value]

[PASSWORD_LIFE_TIME max_value]

[ {PASSWORD_REUSE_TIME

|PASSWORD_REUSE_MAX} max_value]

[PASSWORD_LOCK_TIME max_value]

[PASSWORD_GRACE_TIME max_value]

[PASSWORD_VERIFY_FUNCTION

{function|NULL|DEFAULT} ]

若要将口令参数设置为不超过一天：

1小时：PASSWORD_LOCK_TIME = 1/24

10分钟：PASSWORD_LOCK_TIME = 10/1400

5分钟：PASSWORD_LOCK_TIME = 5/1440

删除配置文件

DROP PROFILE profile [CASCADE]

其中：

profile：是要删除的配置文件的名称

CASCADE：指定此选项可以删除当前分配给用户的配置文件。

查询配置文件

dba_users 查询用户所使用的配置文件

dba_profiles 查询所有的参数文件的基本信息

user_password_limits 查询用户设置的口令文件的每条参数的信息

user_resource_limits 查询设置的资源限制参数

实例

1.创建一个res_profile配置文件

SQL> create profileres_profile limitsessions_per_user4 connect_time60 idle_time20private_sga100K cpu_per_call100;

2.创建一个pwd_profile的配置文件

SQL> create profile pwd_profile limit failed_login_attempts 4 password_lock_time 10;

Profile created.

3.将概要文件分配给用户

SQL> create user user5 identified by user5 profile res_profile;

User created.

查询

SQL> select profile from dba_users where username='USER5';

PROFILE

------------------------------

RES_PROFILE

修改用户的概要文件

SQL> alter user user5 profile pwd_profile;

User altered.

SQL> select profile from dba_users where username='USER5';

PROFILE

------------------------------

PWD_PROFILE

4.修改概要文件，使用alter profile ...limit，执行该语句的用户必须拥有alter profile权限

SQL> alter profile pwd_profile limit password_life_time 10;

Profile altered.

5.查询概要文件pwd_profile内容

SQL> select * from dba_profiles where profile='PWD_PROFILE';

PROFILE RESOURCE_NAME RESOURCE LIMIT

------------------------------ -------------------------------- -------- ----------------------------------------

PWD_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT

PWD_PROFILE SESSIONS_PER_USER KERNEL DEFAULT

PWD_PROFILE CPU_PER_SESSION KERNEL DEFAULT

PWD_PROFILE CPU_PER_CALL KERNEL DEFAULT

PWD_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT

PWD_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT

PWD_PROFILE IDLE_TIME KERNEL DEFAULT

PWD_PROFILE CONNECT_TIME KERNEL DEFAULT

PWD_PROFILE PRIVATE_SGA KERNEL DEFAULT

PWD_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 4

PWD_PROFILE PASSWORD_LIFE_TIME PASSWORD 10

PWD_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT

PWD_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT

PWD_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT

PWD_PROFILE PASSWORD_LOCK_TIME PASSWORD 10

PWD_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT

16 rows selected.

6.删除概要文件

SQL> drop profile pwd_profile;drop profile pwd_profile

*

ERROR at line 1:

ORA-02382: profile PWD_PROFILE has users assigned, cannot drop without CASCADE

SQL> drop profile pwd_profilecascade;

Profile dropped.

说明：如果删除的概要文件已经指定给用户，则必须在drop profile 中加入cascade

8.查询

查询口令文件为用户设置的策略信息

SQL> select * from user_password_limits;

RESOURCE_NAME LIMIT

-------------------------------- ----------------------------------------

FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

PASSWORD_VERIFY_FUNCTION NULL

PASSWORD_LOCK_TIME UNLIMITED

PASSWORD_GRACE_TIME UNLIMITED

查询资源配置文件为用户设置的策略信息

SQL> select * from user_resource_limits;

RESOURCE_NAME LIMIT

-------------------------------- ----------------------------------------

COMPOSITE_LIMIT UNLIMITED

SESSIONS_PER_USER UNLIMITED

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