everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 21

Next >>


 

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 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> 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 oracle_data
TEMPORARY TABLESPACE temp
QUOTA 10K ON oracle_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
/

 

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.