Chapter 3 Administration of Users

1
Chapter 3Administration of Users
2
Objectives

• Understand the importance of administration
  documentation
• Outline the concept of operating system
  authentication
• Be able to create, modify, and delete users and
  logins in Oracle10g
• Explain the concept of a remote user
• List the risks of database links
• List the security risks of linked servers
• List the security risks of remote servers
• Describe best practices for user administration

3
Documentation of User Administration

• Part of the administration process
• Reasons to document
• Provide a paper trail
• Ensure administration consistency
• What to document
• Administration policies, staff and management
• Security procedures
• Procedure implementation scripts or programs
• Predefined roles description

4
Documentation of User Administration (continued)
5
Documentation of User Administration (continued)
6
Operating System Authentication

• Many databases (including Microsoft SQL Server
  2000) depend on OS to authenticate users
• And Oracle allows the option of OS based
  authentication
• Reasons
• Once an intruder is inside the OS, it is easier
  to access the database
• Centralize administration of users
• Users must be authenticated at each level

7
Operating System Authentication (continued)
8
Creating Users

• Must be a standardized, well-documented, and
  securely managed process
• In Oracle10g, use the CREATE USER statement
• Part of the a Data Definition Language (DDL)
• Account can own different objects

9
Users and Security
Accountlocking
Default tablespace
Temporary tablespace
Authentication mechanism
Security domain
Role privileges
Tablespacequotas
Direct privileges
Resource limits
10
Database Schema
Schema Objects Tables Triggers Constraints Indexes
Views Sequences Stored program
units Synonyms User-defined data types Database
links

• A schema is a named collection of objects.
• A user is created, and a corresponding schema is
  created.
• A user can be associated only with one schema.
• Username and schema are often used
  interchangeably.

11
Checklist for Creating Users

• Identify tablespaces in which the user needs to
  store objects.
• Decide on quotas for each tablespace.
• Assign a default tablespace and temporary
  tablespace.
• Create a user.
• Grant privileges and roles to the user.

12
Creating a New User Database Authentication

• Set the initial password

CREATE USER aaron IDENTIFIED BY soccer DEFAULT
TABLESPACE data DEFAULT TEMPORARY TABLESPACE
temp QUOTA 15M ON data QUOTA 10M ON
users PASSWORD EXPIRE
13
Creating a New User Operating System
Authentication

• The OS_AUTHENT_PREFIX initialization parameter
  specifies the format of the usernames.
• Defaults to OPS.

CREATE USER aaron IDENTIFIED EXTERNALLY DEFAULT
TABLESPACE USERS TEMPORARY TABLESPACE temp QUOTA
15m ON data PASSWORD EXPIRE
14
Changing User Quota on Tablespaces

• A users tablespace quotas may be modified for
  any the following situations
• Tables owned by a user exhibit unanticipated
  growth.
• An application is enhanced and requires
  additional tables or indexes.
• Objects are reorganized and placed in different
  tablespaces.
• To modify a users tablespace quota

ALTER USER aaron QUOTA 0 ON USERS
15
Creating an Oracle10g User

• IDENTIFIED clause
• Tells Oracle how to authenticate a user account
• BY PASSWORD option encrypts and stores an
  assigned password in the database
• EXTERNALLY option user is authenticated by the
  OS
• GLOBALLY AS option depends on authentication
  through centralized user management method

16
Creating an Oracle10g User (continued)
17
Creating an Oracle10g User (continued)

• PASSWORD EXPIRE clause tells Oracle to expire
  the user password and prompts the user to enter a
  new password
• ACCOUNT clause enable or disable account
• ALTER USER modifies a user account
• Oracle Enterprise Manager GUI administration tool

18
Creating an Oracle User Using Global
Authentication

• Enterprise-level authentication solution
• Use the CREATE USER statement
• Specify an external_name which is the name used
  for global authentication
• DBA_USERS view contains information about all
  accounts
• Includes the external name

19
Removing Users

• Simple process
• Make a backup first
• Obtain a written request (for auditing purposes)

20
Removing an Oracle User

• DROP command
• CASCADE option when user owns database objects
• Recommendations
• Backup the account for one to three months
• Listing all owned objects
• Lock the account or revoke the CREATE SESSION
  privilege

21
Dropping a User


DROP USER aaron

• Use the CASCADE clause to drop all objects in the
  schema if the schema contains objects.
• Users who are currently connected to the Oracle
  server cannot be dropped.

DROP USER aaron CASCADE
22
Modifying Users

• Modifications involve
• Changing passwords
• Locking an account
• Increasing a storage quota
• ALTER USER DDL statement

23
Obtaining User Information

• Information about users can be obtained by
  querying the following views
• DBA_USERS
• DBA_TS_QUOTAS

24
Default Users

• Oracle default users
• SYS, owner of the data dictionary
• SYSTEM, performs almost all database tasks
• ORAPWD, creates a password file

25
Remote Users
26
Database Links

• Connection from one database to another allow
  DDL and SQL statements
• Types PUBLIC and PRIVATE
• Authentication Methods
• CURRENT USER
• FIXED USER
• CONNECT USER

27
Database Links (continued)
28
Practices for Administrators and Managers

• Manage
• Accounts
• Data files
• Memory
• Administrative tasks
• Backup
• Recovery
• Performance tuning

29
Best Practices

• Follow companys policies and procedures
• Always document and create logs
• Educate users
• Keep abreast of database and security technology
• Review and modify procedures
• Block direct access to database tables
• Limit and restrict access to the server
• Use strong passwords
• Patches, patches, patches

30
Summary

• Document tasks and procedures for auditing
  purposes
• Creating users
• CREATE USER statement in Oracle
• Removing users
• SQL DROP statement
• Modifying user attributes ALTER USER DDL
  statement
• Local database and users
• Remote users
• Database links
• Linked servers