User Creation,profiles and Roles

User Creation:

How to create a User,assign tablespace and Quota 

create user slm identified by slm
default tablespace tech
temporary tablespace tmp
quota unlimited on tech
quota 100m on testing
password expire
account lock


How to Check created users and default Users 

select username,default_tablespace from dba_users;

How to check which tablespace assigned in User quota 

select tablespace_name,USERNAME,BYTES,MAX_BYTES from
dba_ts_quotas where USERNAME='SLM';

Assigning the quota to the user on other tablespace 

SQL> alter user slm quota 100m on dbascf;
User altered.


select tablespace_name,USERNAME,BYTES,MAX_BYTES from
dba_ts_quotas where USERNAME='SLM';



TO UNLOCK THE USER ACCOUNT 

SQL>alter user slm account unlock;
User altered

Check:
SQL> select username, default_tablespace,account_status from
dba_users;

TO FORCE USER TO CHANGE THE PASSWORD 

SQL> alter user srk password expire;
User altered.

Check:
SQL> select username,default_tablespace,account_status from
dba_users;


ROLES:

GRANTING ROLES AND PRIVILEGES TO USERS
create user s1 identified by s1;
User created.
SQL> grant create session,create table,create sequence to s1;
Grant succeeded



Creating profile:

SQL> create profile prof limit
failed_login_attempts 3
Password_lock_time 1
password_life_time 7
sessions_per_user 5
idle_time 1
connect_time 600
Profile created.

To enforce kernel/resource parameters the following parameter must be set


SQL> alter system set resource_limit=true scope=both;

Check

SQL> select profile from dba_profiles;


Assigning profile:

SQL>alter user slm profile prof;

User altered.

SQL> select * from dba_profiles where profile='PROF';