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';
------------------------------------------------------------------
------------------------------------------------------
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>;
