Oracle database user privileges
List Oracle Role Privileges
select
grantee role_name,
privilege privilege,
admin_option admin_grantable
from
dba_sys_privs
where
grantee='<ROLE NAME>'
Lookup sysdba users
SQL> SELECT * FROM v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------
SYS TRUE TRUE
TESTER TRUE TRUE
SQL>
Lookup user privileges and permissions
SQL> SELECT * FROM dba_role_privs WHERE grantee='ETRIALS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
ETRIALS CONNECT NO YES
ETRIALS RESOURCE NO YES
SQL> SELECT * FROM dba_sys_privs WHERE grantee='ETRIALS';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
ETRIALS CREATE VIEW NO
ETRIALS UNLIMITED TABLESPACE NO
SQL>
to see users quotas:
SQL> SELECT tablespace_name, username, bytes FROM DBA_TS_QUOTAS;
TABLESPACE USERNAME BYTES
---------- --------- ----------
SYSAUX DMSYS 196608
SYSAUX OLAPSYS 16252928
SYSAUX WK_TEST 12582912
SYSAUX SYSMAN 78577664
RMAN_TBSP RMAN 8585216
SQL>
Oracle User Temporary Password Change
SQL> select password from dba_users where username='SCOTT';
PASSWORD
-----------------------------
F894844C34402B67
SQL> alter user scott identified by lion;
User altered.
SQL> connect scott/lion
Connected.
REM Do whatever you like...
SQL> connect system/manager
Connected.
SQL> alter user scott identified by values 'F894844C34402B67';
User altered.
SQL> connect scott/tiger
Connected.