Managing Users
DownLoad Link at the Last:
Youtube Video Link:
Lecture 6
Lecture 7
Lecture 8
Lecture 9
Lecture 10
Lecture 11
Objectives
After completing this lesson, you should be able to do the following:
Create new database users
Alter and drop existing database users
Monitor information about existing users
Users and Security
Security domain:
The database administrator defines the names of the users who are allowed to access a database. A security domain defines the settings that apply to the user.
Authentication mechanism:
A user who requires access to the database can be authenticated by one of the following:
Data dictionary
Operating system
Network
The means of authentication is specified at the time the user is defined in the database and can be altered later. This lesson covers authentication by database and by operating system only.
Note: Refer to the “Getting Started with the Oracle Server” lesson for details regarding operating system authentication using roles.
Authentication through the network is covered in the course Oracle9i Database Administration Fundamentals II.
Tablespace quotas:
Tablespace quotas control the amount of physical storage space that is allocated to a user in the tablespaces in the database.
Default tablespace:
The default tablespace defines the location where segments that are created by a user are stored if the user does not explicitly specify a tablespace at the time the segment is created.
Temporary tablespace:
Temporary tablespace defines where extents will be allocated by the Oracle server if the user performs an operation that requires writing sort data to the disk.
Account locking:
Accounts can be locked to prevent a user from logging on to the database. This can be set to occur automatically, or the database administrator can lock or unlock accounts manually.
Resource limits:
Limits can be placed on the use of resources such as CPU time, logical input/output (I/O), and the number of sessions that a user opens.
Direct privileges:
Privileges are used to control the actions that a user can perform in a database.
Role privileges:
A user can be granted privileges indirectly through the use of roles.
Note: Refer to the lessons “Managing Privileges” and “Managing Roles” for information regarding role privileges.
This lesson covers defining a user with the appropriate authentication mechanism, limiting the use of space by the users in the system, and manually controlling account locking.
Database Schema
A schema is a named collection of objects such as tables, views, clusters, procedures, and packages that are associated with a particular user. When a database user is created, a corresponding schema with the same name is created for that user. A user can be associated only with a schema of the same name, and therefore username and schema are often used interchangeably.
The slide shows some of the objects that users can own in an Oracle database.
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.
Creating a New User:
Database Authentication
Creating a New User: Database Authentication
Syntax:
Use the following command to create a new user:
CREATE USER user
IDENTIFIED {BY password | EXTERNALLY}
[ DEFAULT TABLESPACE tablespace ]
[ TEMPORARY TABLESPACE tablespace ]
[ QUOTA {integer [K | M ] | UNLIMITED } ON tablespace
[ QUOTA {integer [K | M ] | UNLIMITED } ON tablespace ]...]
[ PASSWORD EXPIRE ]
[ ACCOUNT { LOCK | UNLOCK }]
[ PROFILE { profile | DEFAULT }]
where:
user: Is the name of the user
BY password: Specifies a user who is authenticated by the database and must supply a password while logging on
EXTERNALLY: Specifies that the user is authenticated by the operating system
GLOBALLY AS: Specifies that the user is authenticated globally
DEFAULT or TEMPORARY TABLESPACE: Identifies the default or temporary tablespace for the user
QUOTA: Defines the maximum space allowed for objects owned by the user in the tablespace (Quota can be defined as integer bytes or kilobytes and megabytes. The keyword UNLIMITED is used to specify that the objects owned by the user can use as much space as is available in the tablespace. By default, no user has any quota on any tablespace.)
PASSWORD EXPIRE: Forces the user to reset the password when the user logs on to the database using SQL*Plus (This option is valid only if the user is authenticated by the database.)
ACCOUNT LOCK/UNLOCK: Can be used to lock or unlock the user’s account explicitly (UNLOCK is the default.)
PROFILE: Is used to control resource usage and to specify the password control mechanism to be used for the user
Note: Refer to the “Managing Profiles” lesson for information about creating profiles.
A password authentication method is mandatory. If a password is specified, it is maintained by the Oracle server in the data dictionary. Password control mechanisms provided by the Oracle server are available when users are authenticated by the server.
After the password is set, when the user logs on using SQL*Plus , the user receives the following message at logon, and is prompted to enter a new password:
ERROR:
ORA-28001: the account has expired
Changing password for PETER
Old password:
New password:
Retype new password:
Password changed
Creating a New User: Database Authentication
Using Oracle Enterprise Manager to Create a New User
From the OEM Console:
Navigate to Databases > Security > Users.
Select Create from the right-mouse menu.
Enter information to create user.
Click Create.
Oracle Security Manager automatically grants the CONNECT role to any user who is created by using the tool.
Note: Refer to the “Managing Roles” lesson for information about the CONNECT role.
Creating a New User: Operating System Authentication
Operating system authentication:
Use the IDENTIFIED EXTERNALLY clause of the CREATE USER command to specify that a user must be authenticated by the operating system. This option is generally useful when the user logs on directly to the machine where the Oracle server is running.
Username for operating system authentication:
The OS_AUTHENT_PREFIX initialization parameter is used to specify the format of the usernames for operating system authentication. This value defaults to OPS$ to make it backward compatible with earlier releases of the Oracle server. To set the prefix to a NULL value, specify this initialization parameter as:
OS_AUTHENT_PREFIX = ““
The example in the slide shows how a user, aaron, is defined in the database. This specifies that the operating system user aaron will be allowed access to the database without having to undergo validation by the Oracle server. Thus, to use SQL*Plus to log on to the system, the UNIX user aaron must enter the following command from the operating system:
$ sqlplus /
Username for operating system authentication:
Note:
Using OS_AUTHENT_PREFIX=OPS$: Provides the flexibility of having a user authenticated by either the operating system or the Oracle server. In this case, the DBA can create the user by entering a command of the form:
CREATE USER ops$user
IDENTIFIED BY password ...
A user who logs on to the machine running the Oracle server need not supply a password. If the user connects from a remote client, he or she can connect by supplying the password.
Setting another initialization parameter, REMOTE_OS_AUTHENT=TRUE, specifies that a user can be authenticated by a remote operating system. The default value of FALSE indicates that a user can be authenticated only by the machine running the Oracle server. Use this parameter with care because there is a potential security problem.
If there are users in the database who are authenticated by the operating system, changing OS_AUTHENT_PREFIX may prevent these users from logging on to the database.
Changing User Quota on Tablespaces
Use the following command to modify tablespace quotas or to reassign tablespaces:
ALTER USER user
[ DEFAULT TABLESPACE tablespace]
[ TEMPORARY TABLESPACE tablespace]
[ QUOTA {integer [K | M] | UNLIMITED } ON tablespace
[ QUOTA {integer [K | M] | UNLIMITED } ON tablespace ] ...]
After a quota of 0 is assigned, the objects owned by the user remain in the revoked tablespace, but they cannot be allocated a new space. For example, if a table that is 10 MB exists in the USERS tablespace, and the USERS tablespace quota is altered to 0, then no more new extents can be allocated for that table.
Any unchanged options remain unchanged.
Note: Beware of UNLIMITED TABLESPACE privileges as ittakes priority over quota settings.
Changing User Quota on Tablespaces
Using Oracle Enterprise Manager Modify Tablespace Quota for a User
Navigate to Databases > Security > Users.
Select user.
Select View/Edit Details from the right-mouse menu.
Enter the quota size in the Quota page.
Click Apply.
Dropping a User
DROP USER user [CASCADE]
Guidelines:
The CASCADE option drops all objects in the schema before dropping the user. This must be specified if the schema contains any objects.
A user who is currently connected to the Oracle server cannot be dropped.
Dropping a User
Using Oracle Enterprise Manager to Drop a User
From the OEM Console:
Navigate to Databases > Security > Users.
Select user.
Select Remove from the right-mouse menu.
Select Yes to confirm drop.
Click Apply.
Obtaining User Information
Information about users can be obtained by querying the following views:
DBA_USERS
DBA_TS_QUOTAS
Obtaining User Information
Use the following query to find the default_tablespace for all users.
SQL> SELECT username, default_tablespace 2 FROM dba_users;
USERNAME DEFAULT_TABLESPACE
--------- ------------------
SYS SYSTEM
SYSTEM SYSTEM
OUTLN SYSTEM
DBSNMP SYSTEM
HR SAMPLE
OE SAMPLE
Summary
In this lesson, you should have learned how to:
Create users by specifying the appropriate password mechanism
Control usage of space by users
Practice 15 Overview
This practice covers the following topics:
Creating users
Displaying data dictionary information about users
Removing user quotas
Practice 15: Managing Users
1 Create user Bob with a password of CRUSADER. Make sure that any objects and
temporary segments created by Bob are not created in the system tablespace. Also,
ensure that Bob can log in and create objects up to one megabyte in size in the USERS
and INDX tablespaces. Use the lab15_01.sql script to grant Bob the ability to create sessions.
Hint: Assign Bob the default tablespace, USERS, and the temporary tablespace, TEMP.
2 Create a user Emi with a password of MARY. Make sure that any objects and sort segments created by Emi are not created in the system tablespace.
3 Display the information on Bob and Emi from the data dictionary.
Hint: This can be obtained by querying DBA_USERS.
4 From the data dictionary, display the information on the amount of space that Bob
can use in tablespaces.
Hint: This can be obtained by querying DBA_TS_QUOTAS.
5 a As user Bob, change his temporary tablespace. What happens?
b As user Bob, change his password to SAM.
6 As user SYSTEM, remove Bob’s quota on his default tablespace.
7 Remove Emi’s account from the database.
8 Bob has forgotten his password. Assign him a password of OLINK and require
that Bob change his password the next time he logs on.
Download Link:
Click Here To Download Lecture 6,7,8,9,10,11
0 Comments