DBA Fundamentals

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:

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.

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 ORACLE_DATA,
-- The temporary tablespace is TEMP,
-- Allow to use 10k of space on the ORACLE_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 oracle_data
TEMPORARY TABLESPACE temp
QUOTA 10K ON oracle_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
/

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> DROP USER developer
CASCADE
/