Oracle User Management :#01

The CREATE USER command creates a system user as shown here.

CREATE USER Test IDENTIFIED BY test12345;

• A DBA must have the CREATE USER system privilege.
• The IDENTIFIED BY clause specifies the user password
• User to connect to Oracle, you must grant the user the CREATE SESSION system privilege.
• Each username must be unique within a database
• Each user/schema for the storage of objects within the database
• Two users can name objects identically because the objects are referred to globally by using a combination of the username and object name.

A complete example of the CREATE USER command:

CREATE USER Test
IDENTIFIED BY test12345
DEFAULT TABLESPACE Users
TEMPORARY TABLESPACE Temp
QUOTA 1M ON Users
PROFILE Developer
ACCOUNT UNLOCK
PASSWORD EXPIRE;

Test has two identified , one for DEFAULT storage of objects and one for TEMPORARY objects.
Test  has the resource limitations allocated by the PROFILE named Developer. The account is unlocked (the default – alternatively the account could be created initially with the LOCK specification).
The PASSWORD EXPIRE clause requires Test to change the password prior to connecting to the database. After the password is set, when the user logs on using SQLPlus or any other software product that connects to the database, the user receives the following message at logon, and is prompted to enter a new password:

[oracle@localhost admin]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 11 07:49:55 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter user-name: test
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for test
New password:
Retype new password:
Password changed


Alter user command : 
To make any other use of the command, a user must have the ALTER USER system privilege.

Use below statement change password for users

alter user test identified by test54321;

we can use Alter command to change tablespace,profile,password,quota etc.

Drop User Command

The DROP USER command is used to drop a user. Examples:

SQL> drop user test1;
User dropped.

Drop user including all reference objects

SQL>DROP USER Test CASCADE;  

Dropping a user causes the user and the user schema to be immediately deleted from the database.
If the user has created objects within their schema, it is necessary to use the CASCADE option in order to drop a user.
If you fail to specify CASCADE when user objects exist, an error message is generated and the user is not dropped.
For drop the user, the DBA must have the DROP USER system privilege.

If you want to deny access to the database, but do not want to drop the user and the
user's objects, you should revoke the CREATE SESSION privilege for the user temporarily.
You cannot drop a user who is connected to the database - you must first terminate the user's session with the ALTER SYSTEM KILL SESSION command.

Default Tablespace:

If one is not specified, the default tablespace for a user is the SYSTEM tablespace – not a good choice for a default tablespace

Temporary Tablespace:

The default Temporary Tablespace for a user is also the SYSTEM tablespace.
Allowing this situation to exist for system users will guarantee that user processing will cause contention with access to the data dictionary.
Generally a DBA will create a TEMP tablespace that will be shared by all users for processing that requires sorting and joins.

Database Authentication:

Database authentication involves the use of a standard user account and password.
Oracle performs the authentication.
System users can change their password at any time.
Passwords are stored in an encrypted format.
Each password must be made up of single-byte characters, even if the database uses a multi-byte character set.

Advantages:

• User accounts and all authentication are controlled by the database. There is no reliance on anything outside of the database.
• Oracle provides strong password management features to enhance security when using database authentication.
• It is easier to administer when there are small user communities.
• Oracle recommends using password management that includes password aging/expiration, account locking, password history, and password complexity verification.

Profile management : #01

Password management

Password management can be easily controlled by a DBA through the use of profiles.

Enabling Password Management

Password management is enabled by creating a profile and assigning the profile to system users when their account is created or by altering system user profile assignments.

Password limits set in this fashion are always enforced. When password management is in use, an existing user account can be locked or unlocked by the ALTER USER command.

Password Account Locking: 

This option automatically locks a system user account if the user fails to execute proper login account name/password entries after a specified number of login attempts.

The FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME parameter are specified as part of a profile.

The FAILED_LOGIN_ATTEMPTS is specified as an integer. 

The PASSWORD_LOCK_TIME is specified as days.

The database account can be explicitly locked with the ALTER USER command. When this happens, the account is not automatically unlocked.

Password Expiration/Aging: 

Specifies the lifetime of a password – after the specified period, the password must be changed.

The PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters are specified as part of a profile.

PASSWORD_LIFE_TIME specifies the maximum life of a password.

If the PASSWORD_GRACE_TIME is exceeded, the account automatically locks.

Both of these parameters are specified in days.

Password History:

This option ensures that a password is not reused within a specified period of time or number of password changes.

