|
"The secret of
success is to know something nobody else knows." -
Aristotle Onassis (1906-1975) |
Read
first then play the video:
DBA-VIDEO -Maintaining
user's account and profile
Maintaining user"s
account and profile
Introduction
As a DBA, you are
responsible for maintaining user accounts due to the growth of the
organization. Also, due to new users and the abuse of the database
resources, you have been assigned to create a profile to limit a group
of users from using the database resources. Your job"s
responsibilities dictate that you should at least be informed of the
following basic fundamental subjects:
Creating a user
Granting object privileges
to a user
Assigning a default
tablespace to a user
Assigning a temporary
tablespace to a user
Assigning a quota to a user
Assigning a profile to a
user
Expiring a user password
Creating a profile
Changing a password
Allocating resource
limitations to a profile
Locking a user account
Unlocking a user account
Using the DBA_USERS view
Using the ALL_OBJECTS view
Using the DEFAULT profile
Activating the resource
limit system parameter
Dropping a user
Dropping a profile
Commands:
CREATE USER
CONNECT system/manager AS
SYSDBA
ALTER USER ACCOUNT LOCK
ALTER USER ACCOUNT UNLOCK
CREATE PROFILE LIMIT
ALTER USER PROFILE
ALTER SYSTEM SET
resource_limit=TRUE
DROP USER CASCADE
DROP PROFILE CASCADE
In this exercise you will
learn how to create a user account with its own default tablespace and
more. You will learn how to create a user profile and how to allocate
resource limitations on each account. Also, you will learn how to lock
and unlock a user account.
Now, let's connect as the system/manager user.
SQL> CONNECT
system/manager
View users information
Query the DBA_USERS view to display a list of all the usernames of
that start with the letter D.
SQL> SELECT username, password,
account_status,
default_tablespace, temporary_tablespace,
profile
FROM dba_users
WHERE username like 'D%'
/
It looks like we do not have a username by the name of
DEVELOPER.
Create a user
Let's create a username DEVELOPER with the following options:
-- Password is DEVELOPER,
-- The default tablespace is ISELF_DATA,
-- The temporary tablespace is TEMP,
-- Allow to use 10k of space on the ISELF_DATA tablespace,
-- No permission to use the SYSTEM tabespace,
-- Use the default profile,
-- Force the user to enter the new password, and
-- Account cannot be locked.
SQL> CREATE USER developer
IDENTIFIED BY developer
DEFAULT TABLESPACE iself_data
TEMPORARY TABLESPACE temp
QUOTA 10K ON iself_data
QUOTA 0K ON SYSTEM
PROFILE default
PASSWORD EXPIRE
ACCOUNT UNLOCK
/
Now, view the list of all
the users, whom their usernames start with a letter D.
SQL> SELECT username, password,
account_status,
default_tablespace, temporary_tablespace, profile
FROM dba_users
WHERE username like 'D%'
/
Grant roles to a user
Now, grant to DEVELOPER
user the CONNECT and RESOURCE roles.
SQL> GRANT CONNECT, RESOURCE TO DEVELOPER
/
Connect to SQLPlus as the DEVELOPER user. Notice that you have to
change the your password. Change it to mypass.
SQL> CONNECT DEVELOPER/DEVELOPER
Connect to SQLPlus as the DEVELOPER user with the new password and
query a simple SQL statement.
SQL> CONNECT DEVELOPER/mypass
SQL> SELECT COUNT(*)
FROM all_objects
/
Lock a user
Connect as the
system/manager user and lock the DEVELOPER user.
SQL> CONNECT system/manager AS SYSDBA
SQL> ALTER USER DEVELOPER ACCOUNT LOCK
/
Verify lock
Now, connect to SQLPlus as the DEVELOPER user. Notice that the
DEVELOPER account was locked.
SQL> CONNECT DEVELOPER/mypass
Unlock a user
Connect as the
system/manager user and unlock the DEVELOPER user.
SQL> CONNECT system/manager
SQL> ALTER USER DEVELOPER ACCOUNT UNLOCK
/
Now, the DEVELOPER user can login to SQL*PLUS.
Create a profile
Since the DEFAULT profile gives users unlimited use of all the
resources definable in the database, create a user profile that has
some host system usage restrictions on it.
The following are restrictions, needed for the DEVELOPER users.
-- The user should not open more that one session,
-- The maximum allowed CPU time in a session is to be 10 seconds,
-- The user can be connected for total amount of 8 hours, and
-- The user can issue no commands for 1 hour.
SQL> CREATE PROFILE developer LIMIT
SESSIONS_PER_USER 1
CPU_PER_SESSION 1000
CONNECT_TIME 4800
IDLE_TIME 60
/
View profiles
View the developer profile
with their parameter values.
SQL> SELECT *
FROM dba_profiles
WHERE limit <> 'DEFAULT' AND
profile = 'DEVELOPER'
ORDER BY profile, limit
/
Notice that the developer profile was created.
Assign a profile to a user
Assign the DEVELOPER
profile to the DEVELOPER user.
SQL> ALTER USER developer PROFILE
developer
/
Activate resource limit
Activate the resource limit
system parameter to true.
SQL> ALTER SYSTEM SET resource_limit=TRUE
/
Now, connect as the DEVELOPER user. Then, go the other session and try
to connect to SQLPlus as the DEVELOPER user. Remember that since the
DEVELOPER profile is active, we are restricted to one and only one
session to be opened.
SQL> CONNECT developer/mypass
Now, connect as the SYSTEM/MANAGER user, and drop the DEVELOPER user
and the DEVELOPER profile.
SQL> CONNECT system/manager AS SYSDBA
SQL> DROP USER developer
CASCADE
/
SQL> DROP PROFILE developer
CASCADE
/
|
"There are two
ways of constructing a software design; one way is to make it so
simple that there are obviously no deficiencies, and the other
way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult." - C.
A. R. Hoare |
Questions:
Q: How do you create a user
account?
Q: How do you create a user
profile?
Q: How do you grant an
object privilege to a user?
Q: How do you assign a
default tablespace to a user?
Q: How do you assign a
temporary tablespace to a user?
Q: What is a quota in the
Oracle database?
Q: How do you assign a
quota to a user?
Q: How do you assign a
profile to a user?
Q: How does a user account
expire?
Q: How do you create an
Oracle profile?
Q: How do you change an
Oracle user"s password?
Q: How do you allocate
resource limitation to a profile?
Q: How do you lock a user?
Q: How do you unlock a
user?
Q: What is the DEFAULT
profile?
Q: Describe the DBA_USERS
and ALL_OBJECTS views.
Q: How do you activate the
resource limit system parameter?
Q: How do you drop a user?
Q: Can you drop a user
containing Oracle objects?
Q: How do you maintain a
profile?
Q: How do you maintain a
user assigned tablespaces?
Q: What do the following
SQL statement do?
SQL> SELECT username,
password, account_status,
default_tablespace, temporary_tablespace,
profile
FROM dba_users
WHERE username like 'D%'
/
SQL> CREATE USER developer
IDENTIFIED BY developer
DEFAULT TABLESPACE iself_data
TEMPORARY TABLESPACE temp
QUOTA 10K ON iself_data
QUOTA 0K ON SYSTEM
PROFILE default
PASSWORD EXPIRE
ACCOUNT UNLOCK
/
SQL> ALTER USER DEVELOPER ACCOUNT LOCK
/
SQL> ALTER USER DEVELOPER ACCOUNT UNLOCK
/
SQL> CREATE PROFILE developer LIMIT
SESSIONS_PER_USER 1
CPU_PER_SESSION 1000
CONNECT_TIME 4800
IDLE_TIME 60
/
SQL> ALTER SYSTEM SET resource_limit=TRUE
/
SQL> DROP USER developer
CASCADE
/
|