Showing posts with label Profile. Show all posts
Showing posts with label Profile. Show all posts

Saturday, December 5, 2009

Practice with User Profiles and User Roles in Oracle


HANDS-ON #1: User profiles




Your company has hired several new people for a new department project in your regions. This department will have some different requirements from regular works, and the DBA wants to ensure that the new hires will not be getting stepped on by current users in the database and vise versa. To help insulate the new hires, it has been decided to create a specific profile for them. The profile name will be NEWREG1 and it will need to have the following properties:
  • After three login attempts, the account should be locked.
  • The password should expire after 30 days.
  • The same password should not be reused again for at 31 days.
  • The account should have a grace period of five days to change an expired password.
  • Each user can have 3 concurrent sessions open.
Write the SQL commands that would implement this profile. Execute your script to create the new profile and capture the results in a spool file. Next, write the query that would list the profile, the profile resource name, and the limit value for the new profile. Execute this query in your database and capture the results in a spool file.
HANDS-ON #2: User roles


You need to create a new ROLE named JRDBA1 that can be used for temporary and assistant DBA personnel. You want the new role to contain all of the current roles that the SYSTEM user has with ADMIN OPTION (when it pertains to the current SYSTEM user setting). You also want the new role to be password-protected with a password of MJOAG2T. Once the role is created, write the statements to assign the role to the user ASSOCDBA1, but do not make it the default role. (HINT: To do this you will need to create the user ASSOCDBA1 first. Be sure you define both a default and a temporary tablespace other than SYSTEM when doing this).
Execute your script to create the new user and ROLE and the assignment of the ROLE to the user, and capture the results in a spool file.
Posted by at No comments:
Labels: , ,

Saturday, July 12, 2008

ORA-28003, ORA-20002 while changing password

Error Description:
----------------------------
While changing user password ORA-28003 and ORA-20002 raised.
SQL> alter user arju identified by a;
alter user arju identified by a
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 4

Cause of The problem:
----------------------------------
There is a password verify function assigned on the profile of the user. The verify function does not allow to to change password to a and it specifies password length less than 4.

Solution of The Problem:
---------------------------------------
Way 1:
-----------
Change the password as it satisfy the password verfify function. Suppose if function impose restriction password to length at least 4 characters, must have one number and one alphanumeric character and one special character then change the password as following,

SQL> alter user arju identified by "b!12";
User altered.

Way 2:
----------------
If you don't care about password verification function then you may disable the function.
To do it first look for the user profile for which you get the error.
SQL> select profile from dba_users where username='ARJU';
PROFILE
------------------------------
DEFAULT

Then look for the assigned function of the intended profile.
SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ----------------------------------------
PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION

Either assigned another function of disable the verification function. You can disable it by,
SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION NULL;
Profile altered.

Now change the password.
SQL> alter user arju identified by a;
User altered.

Posted by at No comments:
Labels: , ,

Saturday, May 24, 2008

ORA-28000: the account is locked

Error Description:
-------------------------
Whenever you try connect to connect a user it failed with error message.
SQL> CONN INDIA/USA
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

And immediately the current user is disconnected from oracle.

Cause of The Problem:
-------------------------------
The user account is locked. This may be explicitly issued by dba user Like,
SQL> ALTER USER INDIA ACCOUNT UNLOCK; or it may be locked internally based on the profile resource limit. In order to know more about profile and resource limit have a look at,
User Resource Limit
Profile in Oracle

For example if I set FAILED_LOGON_ATTEMPTS of the assigned profile to a user set as 10 (which is default in 10.2)then after failed logon attempt 10 times the user account will be automatically locked.

Solution of The problem:
-------------------------------
Try to unlock the user account as a dba user by ALTER USER username ACCOUNT UNLOCK;
SQL> CONN INDIA/USA
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

SQL> CONN ARJU/A
Connected.

SQL> ALTER USER INDIA ACCOUNT UNLOCK;
User altered.

SQL> CONN INDIA/T
Connected.

There may be the reason that user is locked based on imposed resource limit. For example from application wrong password is set and for that the account is locked. You then have to know the assigned profile of the user by,
SQL> SELECT PROFILE FROM DBA_USERS WHERE USERNAME=
2 'INDIA';

PROFILE
----------
DEFAULT

SQL> SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT';

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED

16 rows selected.


