OracleDB‎ > ‎

User Privileges

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.