Managing Users

1
Managing Users
2
Objectives

• Creating new database users
• Altering and dropping existing database users
• Monitoring information about existing users

3
Users and Security
Default tablespace
Accountlocking

• DBA defines users who can access db
• Security domain defines the settings that apply
  to users

Authentication mechanism
Temporary tablespace
Security domain
Role privileges
Tablespacequotas
Resource limits
Direct privileges
4

Database Schema (Some of the objects a user can
own)
Tables - Triggers - Constraints Indexes Views Sequ
ences Stored program units Synonyms User-defined
data types Database links

• Schema named collection of objects like tables,
  views, procedures, etc.
• When a user is created a schema with same name is
  created
• Hence username and schema name used
  interchangeably

5

Checklist for Creating Users (Developers not end
users)
1. Choose a username and authentication
mechanism. 2. Identify tablespaces in which
the user needs to store objects. 3. Decide on
quotas for each tablespace. 4. Assign default
tablespace and temporary tablespace. 5. Create
a user. 6. Grant privileges and roles to the user.
6
Creating a New User Server Authentication

• Set the initial password

CREATE USER peter IDENTIFIED BY my1stson DEFAULT
TABLESPACE data01 TEMPORARY TABLESPACE temp QUOTA
15m ON data01 PASSWORD EXPIRE
Expires at login forcing user to change password
7

Creating a New User Operating System
Authentication (User logs on to machine running
Oracle server)
Use OS_AUTHENT_PREFIX (in parameter
file) Example O/S User user15
Remote LoginPossible
OS_AUTHENT_PREFIX OS_empty string
OPS(default)
Database User OS_USER15USER15 OPSUSER15(defa
ult)
Create Oracle User OS_user15
No
No
Yes
8

Creating a New User Operating System
Authentication (User logs on to machine running
Oracle server)

• E.g., An OS user tikekarr
• Use IDENTIFIED EXTERNALLY clause with create user
• Also exists as a database user
• Oracle will not validate
• To use sqlplus say
• Sqlplus /

9
Creating a New User Guidelines

• Choose a standard password initiallyuse O/S
  authentication sparingly.
• Use the EXPIRE keyword to force users to reset
  their passwords.
• Always assign temporary tablespace.
• Restrict quotas to few usersuse QUOTA UNLIMITED
  with caution.
• Educate users
• To connect
• To change password

10
Controlling Account Lock and Password

ALTER USER peter IDENTIFIED BY hisgrandpa PASSWORD
EXPIRE
11
Changing User Quota on Tablespace

ALTER USER peter QUOTA 0 ON data01

• To get a user out of system (fired/resigned)
• Use password expiration
• Lock account
• Alter password
• Change profile
• Export/import user schema elsewhere

12
Dropping a User


DROP USER peter


Use the CASCADE clause if theschema contains
objects.
DROP USER peter CASCADE
User currently connected cannot be dropped
13
Monitoring Users
DBA_USERS USERNAME USER_ID CREATED ACCOUNT_STATUS
LOCK_DATE EXPIRY_DATE DEFAULT_TABLESPACE TEMPORAR
Y_TABLESPACE
DBA_TS_QUOTAS USERNAME TABLESPACE_NAME BYTES MAX_
BYTES BLOCKS MAX_BLOCKS
14
Monitoring Users
Select tablespace_name, blocks, max_blocks,
bytes, max_bytes From dba_ts_quota Where username
SCOTT -1 in MAX_BLOCKS or MAX_BYTES
indicates unlimited quota Select username,
account_status, temporary_tablespace From
dba_users -lists all users, their account status
and temp. ts
15
Summary

• Creating users specifying the appropriate
  password mechanism
• Controlling usage of space by users