User Management

Privileges assigned to a role

select * from dba_sys_privs where grantee='DBAROLE1'

Privileges And Roles Assigned To User

Select a.grantee User_name, a.granted_role role, b.privilege from DBA_ROLE_PRIVS a, DBA_SYS_PRIVS b where a.granted_role=b.grantee and a.grantee = 'USER1';

To Know User Status

select username,ACCOUNT_STATUS from dba_users where username in ('USER1','USER2');

To drop user

DROP  USER  USER1 CASCADE;

select object_name from dba_objects where owner='USER1';

Get DDL,Roles,Privileges For User

select dbms_metadata.get_ddl('USER','USER1') ddl from dual;

select dbms_metadata.get_granted_ddl('ROLE_GRANT','USER1') ddl from dual;

select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','USER1') ddl from dual;

select dbms_metadata.get_granted_ddl('OBJECT_GRANT','USER1') ddl from dual;

Get Privileges granted to role

select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTEE ='USER1';

Get previliges granted to user

select PRIVILEGE from dba_sys_privs where GRANTEE='USER1';

Change Password For User

alter user USER1 identified by USER1_prod account unlock;