USER MANAGEMENT IN ORACLE DATABASE

 Check Current User Permission
------------------------------------------------------
select * from USER_ROLE_PRIVS where USERNAME=USER;
select * from USER_TAB_PRIVS where Grantee = USER;
select * from USER_SYS_PRIVS where USERNAME = USER;
----------------------------------------------------------------
To Check the roles granted to a user:
--------------------------------------
select * from dba_role_privs where grantee = 'username';
select * from dba_tab_privs where grantee = 'username';
select * from dba_sys_privs where grantee = 'username';
------------------------------------------------------------------

To check status of user on database:--

select 

(select host_name from v$instance) "Host_Name", 

(select to_char(sysdate,'DD-MM-YY HH:MM:SS') from dual) "Date",

username,

account_status 

from dba_users 

where 

username='&username';

output: 

Host_Name    Date    username    account_status 

----------------------------------------------------------------

example        08-May-23  scott      open 

To Check Profile for a user :-

--------------------------------------------

select username,profile from dba_users where username='&username';

select profile,resource_name,limit from dba_profiles where profile='&profile';

To Change User/schema password :-

alter user username account unlocked;

alter user username identified by "password";

To Change Profile Resource limit:-

ALTER PROFILE sample_profile LIMIT <resource_name>  <resource_limit>;

To Create Profile for User:-

This one is ideal but not preferable to use in production environment for security reason just we can use this for testing purpose on lab environment.

SQL>

create profile <profile_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 unlimited

password_life_time unlimited

password_reuse_time unlimited

password_reuse_max unlimited

password_verify_function null

password_lock_time unlimited

password_grace_time unlimited;

If We Want To Change Profile For User:-

alter user <username> profile <profile_name>;