Oracle Database Profile Management

All About Oracle Database Profile

What is a Profile in Oracle Database

The purpose of a profile in oracle database is basically to limit the use of resouce for a particular user.

When you create a profile you define a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits.

How to create Profile in Oracle

SQL> CREATE PROFILE MyFirstProfile LIMIT PASSWORD_REUSE_MAX 10 PASSWORD_REUSE_TIME 30;

How to assign Profile to a user in Oracle

You can assign a profile to a user at the time of creating the user and you can alter user to assign the profile for an existing user

SQL> create user andy identified by password profile MyFirstProfile;

or

SQL> ALTER USER andy PROFILE MyFirstProfile;

How to find Profiles assiged to an user in Oracle

SQL> select username,profile from dba_users

UserName                Profile

ORACLE_OCM      DEFAULT
OJVMSYS               DEFAULT
SYSKM                   DEFAULT
XS$NULL               DEFAULT
GSMCATUSER      DEFAULT

How to find the list of Profiles created in the Database

SQL> select profile , common from dba_profiles

PROFILE                       COMMON

C##WDUSERS               YES
C##SYSPROFILE           YES
C##OEM_PROFILE        YES
C##APPUSER                  YES
ORA_STIG_PROFILE      NO

Note:- If you are running oracle database Version 12c, it got a feature of multitenancy where in you have multiple pluggable databases running in container. COMMON=Yes means the profile is common for all availiable databases while COMMON=NO means the profile is specific to only the Pluggable database for which it has been created and can not be used beyond this scope.

How to find the contents of a Profile

SQL> select * from dba_profiles where profile='DEFAULT'

PROFILE   RESOURCE_NAME                            RESOURCE_TYPE   LIMIT         OMMON
DEFAULT   COMPOSITE_LIMIT                            KERNEL                      UNLIMITED NO
DEFAULT   SESSIONS_PER_USER                        KERNEL                      UNLIMITED NO
DEFAULT   CPU_PER_SESSION                            KERNEL                        UNLIMITED NO
DEFAULT   CPU_PER_CALL                                 KERNEL                        UNLIMITED NO
DEFAULT   LOGICAL_READS_PER_SESSION   KERNEL                        UNLIMITED NO
DEFAULT   LOGICAL_READS_PER_CALL         KERNEL                        UNLIMITED NO
DEFAULT   IDLE_TIME                                          KERNEL                         UNLIMITED NO
DEFAULT   CONNECT_TIME                                KERNEL                         UNLIMITED NO
DEFAULT   PRIVATE_SGA                                   KERNEL                           UNLIMITED NO
DEFAULT   FAILED_LOGIN_ATTEMPTS           PASSWORD                       10                 NO
DEFAULT   PASSWORD_LIFE_TIME                  PASSWORD                       180                NO
DEFAULT   PASSWORD_REUSE_TIME              PASSWORD                       UNLIMITED NO
DEFAULT   PASSWORD_REUSE_MAX              PASSWORD                       UNLIMITED NO
DEFAULT   PASSWORD_VERIFY_FUNCTION   PASSWORD                             NULL    NO
DEFAULT   PASSWORD_LOCK_TIME                 PASSWORD                                1         NO
DEFAULT   PASSWORD_GRACE_TIME                PASSWORD                           7              NO

How to modify a Profile

SQL> ALTER PROFILE MyFirstProfile LIMIT PASSWORD_REUSE_TIME 90 PASSWORD_REUSE_MAX UNLIMITED;

How to delete a Profile

SQL> DROP PROFILE MyFirstProfile CASCADE;