Here FAILED_LOGIN_ATTEMPTS resource is set to 10. You can make it unlimited by

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
Profile altered.

And then unlock the account.
SQL> ALTER USER INDIA ACCOUNT UNLOCK;

Related Documents
---------------------------
User Resource Limit in Oracle
Profile in Oracle
Posted by at No comments:
Labels: , ,

Sunday, April 20, 2008

User Resource Limits in Oracle

In oracle you can set limits on the amount of various system resources available to each user. By doing so, you can prevent the uncontrolled consumption of valuable system resources such as CPU time.

Types of System Resources and Limits
---------------------------------------------
Oracle can limit the use of several types of system resources. In general, you can control each of these resources at the session level, the call level, or both.

A)Session Level
-----------------

•If a user exceeds a session-level resource limit, then Oracle terminates (rolls back) the current statement and returns a message indicating that the session limit has been reached.

•All previous statements in the current transaction are intact, and the only operations the user can perform are COMMIT, ROLLBACK, or disconnect.

•All other operations produce an error. Even after the transaction is committed or rolled back, the user can accomplish no more work during the current session.

Session level resource limitation is set by CPU_PER_SESSION, LOGICAL_READS_PER_CALL

B)Call Level
------------------

•If a user exceeds a call-level resource limit, then Oracle halts the processing of the statement, rolls back the statement, and returns an error.

•Unlike session level, all previous statements of the current transaction remain intact, and the user's session remains connected.
Call level resource limitation is set by CPU_PER_CALL, LOGICAL_READS_PER_CALL

Both session level and call level limitation can be set by

ALTER PROFILE profile_name LIMIT resource_name value;

Other Resources:
--------------

•You can limit the number of concurrent sessions for each user. Each user can create only up to a predefined number of concurrent sessions. This is set by the resource name SESSIONS_PER_USER.

•You can limit the idle time for a session. If the time between Oracle calls for a session reaches the idle time limit, then the current transaction is rolled back, the session is aborted, and the resources of the session are returned to the system. The next call receives an error that indicates the user is no longer connected to the instance. This limit is set by the resource name IDLE_TIME.

•You can limit the elapsed connect time for each session. If a session's duration exceeds the elapsed time limit, then the current transaction is rolled back, the session is dropped, and the resources of the session are returned to the system. This limit is set as a number of elapsed minutes.For performance reason oracle check elapsed idle time or elapsed connection time after every few minutes.This elapsed connect time limitation is maintained by resource name CONNECT_TIME.


•You can limit the amount of private SGA spacefor a session. This limit is only important in systems that use the shared server configuration. Otherwise, private SQL areas are located in the PGA. This limitation is maintained by resource name PRIVATE_SGA.

All limitations can be set by assign a profile to the specified user and then altering the resource. ALTER PROFILE profile_name LIMIT resource_name value;

Related Documents:
Profile in Oracle
Posted by at No comments:
Labels: ,

Profile in Oracle

In oracle you can set limits on the amount of various system resources available to each user. By doing so, you can prevent the uncontrolled consumption of valuable system resources such as CPU time.

Resource limits and password management of a user are assigned by the profile. Every user (even SYS) is assigned to a profile: by default, to the profile called DEFAULT. Based on the profile settings it is determined how resource and/or password management will be enforced to a user.

To use profiles, first categorize the related types of users in a database. Just as roles are used to manage the privileges of related users, profiles are used to manage the resource limits of related users.

How I can see in which profile a user is assigned?
-------------------------------------------------------
SQL> select profile from dba_users where username='ARJU';
PROFILE
------------------------------
DEFAULT

To see the default profile settings issue the query,

select * from dba_profiles where profile ='DEFAULT';

From the output one row is ,
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ---------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

So we see here FAILED_LOGIN_ATTEMPTS in DEFAULT profile is set to 10 which means after failed 10 consecutive connect requests with an incorrect password, the account will be locked.

To change the settings use
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 100;

Resource_type in DBA_PROFILES table can have two values- KERNEL or PASSWORD.

The kernel limitations are to do with how much processing capacity a session is allowed to consume.
The password resource type limits the settings that are relevant to security

There are other resources like PASSWORD_LIFE_TIME,PASSWORD_REUSE_TIME etc.

Related Documents:
-------------------
User Resource Limit in Oracle
Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)