Lab 10

1

• Lab 10
• Users and Profiles

Eric Langager College of Software Beijing
University of Aeronautics and Astronautics
2
Objectives

• Create, modify, and remove users
• Discover when and how to create, use, and drop
  profiles
• Manage passwords
• View information about users, profiles,
  passwords, and resources

3
Overview of Database Users

• Creating New Users
• Modifying User Settings with the ALTER USER
  Statement
• Removing Users

4
Creating New Users

• The syntax block for the CREATE USER command is

5
Creating New Users

• The first 2 lines in the CREATE USER command are
  required
• CREATE USER ltnamegt
• IDENTIFIED lthowgt
• Can be done in 3 ways
• IDENTIFIED BY ltpasswordgt
• IDENTIFIED EXTERNALLY
• IDENTIFIED GLOBALLY AS ltnamegt

6
Creating New Users

• The following lines in the CREATE USER command
  are optional (have default values)
• DEFAULT TABLESPACE lttbspnamegt
• Used to assign a default tablespace if the user
  is allowed to create database objects
• TEMPORARY TABLESPACE lttmpnamegt
• Used to assign a temporary tablespace for the user

7
Creating New Users

• QUOTA ltngt ON lttbspnamegt
• Used to limit the amount of tablespace allocated
  to the user. Key points in setting a quota
  include
• Define quotas in any tablespace except the undo
  tablespace, which is used only for undo records
• Define quotas to as many tablespaces as you want
• Set a tablespace quota to unlimited by using the
  UNLIMITED keyword
• Set a tablespace quota to zero by using the
  number zero as the quota.

8
Creating New Users

• PROFILE ltpnamegt
• Used to assign a profile to this user. Default
  profile is used if none is provided
• PASSWORD EXPIRE
• Used to force the new password to expire
  immediately. This prompts the user for a new
  password
• ACCOUNT ltlockgt
• Used with either LOCK or UNLOCK to control the
  users access to the database

9
Creating New Users

• After creating a new user, give user a system
  privilege to allow them to log onto the database
• GRANT CREATE SESSION TO ltusergt
• Users can now log on, but cannot create tables
  until they have further system privileges

10
Modifying User Settings with the ALTER USER
Statement

• The syntax block for the ALTER USER command is
  very similar to the CREATE USER command

11
Modifying User Settings with the ALTER USER
Statement

• Quotas and the amount of used space are listed in
  DBA_TS_QUOTAS

Username Tablespace Used Space Quota
12
Removing Users

• Requires DROP USER privilege
• To remove a user who owns no tables or objects
  use DROP USER ltusergt
• To remove a user who owns tables and/or objects
  use DROP USER ltusergt CASCADE

13
Introduction To Profiles

• Users are assigned a profile
• Profiles can be used to manage user passwords
• Profiles are also used to limit the system
  resources that are available to a user
• One profile can be assigned to multiple users
• A user can be assigned only one profile at a time

14
Introduction To Profiles

• Creating Profiles
• Managing Passwords
• Controlling Resource Usage
• Dropping a Profile

15
Creating Profiles

• Basic syntax for creating a profile
• CREATE PROFILE ltprofilegt LIMIT
• ltpassword_settinggt ...
• ltresource_settinggt ltlimitgt ...
• Three important pieces above
• Password settings
• Resource settings
• Limit, which must be set to UNLIMITED, DEFAULT,
  or a number

16
Password Settings

• FAILED_LOGIN_ATTEMPTS
• Maximum number of times the user can retry the
  password before the account is locked
• PASSWORD_LIFE_TIME
• Maximum number of days the password can be used
  without changing
• PASSWORD_REUSE_TIME
• Minimum number of days before the same password
  can be used again
• PASSWORD_REUSE_MAX
• Minimum number of times the password must change
  before the same password can be used again

17
Password Settings

• PASSWORD_LOCK_TIME
• Number of days (or a fraction of a day) that the
  account is locked after the maximum set by
  FAILED_LOGIN_ATTEMPTS has been reached
• PASSWORD_GRACE_TIME
• Number of days after the password expires in
  which the user is given a warning that the
  password is expired, and is allowed to log on
  with the old password
• PASSWORD_VERIFY_FUNCTION
• Database function called to verify the complexity
  of the password

