Download Oracle Database User Management: Creating, Altering, Dropping, and Monitoring Users and more Study notes Business Administration in PDF only on Docsity!
Page 1
ACCESS CONTROL
Today we will cover creating new database users, altering and dropping existing users, monitoring information about existing users and terminating user sessions.
You can control access to an Oracle database by creating, altering, dropping and monitoring users.
Controlling Access to and Use of the Database Create valid usernames and passwords Authorize a user to connect to the database.
Each Oracle database has a list of valid database users, identified by usernames. A username is Required to access the database Supplied by every database application. Defined in the database.
When a user is created, a corresponding schema of the same name is created for that user.
Oracle database Authentication A password is assigned with each username and can be changed later by the user. The Oracle Server stores usernames and encrypted passwords. User authentication is validated by the Oracle Server when a user attempts to connect to the database. Users can be authenticated by the operating system.
Authentication Specification Description Default tablespace Specifies where objects are built if no tablespace is referenced in the CREATE TABLE or CREATE INDEX command. Temporary Tablespace Provides storage for SQL statements that require disk space to sort or summarize data. Tablespace quotas Determine the maximum space the user may consume for each tablespace System resource limits Include the amount of CPU time, the number of logical reads, the number of concurrent sessions per user, and the idle time for a session, specified through profiles.
Creating a User
CREATE USER SQL command :
Page 2
Syntax CREATE USER user IDENTIFIED BY password externally
DEFAULT TABLESPACE tablespace TEMPORARY TABLESPACE tablespace PROFILE profile
QUOTA integer ON tablespace UNLIMITED
where user identifies the name of the user to be created.
BY password specifies the password to log in
EXTERNALLY verifies user access through the operating system
DEFAULT TABLESPACE identifies the default tablespace for user objects
TEMPRORARY identifies the temporary tablespace for temporary TABLESPACE segments
QUOTA allows the user allocate space in the tablespace
Integer specifies quota in K or M
UNLIMITED allows the user to allocate space within the tablespace without boundaries.
PROFILE assigns the profile named to the user.
Example:
CREATE USER jack IDENTIFIED BY jill DEFAULT TABLESPACE student TEMPORARY TABLESPACE temp QOUTA 15M ON student ;
Now create a user named tom with a password of tom. Tom’s temporary tablespace should be TEMP, but he should not be assigned a default tablespace at this time. Give tom unlimited tablespace quota on the SYSTEM tablespace.
Note : 2 usernames are reserved on an Oracle database : sys and system By default, a user does not have access to any tablespace in the database. Assign quotas to a user to prevent the objects of the user from excessive consumption in a tablespace.
Page 4
Example : Alter the user joe. Change the password to hill. Change the default tablespace to staff, with a quota of 10MB
ALTER USER jack IDENTIFIED BY hill DEFAULT TABLESPACE staff QOUTA 10M ON staff ;
Now, change tom’s default tablespace to staff with a quota of 10M.
Note : Only options specified within the ALTER USER SQL command are changed; all previously given resources remain.
Dropping an Existing User
Syntax:
DROP USER user CASCADE Where
user user to drop CASCADE drops all objects in the user’s schema before dropping the user. This option must be specified to drop any user whose schema contains any objects.
Example : Drop the user joe and select the option that will drop all of joe’s objects.
DROP USER joe CASCADE;
Note : A user that is currently connected to the database cannot be dropped.
Monitoring Users
You can view user information in the data dictionary, which stores information about every username. The data dictionary includes information on: All users in the database The default tablespace for the tables and indexes of each user The tablespace used for temporary segments Space quotas
Helpful Data Dictionary Views : ALL_USERS USER_USERS DBA_TS_QUOTAS USER_TS_QUOTAS
Display information about the current user with the USER_USERS data dictionary view e.g.
SELECT *
FROM user_users;
Page 5
Display information about all users of the database with the DBA_USERS data dictionary view e.g.
SELECT *
FROM dba_users;
Display tablespace quotas for the current user with the USER_TS_QUOTAS data dictionary view e.g.
SELECT *
FROM user_ts_quotas
Note : A value of –1 represents unlimited quota.
Display tablespace quotas for all users with the DBA_TS_QUOTAS data dictionary view e.g.
SELECT * FROM dba_ts_quotas
Killing a user session
When necessary, terminate a user’s session while that user is logged on to the database.
Killing a user session Prevents a user from issuing further database calls Frees locked resources Will display a message to the user Requires the ALTER SYSTEM privilege.
Kill a user session when A user is holding resources urgently needed by another user The DBA needs to shutdown the database – use the IMMEDIATE option during shutdown as an alternative to killing a user session.
Syntax :
ALTER SYSTEM
KILL SESSION ‘integer1, integer2’
Where
KILL SESSION terminates a session integer1 specifies the user SESSION ID integer2 specifies the user serial number
The ALTER SYSTEM KILL SESSION command performs a number of tasks upon execution. Rolls back the user’s current transaction Releases all currently held table or row locks Frees all resources currently reserved by that user
If the user session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, the Oracle Server waits for this activity to complete.
