Creating and Altering User Profiles

User profiles:
------------------
Specifying a Profile. You also specify a profile when you create a user. A profile is a set of limits on database resources and password access to the database. If no profile is specified, then the user is assigned a default profile.

Views: DBA_PROFILES

This view contains info about the profiles


SQL> desc dba_profiles
 Name                                                 Null?    Type
 ---------------------------------------------------- -------- ------------------------------------
 PROFILE                                              NOT NULL VARCHAR2(128)
 RESOURCE_NAME                                        NOT NULL VARCHAR2(32)
 RESOURCE_TYPE                                                 VARCHAR2(8)
 LIMIT                                                         VARCHAR2(128)
 COMMON                                                        VARCHAR2(3)


Eg:

Creating profile:
---------------------

CREATE PROFILE "Pavan" LIMIT CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_LOCK_TIME 1
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_VERIFY_FUNCTION NULL;


Assigning user to the profile:
------------------------------------
ALTER USER pk PROFILE pavan;



Eg:
Limiting sessions per user:
----------------------------------

Change the value of SESSIONS_PER_USER (along with the other parameters, where relevant) from UNLIMITED to DoD-compliant, site-specific requirements and then assign users to the profile

Altering profiles:
---------------------
ALTER PROFILE new_profile 
   LIMIT PASSWORD_REUSE_TIME 90
   SESSIONS_PER_USER 10
   PASSWORD_REUSE_MAX UNLIMITED;



Checking the profile of the particular user:

View: DBA_USERS

SQL> desc dba_users
 Name                                                 Null?    Type
 ---------------------------------------------------- -------- ------------------------------------
 USERNAME                                             NOT NULL VARCHAR2(128)
 USER_ID                                              NOT NULL NUMBER
 PASSWORD                                                      VARCHAR2(4000)
 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(128)
 INITIAL_RSRC_CONSUMER_GROUP                                   VARCHAR2(128)
 EXTERNAL_NAME                                                 VARCHAR2(4000)
 PASSWORD_VERSIONS                                             VARCHAR2(12)
 EDITIONS_ENABLED                                              VARCHAR2(1)
 AUTHENTICATION_TYPE                                           VARCHAR2(8)
 PROXY_ONLY_CONNECT                                            VARCHAR2(1)
 COMMON                                                        VARCHAR2(3)
 LAST_LOGIN                                                    TIMESTAMP(9) WITH TIME ZONE
 ORACLE_MAINTAINED                                             VARCHAR2(1)

This atrribute PROFILE in the above  view contain the name of the profile assigned to the user.






Comments

Post a Comment

Popular posts from this blog

Oracle Database Client(12.1.0.2.0) Installation for Microsoft Windows(x64)

Image
Step1) Download Oracle Client 12C from below link http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html Oracle Database Client (12.1.0.2.0) for Microsoft Windows (x64) winx64_12102_client.zip  (64-bit) (925,039,944 bytes) Oracle Database Client (12.1.0.2.0) for Microsoft Windows (32-bit) winnt_12102_client32.zip  (32-bit) (902,922,170 bytes)  chose the one based on your laptop Step2) Extract the downloaded file Oracle 12c 64 bit client has now been extracted. Double click ‘setup.exe’. This may test your patience, let it work! (Sometimes a minute or less can seem like an eternity). Don’t touch anything! Administrator: Enables applications to connect to an Oracle database on the local system or on a remote system. It provides tools that let y...
Read more

configuring goldengate

configuring goldengate Pre req:  Install Goldengate software on both the servers golden gate home: /u02/app/goldengate Update bash_profile on both Source and Target side [oracle@localhost ~]$ vi .bash_profile if [ -f ~/.bashrc ]; then         . ~/.bashrc fi # User specific environment and startup programs export ORACLE_SID=test export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 export GRID_HOME=/u01/app/oracle/product/12.1.0/grid export LD_LIBRARY_PATH=$ORACLE_HOME/lib export PATH=$ORACLE_HOME/bin:$PATH: export PATH=$GRID_HOME/bin:$PATH: export PATH=$ORACLE_HOME/OPatch:$PATH: PATH=$PATH:$HOME/bin export PATH export GGS_HOME=/u02/app/goldengate alias ggn='cd ${GGS_HOME}' alias ggsci='cd ${GGS_HOME}/ggsci' export LD_LIBRARY_PATH=$GGS_HOME:$ORACLE_HOME/lib:/lib:/usr/lib ".bash_profile" 25L, 684C                                    ...
Read more

Configuring oracle enterprise manager express 12c

1.TO configure EM express and connect to the database using EM database Express the Listener should be up an running. [oracle@pavanorcl101 ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-OCT-2017 15:17:47 Copyright (c) 1991, 2014, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pavanorcl101.mydomain.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date                17-OCT-2017 15:17:22 Uptime                    0 days 0 hr. 0 min. 25 sec Trace Level               off Security                ...
Read more