How to manage Oracle Database User profile
Oracle database
数据库user profile配置文件用户资源限制
SQL> set linesize 200 SQL> col TEMPORARY_TABLESPACE for a25 SQL> col DEFAULT_TABLESPACE for a25 SQL> col USERNAME for a25 SQL> col ACCOUNT_STATUS for a25 SQL> select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE,AUTHENTICATION_TYPE from dba_users where username ='PROFILE'; USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE AUTHENTI ------------------------- ------------------------- ------------------------- ------------------------- -------------------- -------- PROFILE OPEN USERS TEMP UNLIMITED_PASSWORD PASSWORD
创建 profile
示例1：
create profile UNLIMITED_PASSWORD limit PASSWORD_LIFE_TIME UNLIMITED PASSWORD_GRACE_TIME DEFAULT PASSWORD_LOCK_TIME DEFAULT PASSWORD_VERIFY_FUNCTION DEFAULT PASSWORD_REUSE_MAX DEFAULT PASSWORD_REUSE_TIME DEFAULT FAILED_LOGIN_ATTEMPTS DEFAULT PRIVATE_SGA DEFAULT CONNECT_TIME DEFAULT IDLE_TIME DEFAULT LOGICAL_READS_PER_CALL DEFAULT LOGICAL_READS_PER_SESSION DEFAULT CPU_PER_CALL DEFAULT CPU_PER_SESSION DEFAULT SESSIONS_PER_USER DEFAULT COMPOSITE_LIMIT DEFAULT;
示例2：
create profile app_user limit sessions_per_user unlimited cpu_per_session unlimited cpu_per_call 3000 connect_time 45 logical_reads_per_session default logical_reads_per_call 1000 private_sga 15k composite_limit 5000000;
创建用户的时候指定 profile ，未指定则使用默认的 default profile
create user profile profile UNLIMITED_PASSWORD identified by orcl1234;
修改用户的 profile 设置
alter user profile new_profile_name;
查看用户当前的 profile 详细设置
select username, b.* from dba_users a, dba_profiles b where a.profile = b.profile and a.username='PROFILE'; USERNAME PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ ------------------------------ -------------------------------- -------- ---------------------------------------- PROFILE UNLIMITED_PASSWORD PASSWORD_LIFE_TIME PASSWORD UNLIMITED PROFILE UNLIMITED_PASSWORD PASSWORD_GRACE_TIME PASSWORD DEFAULT PROFILE UNLIMITED_PASSWORD PASSWORD_LOCK_TIME PASSWORD DEFAULT PROFILE UNLIMITED_PASSWORD PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT PROFILE UNLIMITED_PASSWORD PASSWORD_REUSE_MAX PASSWORD DEFAULT PROFILE UNLIMITED_PASSWORD PASSWORD_REUSE_TIME PASSWORD DEFAULT PROFILE UNLIMITED_PASSWORD FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT PROFILE UNLIMITED_PASSWORD PRIVATE_SGA KERNEL DEFAULT PROFILE UNLIMITED_PASSWORD CONNECT_TIME KERNEL DEFAULT PROFILE UNLIMITED_PASSWORD IDLE_TIME KERNEL DEFAULT PROFILE UNLIMITED_PASSWORD LOGICAL_READS_PER_CALL KERNEL DEFAULT USERNAME PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ ------------------------------ -------------------------------- -------- ---------------------------------------- PROFILE UNLIMITED_PASSWORD LOGICAL_READS_PER_SESSION KERNEL DEFAULT PROFILE UNLIMITED_PASSWORD CPU_PER_CALL KERNEL DEFAULT PROFILE UNLIMITED_PASSWORD CPU_PER_SESSION KERNEL DEFAULT PROFILE UNLIMITED_PASSWORD SESSIONS_PER_USER KERNEL DEFAULT PROFILE UNLIMITED_PASSWORD COMPOSITE_LIMIT KERNEL DEFAULT