Chapter 10 : MANAGING USERS
CHAPTER NO 10
MANAGING USERS
In this lesson we cover
- Create new database users
- Alter database users
- Drop database users
- Monitoring information about existing users
- Obtain user information
SECURITY AND DOMAIN:
The database administrator defines the names of the users who are allowed to access the database.
A security domain defines the settings that apply to the users.
Security domain include
1) Authentication mechanism
2) Tablespace quota
3) Default tablespace
4) Temporary tablespace
5) Account locking
6) Resource limit
7) Direct privilege
8) Role privilege
AUTHENTICATION MECHANISM
A user can access the database can be authenticated by on of the following
A) Data dictionary
B) Operating system
C) Network
Means of authentication can be defined at the time of user creation or
Can be defined later by alter command
In this lesson we study
Data dictionary
&
Operating system authentication.
TABLESPACE QUOTA
Tablespace quota controls the amount of physical storage that is allowed to the user in the tablespace in the database.
DEFAULT TABLESPACE
Default tablespace are the location where segments that are created by user are stored.
If user does not explicitly define the tablespace at the time of segments are created.
TEMPORARY TABLESPACE
Temporary tablespace are the location where extent ae allocated by the oracle server if the user performs the operation like sorting the data.
ACCOUNT LOCKING
User account can be lock to prevent the user when user logging on the database.
This can be done automatically
Or the DBA can lock / unlock the a/c manually.
RESOURCE LIMIT
Limits can be places on the user of resources such as
CPU time
Logical I/O
No. of session that a user open.
DIRECT PRIVILEGE
Privilege are used to control the action that a user can perform in a database
ROLE PRIVILEGE
A user can be granted privileges indirectly through the use of roles.
DATABASE SCHEMA
A database schema is a named collection of object such as (tables, views, clusters, procedures and packages) that are associated with the 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 with one schema only.
Therefore user and schema are often used interchangeably.
When we create any user we must consider the following point that called checklist for creating user.
1- Identify the tablespace where user stores objects.
2- Decide tablespace quota for each tablespace
3- Assign a default tablespace and temporary tablespace
4- Create a user
5- Grant privileges and assign role to the user.
PRACTICAL #
Creating a new user by database authentication:
Now we create user
Create user abc1
Identified by abc1
Default tablespace example
Temporary tablespace temp
Quota 15m on example
Quota 10m on users
Password expire;
For finding the names of tablespaces that tablespace can exist or not use this query
Select tablespace_name from dba_tablespaces;
User : Name of the user
By password : Means database authentication mechanism
Use password while logging on database.
By externally: Means operating system authentication
Therefore password is not required.
By globally: Means user can access any application.
Default and temporary tablespaces: Identify the tablespaces for the user
If not mention then use system tablespace.
Quota : Maximum space allowed to the user for objects in any tablespace.
Quota can be define integer byte/ kilo bytes (KB) and megabytes (MB)
Unlimited: This keyword use for user can use as much as space available in any tbalespace.
Password expire: This option force the user to change password when the user log on the database by SQL
This option available only for database authentication mechanism.
Account lock/unlock: This option is used for user account can be lock / unlock explicitly.
Unlock is default.
Profile: Profile is used to control resource usage and password controlling for the user.
Createing by new user by : operating system authentication :
Study later
CHANGING USER QUOTA ON TABLESAPACES
We can modify user table quotas in following situations
1- Table owned by user grow continuously
2- An application is enhanced and require more table or indexes.
3- Objects are reorganized and places on different tablespaces.
PRACTICAL #
Alter user abc1
Quota 0 on users;
Important note: after 0 qouta assign to user then no new extent can be allocated for the objects of user.
DROPING A USER
A user can be drop by using the following command
PRACTICAL #
Drop user abc1;
If we want to remove the user and its associated objects then we use cascade option
PRACTICAL #
Drop user abc1 cascade;
TO GET INFORMATION ABOUT THE USERS OF ORACLE USE THIS QUERY
Sql> desc dba_users
name null? Type
----------------------------------------------------- -------- ---------------
username not null varchar2(30)
user_id not null number
password varchar2(30)
account_status not null varchar2(32)
lock_date date
expiry_date date
default_tablespace not null varchar2(30)
temporary_tablespace not null varchar2(30)
created not null date
profile not null varchar2(30)
initial_rsrc_consumer_group varchar2(30)
external_name varchar2(4000)
IF WE GET THE INFORMATION ABOUT TABLESPACE QUOTA OF USERS THEN
Desc dba_ts_quotas;
name null? Type
----------------------------------------------------------------------------------- -------- ------
tablespace_name not null varchar2(30)
username not null varchar2(30)
bytes number
max_bytes number
blocks not null number
max_blocks number
IF ANY USER ACCOUNT LOCK
then we use
PRACTICAL #
Sql> alter user abc account unlock
IF ANY USER ACCOUNT LOCK AND EXPIRE
then we use
PRACTICAL #
Sql> alter user hr identified by hr account unlock;
User altered.
IF WE WANT TO LOCK THE ACCOUNT OF ANY USER THEN WE USE
PRACTICAL #
Alter user abc account lock ;
User altered.
No comments:
Post a Comment