User Management

Oracle User Management
with reference to 11g documentation
Creating a New User Account
You create a database user with the CREATE USER statement. To create a user, you must have the CREATE USER system privilege.
Creating a User Account with the CREATE SESSION Privilege:

· CREATE USER jward
· IDENTIFIED BY password
· DEFAULT TABLESPACE data_ts
· QUOTA 100M ON test_ts
· QUOTA 500K ON data_ts
· TEMPORARY TABLESPACE temp_ts
· PROFILE clerk;
· GRANT CREATE SESSION TO jward;

System Privileges:
--CREATE SESSION system privileges : A newly created user cannot connect to the database until you grant the user the .
-- SELECT ANY DICTIONARY privilege : If the user must access Oracle Enterprise Manager, you should also grant the user the.

Creating database user names with case senstivity:
CREATE USER "jward" IDENTIFIED BY password;
So, when you query the ALL_USERS data dictionary view, you will find that the user account is stored using the case that you used to create it.
Find Existing Users Tablespace quotas
USER_TS_QUOTAS

Restricting the Quota Limits for User Objects in a Tablespace
ALTER USER scott QUOTA 0 ON system;

Granting Users the UNLIMITED TABLESPACE System Privilege
GRANT UNLIMITED TABLESPACE TO new_user;

Specifying a Profile for the User
GRANT USER jward clerk_role;

ALTER USER jward DEFAULT ROLE clerk_role;

Using the ALTER USER Statement to Alter a User Account
ALTER USER avyrros
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE data_ts
TEMPORARY TABLESPACE temp_ts
QUOTA 100M ON data_ts
QUOTA 0 ON test_ts
PROFILE clerk;

Changing Non-SYS User Passwords
Most users can change their own passwords with the PASSWORD statement, as follows:
PASSWORD andy
Changing password for andy
New password: password
Retype new password: password

Changing the SYS User Password
If you must change the SYS user password, then you should use the ORAPWD command line utility to create a new password file that contains the password that you want to use. Do not use the ALTER USER statement or the PASSWORD command to change the SYS user password. Note the following:

Creating user Profile in Oracle 11g
When you create a new user account in Oracle 11g, the user is by default assigned the “DEFAULT” profile. This isn’t something that changed from previous versions. However, the definition of the “DEFAULT” profile changed in Oracle 11g.
To view the definition of the “DEFAULT” profile, you can execute the following query (with user SYS as SYSDBA):
select * from dba_profiles where profile=’DEFAULT’ and resource_name in (‘PASSWORD_LIFE_TIME’,'PASSWORD_GRACE_TIME’)
Out put of 11g:
PROFILE,RESOURCE_NAME,RESOURCE_TYPE,LIMIT
DEFAULT,PASSWORD_LIFE_TIME,PASSWORD,180
DEFAULT,PASSWORD_GRACE_TIME,PASSWORD,7One way to avoid the expiration of passwords after 180 days would be to change the definition of the DEFAULT profile, but I wouldn’t touch this. I would keep the DEFAULT profile for user accounts that require regular password changes, like developer accounts. For the accounts that need to keep their passwords unexpired, you could create a new user profile. Here is an example;
CREATE PROFILE "UNLIMITED_PWD_EXPIRATION" LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME DEFAULT
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_LOCK_TIME 1
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_VERIFY_FUNCTION
VERIFY_FUNCTION_11G
Besides having an unlimited password expiration, this user profile also enables the password verification function “VERIFY_FUNCTION_11G”, which adds some required complexity to the user passwords. This function can be installed by running the utlpwdmg.sql script in $ORACLE_HOME/rdbms/admin:

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 18 13:03:05 2012 Copyright (c) 1982,
2011, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL>@?/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
Function created.
SQL> exit

Unlike older releases, Oracle 11g sets password expiry by default. That's really annoying
ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED;
And also let's turn off the default auditing:
NOAUDIT ALL;
DELETE FROM SYS.AUD$;

You can use the below views to find role related informations,

DBA_ROLE_PRIVS - Roles granted to users and roles
ROLE_ROLE_PRIVS - Roles which are granted to roles
ROLE_SYS_PRIVS - System privileges granted to roles
ROLE_TAB_PRIVS - Table privileges granted to roles

SQL> select GRANTEE, GRANTED_ROLE from DBA_ROLE_PRIVS where GRANTEE='HXSQS0';

GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
HXSQS0 ALB_USER
HXSQS0 PSSELECT
HXSQS0 PSCONVERT