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

查看用户当前所赋予的权限：

SQL> select * from dba_sys_privs where grantee ='PROFILE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
PROFILE                        INSERT ANY TABLE                         NO
PROFILE                        UNLIMITED TABLESPACE                     NO
PROFILE                        DROP ANY SEQUENCE                        NO
PROFILE                        UPDATE ANY TABLE                         NO
PROFILE                        DROP ANY TABLE                           NO
PROFILE                        ALTER ANY SEQUENCE                       NO
PROFILE                        SELECT ANY TABLE                         NO
PROFILE                        DROP TABLESPACE                          NO
PROFILE                        CREATE TABLESPACE                        NO
PROFILE                        GRANT ANY PRIVILEGE                      NO
PROFILE                        SELECT ANY SEQUENCE                      NO

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
PROFILE                        CREATE ANY INDEX                         NO
PROFILE                        DELETE ANY TABLE                         NO
PROFILE                        CREATE ANY TABLE                         NO
PROFILE                        CREATE TABLE                             NO
PROFILE                        SELECT ANY DICTIONARY                    NO
PROFILE                        CREATE ANY SEQUENCE                      NO
PROFILE                        CREATE SEQUENCE                          NO
PROFILE                        ALTER ANY TABLE                          NO
PROFILE                        DROP USER                                NO
PROFILE                        CREATE USER                              NO

查看用户当前所赋予的角色：

conn profile/password;
SQL> select * from USER_ROLE_PRIVS;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
PROFILE                        CONNECT                        NO  YES NO

conn / as sysdba
SQL> select * from dba_role_privs where grantee ='PROFILE';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
PROFILE                        RESOURCE                       NO  YES
PROFILE                        CONNECT                        NO  YES