18
Resource Settings

• SESSIONS_PER_USER
• Set the maximum number of concurrent sessions.
• CPU_PER_SESSION
• Set the maximum CPU time (in hundredths of a
  second) for a users entire session.
• CPU_PER_CALL
• Set the maximum CPU time (in hundredths of a
  second) for any single call to the database. A
  call is a single task sent to the database, such
  as parsing a new SQL statement or executing a
  query.

19
Resource Settings

• CONNECT_TIME
• Set the maximum number of minutes a users
  session can last.
• IDLE_TIME
• Set the maximum number of minutes a users
  session can remain inactive. A long-running
  query or other task in which the user is waiting
  for the database does not count toward idle time.
• LOGICAL_READS_PER_SESSION
• Set the maximum number of data blocks read from
  either memory or disk during a users session.

20
Resource Settings

• LOGICAL_READS_PER_CALL
• Set the maximum number of data blocks read during
  a single call.
• PRIVATE_SGA
• Set the number of bytes (in bytes, kilobytes, or
  megabytes) that the users session may allocate
  in the shared pool of the SGA.
• Only applies to a shared server, in which part of
  the shared pool is allocated to user sessions.
  Ignored on dedicated servers.
• COMPOSITE_LIMIT
• Set the maximum resource cost (in service units)
  for a session.
• A service unit calculates a resource cost based
  on a weighted sum of CPU_PER_SESSION,
  CONNECT_TIME, LOGICAL_READS_PER_SESSION, and
  PRIVATE_SGA.

21
Managing Passwords

• Change a password and make it expire
• Enforce password time limits, history, and other
  settings
• Enforce password complexity

22
Controlling Resource Usage

• Before adjusting resource limits you must change
  the initialization parameter named
  RESOURCE_LIMITS to a value of TRUE
• To enforce resource limits on a profile, use the
  ALTER PROFILE command with resource clauses

23
Controlling Resource Usage

• Use ALTER PROFILE with resource clauses to add
  resource restrictions, as follows
• ALTER PROFILE ltprofilegt LIMIT
• ltpassword settinggt ...
• SESSIONS_PER_USER ltconcurrent sessionsgt
• CPU_PER_SESSION lthundredths of secondsgt
• CONNECT_TIME ltminutesgt
• IDLE_TIME ltminutesgt
• LOGICAL_READS_PER_SESSION ltdata blocksgt
• LOGICAL_READS_PER_CALL ltdata blocksgt
• PRIVATE_SGA ltbytesgt
• COMPOSITE_LIMIT ltservice unitsgt

24
Dropping a Profile

• Use the DROP PROFILE command to remove a profile
• (similar to DROP USER)
• If users have been assigned this profile, use
  CASCADE
• DROP PROFILE ltprofilegt CASCADE
• If no users have been assigned this profile,
  CASCADE is unnecessary
• DROP PROFILE ltprofilegt

25
Obtaining Profile, Password, and Resource Data

• Run queries on the following dictionary views to
  gather information on users, quotas, and
  profiles
• DBA_USERS View user profile, expiration date of
  password, and account status
• DBA_TS_QUOTAS View the storage quotas for each
  user
• RESOURCE_COST View the weight setting for each
  resource used in calculating COMPOSITE_COST
• DBA_PROFILES View the settings for each profile

26
Obtaining Profile, Password, and Resource Data

• The console can be used to view and alter
  information on users, roles, and profiles without
  writing any code

Users, Roles, and Profiles are handled by the
security manager
27
Obtaining Profile, Password, and Resource Data

• Browse users and select one to bring up the
  property sheet for that user

User Name User Profile Password Tablespaces
28
Obtaining Profile, Password, and Resource Data

• Quota tab on users property sheet can be used to
  alter tablespace quotas for that user

Quota tab Tablespaces
29
Obtaining Profile, Password, and Resource Data

• If you browse the profiles in the security
  manager and select one, it will bring up the
  property sheet for that profile

Resource Limits Selected Profile
30
Obtaining Profile, Password, and Resource Data

• Password tab contains restrictions and settings
  for the passwords of users with this profile

Password Tab Password Expiration Password
Complexity Password History Password Lockout