ORACLE DATABASE ADMINISTRATION
User Account Management
ORACLE DATABASE ADMINISTRATION SERIES
S.N STEPHEN N NJOROGE
LinkedIn: [Link]/in/stephen-njoroge
SENIOR DATABSE ADMINISTRATOR | MSSQL | ORACLE| PostgreSQL |OCI |AWS |
DATABASE ADMINISTRATION SERIES |
Creating a User Account in Oracle Database
Here's a comprehensive guide to creating a user account in Oracle Database, including setting quotas, profiles and
privileges.
Step 1: Connect to the Database as Administrator
sqlplus / as sysdba
Step 2: Check Existing Tablespaces
SELECT tablespace_name FROM dba_tablespaces;
Step 3: Create a Profile
CREATE PROFILE app_user_profile LIMIT
SESSIONS_PER_USER 5
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 60
IDLE_TIME 20
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
COMPOSITE_LIMIT 5000000
PRIVATE_SGA 15K
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME 180
PASSWORD_REUSE_MAX 10
2
User Account Management
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7;
Step 4: Create the User
CREATE USER itop_user
IDENTIFIED BY "Secure2025"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE app_user_profile
QUOTA 100M ON users
QUOTA 50M ON example
ACCOUNT UNLOCK;
Step 5: Grant Privileges to the User
Basic privileges:
GRANT CREATE SESSION TO itop_user;
Additional privileges
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO itop_user;
GRANT UNLIMITED TABLESPACE TO itop_user; -- Alternative to quotas
3
User Account Management
Role-based approach:
GRANT CONNECT, RESOURCE TO itop_user;
CREATE ROLE app_developer;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO app_developer;
GRANT app_developer TO itop_user;
Step 6: Verify the User Creation
COLUMN USERNAME FORMAT A15
COLUMN ACCOUNT_STATUS FORMAT A20
COLUMN DEFAULT_TABLESPACE FORMAT A20
COLUMN TEMPORARY_TABLESPACE FORMAT A20
COLUMN PROFILE FORMAT A20
SELECT
username, account_status, default_tablespace, temporary_tablespace, profile
FROM dba_users
WHERE username = 'ITOP_USER';
-- Check quotas
SELECT tablespace_name, bytes/1024/1024 MB, max_bytes/1024/1024 max_MB
FROM dba_ts_quotas
WHERE username = 'ITOP_USER';
4
User Account Management
-- Check privileges
SELECT * FROM dba_sys_privs WHERE grantee = 'ITOP_USER';
SELECT * FROM dba_role_privs WHERE grantee = 'ITOP_USER';
5
User Account Management
Step 7: Set Password Policies
ALTER PROFILE app_user_profile LIMIT
PASSWORD_LIFE_TIME 60 -- Password expires in 60 days
PASSWORD_GRACE_TIME 10 -- 10 days grace period to change password
PASSWORD_REUSE_TIME 180 -- Cannot reuse a password for 180 days
PASSWORD_REUSE_MAX 5 -- Cannot reuse last 5 passwords
FAILED_LOGIN_ATTEMPTS 5 -- Account locks after 5 failed attempts
PASSWORD_LOCK_TIME 1/24; -- Account remains locked for 1 hour
Step 8: Modify User
ALTER USER itop_user QUOTA 200M ON users;
ALTER USER itop_user IDENTIFIED BY "newsecure456#";
Step 9: Lock/Unlock Account
ALTER USER itop_user ACCOUNT LOCK; -- To lock
ALTER USER itop_user ACCOUNT UNLOCK; -- To unlock
6
User Account Management
Step 10: Connect as the New User
CONNECT itop_user/newsecure456#@itopuat
7
User Account Management