Hands-On 20 (Maintaining user’s account and profile)

As a DBA, you are responsible for maintaining user accounts due to the growth of the organization. Also, due to new users and the abuse of the database resources, you have been assigned to create a profile to limit a group of users from using the database resources. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Creating a user

Granting object privileges to a user

Assigning a default tablespace to a user

Assigning a temporary tablespace to a user

Assigning a quota to a user

Assigning a profile to a user

Expiring a user password

Creating a profile

Changing a password

Allocating resource limitations to a profile

Locking a user account

Unlocking a user account

Using the DBA_USERS view

Using the ALL_OBJECTS view

Using the DEFAULT profile

Activating the resource limit system parameter

Dropping a user

Dropping a profile

Commands:

CREATE USER

CONNECT system/manager AS SYSDBA

ALTER USER ACCOUNT LOCK

ALTER USER ACCOUNT UNLOCK

CREATE PROFILE LIMIT

ALTER USER PROFILE

ALTER SYSTEM SET resource_limit=TRUE

DROP USER CASCADE

DROP PROFILE CASCADE

-- Hands-On 20 (Maintaining user's account and profile)
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
SET SQLPROMPT 'SQL> '
COL name FORMAT a30
col username format a10
col password format a8
col account_status format a15
col default_tablespace format a20
col temporary_tablespace format a20
col profile format a20
pause

--Start

CLEAR SCR
-- In this exercise you will learn how to create a
-- user account with it's own default tablespace and 
-- more. 

-- You will learn how to create a user profile and how
-- to allocate resource limitations on each account.

-- Also, you will learn how to lock and unlock a user
-- account.

-- Now, let's connect as the system/manager user.

pause


CONNECT system/manager

pause

CLEAR SCR
-- Query the DBA_USERS view to display a list of 
-- all the usernames of that start with the
-- letter D.

pause


SELECT username, password, account_status,
default_tablespace, temporary_tablespace, 
profile
FROM dba_users
WHERE username like 'D%'
/

-- It looks like we do not have a username by the name of
-- DEVELOPER.

pause

CLEAR SCR
-- Let's create a username DEVELOPER with the following
-- options:
-- Password is DEVELOPER, 
-- The default tablespace is ISELF_DATA, 
-- The temporary tablespace is TEMP,
-- Allow to use 10k of space on the ISELF_DATA tablespace,
-- No permission to use the SYSTEM tabespace,
-- Use the default profile, 
-- Force the user to enter the new password, and 
-- Account cannot be locked.

pause


CREATE USER developer
IDENTIFIED BY developer
DEFAULT TABLESPACE iself_data
TEMPORARY TABLESPACE temp
QUOTA 10K ON iself_data
QUOTA 0K ON SYSTEM
PROFILE default
PASSWORD EXPIRE
ACCOUNT UNLOCK
/

pause

CLEAR SCR
-- Now, view the list of all the users, whom their usernames
-- start with a letter D.

pause


SELECT username, password, account_status,
default_tablespace, temporary_tablespace, 
profile
FROM dba_users
WHERE username like 'D%'
/

pause

CLEAR SCR
-- Now, grant to DEVELOPER user the CONNECT and RESOURCE roles.

pause


GRANT CONNECT, RESOURCE TO DEVELOPER
/

pause

CLEAR SCR
-- Connect to SQLPlus as the DEVELOPER user.
-- Notice that you have to change the your password.
-- Change it to mypass.

pause


CONNECT DEVELOPER/DEVELOPER

pause

CLEAR SCR
-- Connect to SQLPlus as the DEVELOPER user with
-- the new password and query a simple SQL statement.

pause

CONNECT DEVELOPER/mypass

SELECT COUNT(*)
FROM all_objects
/

pause

CLEAR SCR
-- Connect as the system/manager user and lock 
-- the DEVELOPER user.

pause


CONNECT system/manager AS SYSDBA

ALTER USER DEVELOPER ACCOUNT LOCK
/

pause

CLEAR SCR
-- Now, connect to SQLPlus as the DEVELOPER user.
-- Notice that the DEVELOPER account was locked.

pause


CONNECT DEVELOPER/mypass

pause

CLEAR SCR
-- Connect as the system/manager user and unlock the
-- DEVELOPER user.

pause

CONNECT system/manager

ALTER USER DEVELOPER ACCOUNT UNLOCK
/

-- Now, the DEVELOPER user can login to SQL*PLUS.

pause


CLEAR SCR
-- Since the DEFAULT profile gives users unlimited 
-- use of all the resources definable in the database,

-- create a user profile that has some host system 
-- usage restrictions on it. 

-- The following are restrictions, needed for the 
-- DEVELOPER users.

-- The user should not open more that one session, 
-- The maximum allowed CPU time in a session is to be 10 seconds, 
-- The user can be connected for total amount of 8 hours, and
-- The user can issue no commands for 1 hour.

pause

CREATE PROFILE developer LIMIT
SESSIONS_PER_USER 1
CPU_PER_SESSION 1000
CONNECT_TIME 4800
IDLE_TIME 60
/

pause

CLEAR SCR
-- View the developer profile with their parameter values.

pause


SELECT * 
FROM dba_profiles
WHERE limit <> 'DEFAULT' AND
profile = 'DEVELOPER'
ORDER BY profile, limit
/

-- Notice that the developer profile was created.

pause

CLEAR SCR
-- Assign the DEVELOPER profile to the DEVELOPER user.

pause

ALTER USER developer PROFILE developer
/

pause

CLEAR SCR
-- Activate the resource limit system parameter to true.

pause

ALTER SYSTEM SET resource_limit=TRUE
/

pause

CLEAR SCR
-- Now, connect as the DEVELOPER user.
-- Then, go the other session and try to connect to 
-- SQLPlus as the DEVELOPER user.

-- Remember that since the DEVELOPER profile is active
-- we are restricted to one and only one session to be
-- opened.

pause

CONNECT developer/mypass

pause

CLEAR SCR
-- Now, connect as the SYSTEM/MANAGER user, and drop
-- the DEVELOPER user and the DEVELOPER profile.

pause

CONNECT system/manager AS SYSDBA

DROP USER developer
CASCADE
/

DROP PROFILE developer
CASCADE
/

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause