管理安全和资源（一）--策略文件

 

Profile 是口令限制、资源限制的命名集合。建立Oracle数据库时，Oracle会自动建立Default Profile，然后分配各用户，如果没有指定，用户会启用default profile。

1、查看口令文件

09:07:15 SQL> select username ,profile from dba_users;

USERNAME                       PROFILE

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

MGMT_VIEW                      DEFAULT

SYS                            DEFAULT

SYSTEM                         DEFAULT

DBSNMP                         MONITORING_PROFILE

SYSMAN                         DEFAULT

SCOTT                          DEFAULT

OUTLN                          DEFAULT

MDSYS                          DEFAULT

ORDSYS                         DEFAULT

EXFSYS                         DEFAULT

DMSYS                          DEFAULT

WMSYS                          DEFAULT

CTXSYS                         DEFAULT

ANONYMOUS                      DEFAULT

XDB                            DEFAULT

ORDPLUGINS                     DEFAULT

SI_INFORMTN_SCHEMA             DEFAULT

USERNAME                       PROFILE

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

OLAPSYS                        DEFAULT

TSMSYS                         DEFAULT

BI                             DEFAULT

PM                             DEFAULT

MDDATA                         DEFAULT

IX                             DEFAULT

SH                             DEFAULT

DIP                            DEFAULT

2、profile 管理口令的安全

09:10:28 SQL> select * from dba_profiles;

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

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

DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10

DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD UNLIMITED

DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

3、建立新的profile

（1）创建profile文件

09:19:59 SQL> create profile pass_profile limit

09:24:35   2    FAILED_LOGIN_ATTEMPTS 3

09:24:42   3    PASSWORD_LIFE_TIME  10

09:24:48   4    PASSWORD_REUSE_TIME 2

09:24:52   5    PASSWORD_LOCK_TIME 1/1440

09:25:27   6    PASSWORD_GRACE_TIME 2;

Profile created.

（2）将profile应用到用户

09:26:18 SQL> create user tom identified by tom profile pass_profile default tablespace users;

User created.

09:26:46 SQL> select username,profile from dba_users

09:26:56   2   where username='TOM';

USERNAME                       PROFILE

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

TOM                            PASS_PROFILE

09:27:44 SQL> select * from dba_profiles where profile='PASS_PROFILE';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

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

PASS_PROFILE                   COMPOSITE_LIMIT                  KERNEL   DEFAULT

PASS_PROFILE                   SESSIONS_PER_USER                KERNEL   DEFAULT

PASS_PROFILE                   CPU_PER_SESSION                  KERNEL   DEFAULT

PASS_PROFILE                   CPU_PER_CALL                     KERNEL   DEFAULT

PASS_PROFILE                   LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT

PASS_PROFILE                   LOGICAL_READS_PER_CALL           KERNEL   DEFAULT

PASS_PROFILE                   IDLE_TIME                        KERNEL   DEFAULT

PASS_PROFILE                   CONNECT_TIME                     KERNEL   DEFAULT

PASS_PROFILE                   PRIVATE_SGA                      KERNEL   DEFAULT

PASS_PROFILE                   FAILED_LOGIN_ATTEMPTS            PASSWORD 3

PASS_PROFILE                   PASSWORD_LIFE_TIME               PASSWORD 10

PASS_PROFILE                   PASSWORD_REUSE_TIME              PASSWORD 2

PASS_PROFILE                   PASSWORD_REUSE_MAX               PASSWORD DEFAULT

PASS_PROFILE                   PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT

PASS_PROFILE                   PASSWORD_LOCK_TIME               PASSWORD .0006

PASS_PROFILE                   PASSWORD_GRACE_TIME              PASSWORD 2

验证：

09:28:26 SQL> grant create session to tom;

Grant succeeded.

09:28:32 SQL> conn tom/t123

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

09:28:37 SQL> conn tom/t1234

ERROR:

ORA-01017: invalid username/password; logon denied

09:28:40 SQL> conn tom/t1235

ERROR:

ORA-01017: invalid username/password; logon denied

09:28:44 SQL> conn tom/t1236

ERROR:

ORA-28000: the account is locked

09:28:46 SQL> conn /as sysdba

Connected.

09:29:07 SQL> select username ,account_status from dba_users

09:29:14   2   where username='TOM';

USERNAME                       ACCOUNT_STATUS

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

TOM                            LOCKED(TIMED)