If either PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX are set to a value other than DEFAULT or UNLIMITED, the other parameter must be set to UNLIMITED.

PASSWORD_REUSE_TIME is specified in days.

PASSWORD_REUSE_MAX is an integer value specifying the number of password changes required before a password can be reused.

If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED.

If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED

Password Complexity Verification:

This option ensures that a password is complex – this helps provide protection against system intruders who attempt to guess a password.

This is implemented by use of a password verification function. A DBA can write such a function or can use the default function named VERIFY_FUNCTION.

The function that is used for password complexity verification is specified with the profile parameter, PASSWORD_VERIFY_FUNCTION.

If NULL is specified (the default), no password verification is performed.

When a DBA connected as the user SYS executes the utlpwdmg.sql script (located at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql) , the Oracle Server creates the VERIFY_FUNCTION .

[oracle@localhost admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 11 07:19:45 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.

Profile altered.

Function created.

Grant succeeded.

SQL>

Creating a Profile with Password Protection:

SQL> CREATE PROFILE developer LIMIT

PASSWORD_REUSE_TIME 1

PASSWORD_LOCK_TIME 2

PASSWORD_REUSE_MAX 3

FAILED_LOGIN_ATTEMPTS 2

PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;  

Profile created.

SQL>SELECT * FROM DBA_PROFILES WHERE PROFILE ='DEVELOPER';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

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

DEVELOPER                      COMPOSITE_LIMIT                  KERNEL   DEFAULT

DEVELOPER                      SESSIONS_PER_USER                KERNEL   DEFAULT

DEVELOPER                      CPU_PER_SESSION                  KERNEL   DEFAULT

DEVELOPER                      CPU_PER_CALL                     KERNEL   DEFAULT

DEVELOPER                      LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT

DEVELOPER                      LOGICAL_READS_PER_CALL           KERNEL   DEFAULT

DEVELOPER                      IDLE_TIME                        KERNEL   DEFAULT

DEVELOPER                      CONNECT_TIME                     KERNEL   DEFAULT

DEVELOPER                      PRIVATE_SGA                      KERNEL   DEFAULT

DEVELOPER                      FAILED_LOGIN_ATTEMPTS            PASSWORD 2

DEVELOPER                      PASSWORD_LIFE_TIME               PASSWORD DEFAULT

DEVELOPER                      PASSWORD_REUSE_TIME              PASSWORD 1

DEVELOPER                      PASSWORD_REUSE_MAX               PASSWORD 3

DEVELOPER                      PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION

DEVELOPER                      PASSWORD_LOCK_TIME               PASSWORD 2

DEVELOPER                      PASSWORD_GRACE_TIME              PASSWORD DEFAULT

SQL> create user tes identified by te;

create user tes identified by te

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20001: Password length less than 8

SQL>

Using the Data Dictionary:

Information about password and resource limits can be obtained by querying the following views:

DBA_USERS

DBA_PROFILES

Database Profiles

Profile – is a database object – a named set of resource limits to:

Restrict database usage by a system user – profiles restrict users from performing operations that exceed reasonable resource utilization. Examples of resources that need to be managed:

• Disk storage space.
• I/O bandwidth to run queries.
• CPU power.
• Connect time.

Enforce password practices – how user passwords are created, reused, and validated
Profiles are assigned to users as part of the CREATE USER or ALTER USER commands

• User accounts can have only a single profile
• A default profile can be created – a default already exists within Oracle named
• DEFAULT – it is applied to any user not assigned another profile.
• Assigning a new profile to a user account supersedes any earlier profile.
• Profiles cannot be assigned to roles or other profiles.

Profiles only take effect when resource limits are "turned on" for the database as a whole. Specify the RESOURCE_LIMIT initialization parameter.

RESOURCE_LIMIT = TRUE

Use the ALTER SYSTEM statement to turn on resource limits.

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;


Profile Specifications

Profile specifications include:

• Password aging and expiration
• Password history
• Password complexity verification
• Account locking
• CPU time
• Input/output (I/O) operations
• Idle time
• Connect time
• Memory space (private SQL area for Shared Server only)
• Concurrent sessions

If users not assigned a specific profile are automatically assigned the DEFAULT profile. The DEFAULT profile has only one significant restriction it doesn't specify a password verification function.

This query lists the resource limits for the DEFAULT profile


SQL> COLUMN profile FORMAT A10;
COLUMN resource_name FORMAT a30;
COLUMN resource FORMAT a8;
COLUMN limit FORMAT a15;
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
DEFAULT    CPU_PER_CALL                   KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_SESSION      KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_CALL         KERNEL   UNLIMITED
DEFAULT    IDLE_TIME                      KERNEL   UNLIMITED
DEFAULT    CONNECT_TIME                   KERNEL   UNLIMITED
DEFAULT    PRIVATE_SGA                    KERNEL   UNLIMITED
DEFAULT    FAILED_LOGIN_ATTEMPTS          PASSWORD 10
DEFAULT    PASSWORD_LIFE_TIME             PASSWORD 180
DEFAULT    PASSWORD_REUSE_TIME            PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX             PASSWORD UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION       PASSWORD NULL
DEFAULT    PASSWORD_LOCK_TIME             PASSWORD 1
DEFAULT    PASSWORD_GRACE_TIME            PASSWORD 7



Creating a Profile
===================

DBA creates a profile with the CREATE PROFILE command.
This command has clauses that explicitly set resource limits. A DBA must have the CREATE PROFILE system privilege in order to use this command.
Example:

CREATE PROFILE developer LIMIT
SESSIONS_PER_USER 4
CPU_PER_SESSION unlimited
CPU_PER_CALL 6000
LOGICAL_READS_PER_SESSION unlimited
LOGICAL_READS_PER_CALL 100
IDLE_TIME 1
CONNECT_TIME 480
PASSWORD_REUSE_TIME 1
PASSWORD_LOCK_TIME 2
PASSWORD_REUSE_MAX 3
FAILED_LOGIN_ATTEMPTS 2;

Resource limits that are not specified for a new profile inherit the limit set in the DEFAULT profile.


Assigning Profiles
===========================

Profiles can only be assigned to system users if the profile has first been created. Each system user is assigned only one profile at a time. When a profile is assigned to a system user who already has a profile, the new profile replaces the old one – the current session,if one is taking place, is not affected, but
subsequent sessions are affected. Also, you cannot assign a profile to a role or another profile

profiles are assigned with the CREATE USER and ALTER USER
command. An example CREATE USER command

CREATE USER userp IDENTIFIED BY userp
PROFILE developer;

SELECT username, profile FROM dba_users
WHERE username = 'USERP';


Altering Profiles
====================
Profiles can be altered with the ALTER PROFILE command.
A DBA must have the ALTER PROFILE system privilege to use this command.
When a profile limit is adjusted, the new setting overrides the previous setting for
the limit, but these changes do not affect current sessions in process.
Example:

ALTER PROFILE developer LIMIT
CPU_PER_CALL default
LOGICAL_READS_PER_SESSION 20000
SESSIONS_PER_USER 1;




Dropping a Profile
=========================

Profiles no longer required can be dropped with the DROP PROFILE command.
The DEFAULT profile cannot be dropped.
The CASCADE clause revokes the profile from any user account to which it was
assigned – the CASCADE clause MUST BE USED if the profile has been assigned to any user account.
When a profile is dropped, any user account with that profile is reassigned the DEFAULT profile.
Examples



SQL> drop profile developer;
drop profile developer
*
ERROR at line 1:
ORA-02382: profile DEVELOPER has users assigned, cannot drop without CASCADE

SQL> DROP PROFILE DEVELOPER CASCADE;

Profile dropped.

SQL> SELECT username, profile FROM dba_users
WHERE username = 'USERP';  

USERNAME                       PROFILE
------------------------------ ------------------------------
USERP                          DEFAULT

SQL>

Changes that result from dropping a profile only apply to sessions that are created after the change – current sessions are not modified.

Managing Tablespaces : #4

View information about Tablespace & Datafiles 


Oracle has provided many Data dictionaries to view information about tablespaces and datafiles. Some of them are:

To view information about Tablespaces in a database give the following query

SQL> select * from dba_tablespaces
SQL> select * from v$tablespace;

To view information about Datafiles
SQL> select * from dba_data_files;
SQL> select * from v$datafile;

To view information about Tempfiles
SQL> select * from dba_temp_files;
SQL> select * from v$tempfile;

To view information about free space in datafiles
SQL> select * from dba_free_space;

To view information about free space in tempfiles
SQL> select * from V$TEMP_SPACE_HEADER;

Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 500 lines 500
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;


Check the tablespace Utilization.

Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"

set pages 500 lines 3276
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

Tablespace Utilization Script (including AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)
---------------------------------------------------------------------------------------------------------------------
set pages 500 lines 3276
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 500 lines 327
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;