iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 21

"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
/