SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jan 28 00:06:49 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba;
Connected.
SQL>
SQL>
SQL> @ 'E:\oracle\ora92\rdbms\admin\utlpwdmg.sql';
Function created.
Profile altered.
SQL> desc dba_profiles;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(40)
SQL> column profile format a8;
SQL> column resource_name format a15;
SQL> column resource format a15;
SQL> column limit format a15;
SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------- --------------- -------- ---------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_AT PASSWORD 3
TEMPTS
DEFAULT SESSIONS_PER_US KERNEL UNLIMITED
ER
DEFAULT PASSWORD_LIFE_T PASSWORD 60
IME
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------- --------------- -------- ---------------
DEFAULT PASSWORD_REUSE_ PASSWORD 1800
TIME
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT PASSWORD_REUSE_ PASSWORD UNLIMITED
MAX
DEFAULT LOGICAL_READS_P KERNEL UNLIMITED
ER_SESSION
DEFAULT PASSWORD_VERIFY PASSWORD VERIFY_FUNCTION
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------- --------------- -------- ---------------
_FUNCTION
DEFAULT LOGICAL_READS_P KERNEL UNLIMITED
ER_CALL
DEFAULT PASSWORD_LOCK_T PASSWORD .0006
IME
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT PASSWORD_GRACE_ PASSWORD 10
TIME
PROFILE RESOURCE_NAME RESOURCE LIMIT
-------- --------------- -------- ---------------
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
16 rows selected.
SQL>
============================================================================================
CREATING A PROFILE:PASSWORD SETTING
=============================================================================================
SQL>
SQL> create profile tinku_profile limit
failed_login_attempts 3
password_lock_time unlimited
password_life_time 20
password_reuse_time 10
password_verify_function verify_function
password_grace_time 3;
Profile created.
SQL>
SQL> desc dba_profiles;
Name Null? Type
----------------------------------------- -------- --------------------------
PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(40)
SQL> column profile format a15;
SQL> select * from dba_profiles where profile='TINKU_PROFILE';
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- --------------- -------- ---------------
TINKU_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
TINKU_PROFILE SESSIONS_PER_US KERNEL DEFAULT
ER
TINKU_PROFILE CPU_PER_SESSION KERNEL DEFAULT
TINKU_PROFILE CPU_PER_CALL KERNEL DEFAULT
TINKU_PROFILE LOGICAL_READS_P KERNEL DEFAULT
ER_SESSION
TINKU_PROFILE LOGICAL_READS_P KERNEL DEFAULT
ER_CALL
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- --------------- -------- ---------------
TINKU_PROFILE IDLE_TIME KERNEL DEFAULT
TINKU_PROFILE CONNECT_TIME KERNEL DEFAULT
TINKU_PROFILE PRIVATE_SGA KERNEL DEFAULT
TINKU_PROFILE FAILED_LOGIN_AT PASSWORD 3
TEMPTS
TINKU_PROFILE PASSWORD_LIFE_T PASSWORD 20
IME
TINKU_PROFILE PASSWORD_REUSE_ PASSWORD 10
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- --------------- -------- ---------------
TIME
TINKU_PROFILE PASSWORD_REUSE_ PASSWORD DEFAULT
MAX
TINKU_PROFILE PASSWORD_VERIFY PASSWORD VERIFY_FUNCTION
_FUNCTION
TINKU_PROFILE PASSWORD_LOCK_T PASSWORD UNLIMITED
IME
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- --------------- -------- ---------------
TINKU_PROFILE PASSWORD_GRACE_ PASSWORD 3
TIME
16 rows selected.
============================================================================================
ALTERING A PROFILE:PASSWORD SETTING
============================================================================================
SQL>
SQL> alter profile tinku_profile limit
2 failed_login_attempts 10
3 password_life_time 60
4 password_reuse_time 40
5 password_grace_time 10;
Profile altered.
SQL> select * from dba_profiles where profile='TINKU_PROFILE';
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- --------------- -------- ---------------
TINKU_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
TINKU_PROFILE SESSIONS_PER_US KERNEL DEFAULT
ER
TINKU_PROFILE CPU_PER_SESSION KERNEL DEFAULT
TINKU_PROFILE CPU_PER_CALL KERNEL DEFAULT
TINKU_PROFILE LOGICAL_READS_P KERNEL DEFAULT
ER_SESSION
TINKU_PROFILE LOGICAL_READS_P KERNEL DEFAULT
ER_CALL
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- --------------- -------- ---------------
TINKU_PROFILE IDLE_TIME KERNEL DEFAULT
TINKU_PROFILE CONNECT_TIME KERNEL DEFAULT
TINKU_PROFILE PRIVATE_SGA KERNEL DEFAULT
TINKU_PROFILE FAILED_LOGIN_AT PASSWORD 10
TEMPTS
TINKU_PROFILE PASSWORD_LIFE_T PASSWORD 60
IME
TINKU_PROFILE PASSWORD_REUSE_ PASSWORD 40
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- --------------- -------- ---------------
TIME
TINKU_PROFILE PASSWORD_REUSE_ PASSWORD DEFAULT
MAX
TINKU_PROFILE PASSWORD_VERIFY PASSWORD VERIFY_FUNCTION
_FUNCTION
TINKU_PROFILE PASSWORD_LOCK_T PASSWORD UNLIMITED
IME
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------- --------------- -------- ---------------
TINKU_PROFILE PASSWORD_GRACE_ PASSWORD 10
TIME
16 rows selected.
SQL> alter profile default limit
2 password_life_time 60
3 password_grace_time 10
4 password_reuse_time 1800
5 password_reuse_max unlimited
6 failed_login_attempts 3
7 password_lock_time 1/1440
8 password_verify_function verify_function;
Profile altered.
SQL> select * from dba_profileS where profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL
UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL
UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL
UNLIMITED
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT CPU_PER_CALL KERNEL
UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL
UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL
UNLIMITED
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT IDLE_TIME KERNEL
UNLIMITED
DEFAULT CONNECT_TIME KERNEL
UNLIMITED
DEFAULT PRIVATE_SGA KERNEL
UNLIMITED
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD
3
DEFAULT PASSWORD_LIFE_TIME PASSWORD
60
DEFAULT PASSWORD_REUSE_TIME PASSWORD
1800
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_REUSE_MAX PASSWORD
UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD
VERIFY_FUNCTION
DEFAULT PASSWORD_LOCK_TIME PASSWORD
.0006
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_GRACE_TIME PASSWORD
10
16 rows selected.
============================================================================================
CRAETING A PROFILE:RESOURCE LIMIT
============================================================================================
SQL> create profile deve_profile limit
2 sessions_per_user 2
3 cpu_per_session 10000
4 idle_time 60
5 connect_time 480;
Profile created.
SQL> create user raju identified by ku23$s
2 default tablespace system
3 temporary tablespace temp
4 quota 15m on system
5 quota 10m on temp
6 profile deve_profile;
User created.
SQL> grant connect,resource to raju;
Grant succeeded.
SQL> conn raju/ku23$s
Connected.
SQL>
==========================================================================================
give the profile to user
============================================================================================
SQL> create user tinku identified by ku23$s
2 default tablespace system
3 temporary tablespace temp
4 quota 15m on system
5 quota 10m on temp
6 profile tinku_profile;
User created.
SQL> grant connect,resource to tinku;
Grant succeeded.
SQL> conn tinku/ku23$s
Connected.
SQL>
==========================================================================================
DROPPING A PROFILE:PASSWORD SETTING
===========================================================================================
SQL> desc dba_users;
Name Null? Type
----------------------------------------- -------- --------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
SQL> select username,profile from dba_users;
USERNAME PROFILE
------------------------------ ------------------------------
SYS DEFAULT
SYSTEM DEFAULT
DBSNMP DEFAULT
MO DEFAULT
SCOTT DEFAULT
MOHAN DEFAULT
OUTLN DEFAULT
ORDSYS DEFAULT
MDSYS DEFAULT
OE DEFAULT
PM DEFAULT
USERNAME PROFILE
------------------------------ ------------------------------
SH DEFAULT
QS_ADM DEFAULT
QS DEFAULT
QS_WS DEFAULT
QS_ES DEFAULT
QS_OS DEFAULT
CTXSYS DEFAULT
OLAPSYS DEFAULT
ODM_MTR DEFAULT
ODM DEFAULT
WKPROXY DEFAULT
USERNAME PROFILE
------------------------------ ------------------------------
WKSYS DEFAULT
ANONYMOUS DEFAULT
XDB DEFAULT
QS_CBADM DEFAULT
QS_CB DEFAULT
QS_CS DEFAULT
RMAN DEFAULT
ORDPLUGINS DEFAULT
WMSYS DEFAULT
HR DEFAULT
RAJ DEFAULT
USERNAME PROFILE
------------------------------ ------------------------------
TINKU TINKU_PROFILE
MAA TINKU_PROFILE
RAJU DEVE_PROFILE
36 rows selected.
SQL> drop profile deve_profile cascade;
Profile dropped.
SQL> select username,profile from dba_users;
USERNAME PROFILE
------------------------------ ------------------------------
SYS DEFAULT
SYSTEM DEFAULT
DBSNMP DEFAULT
MO DEFAULT
SCOTT DEFAULT
MOHAN DEFAULT
OUTLN DEFAULT
ORDSYS DEFAULT
MDSYS DEFAULT
OE DEFAULT
PM DEFAULT
USERNAME PROFILE
------------------------------ ------------------------------
SH DEFAULT
QS_ADM DEFAULT
QS DEFAULT
QS_WS DEFAULT
QS_ES DEFAULT
QS_OS DEFAULT
CTXSYS DEFAULT
OLAPSYS DEFAULT
ODM_MTR DEFAULT
ODM DEFAULT
WKPROXY DEFAULT
USERNAME PROFILE
------------------------------ ------------------------------
WKSYS DEFAULT
ANONYMOUS DEFAULT
XDB DEFAULT
QS_CBADM DEFAULT
QS_CB DEFAULT
QS_CS DEFAULT
RMAN DEFAULT
ORDPLUGINS DEFAULT
WMSYS DEFAULT
HR DEFAULT
RAJ DEFAULT
USERNAME PROFILE
------------------------------ ------------------------------
RAJU DEFAULT
TINKU TINKU_PROFILE
MAA TINKU_PROFILE
36 rows selected.
Very useful info. Hope to see more posts soon!.
ReplyDeletejiofi local html login