DBSecWorx: DBMS

Oracle Proxy User Accounts All Versions

Security Advisory

If you build a house on a bad foundation you should not be surprised when walls start cracking and the house collapses. Building an Oracle Database based application with a "normal" user and expecting it to be secure is a guarantee that any attacker with half a skill set can take it down at will.

Read, with care, the demo posted in the "How Oracle Works" section to see one way to create the foundations for a secure environment. And, what is demonstrated in the demo code can be back ported to any application without breaking it. You may have an issue with the application vendor's support policies ... but if you do that says more about their lack of interest in securing their product and their level of caring for their customers than it does Proxy User accounts.

With the information on this page you can achieve the following goals:

Secure an existing application by converting the application user into a user that has no privileges: Not even the privilege to connect to the database
Make it possible to change application passwords at will with zero downtime
Limit the number of accounts with escalated privileges to as small a number as you wish
Not pay a single additional dollar and purchase, subscribe, or license anything from any vendor

Sound too good to be true? Most of this technology has been available in Oracle for more than a decade. One part of the demo uses, but does not require, a new technology introduced with Oracle 18c.

Note: There is a wealth of syntaxes available for creating, altering, and dropping users. This website is focused on security and primarily security through configuration rather than trying to spend vast fortunes to make some account executive happy with his or her commissions and bonuses. So, if you want to look into all of the available syntaxes visit MorgansLibrary.org or the online docs at https://docs.oracle.com. What you will find here will be what you need to lock down your data and your databases and vastly reduce their attack surface.

Recommended Security Rules

NEVER

Use the standard Oracle CREATE USER syntax to create a user that connects to the database whether that user is human or mechanical, whether that user connects through an application or runs ad hoc SQL.

WITH GREAT CARE

Design all database access using Proxy Users and users created with the "NO AUTHENTICATION" syntax when possible.

CAUTIONS

Limit the number of users connecting with any privilege, including CREATE SESSION to the bare minimum. Note in the demo below that the application server users have no privileges ... zero ... they do not even have CREATE SESSION ... they don't need them.
Carefully monitor the default users created by Oracle Corp. for unexplained accesses. For example, why would anyone log in as WMSYS or XDB?

How Oracle Works

The demo that follow is based on the this three level mapping:

Application objects are owned by the user MECHID.
    Application maintenance will be performed by an APPDBA.
    Privileges on application objects is granted to a schema with no objects other than SYNONYMS named NOOBJ.
        The NOOBJ schema is accessible via proxy users named PROXYA and PROXYB.

The application schema containing sensitive (PII, PHI, PCI) data is not owned by a person ... it is owned by a mechid.
A mechid is a non-human user. We named that schema "mechid" to make that clear through-out the demo.

In Oracle 18c and above the mechid schema should be created with the NO AUTHENTICATION syntax. In older versions the mechid schema should be created with a normal user and then the password should be expired and the account locked. The demo, below, shows the syntax for both.

Proxy users should be

Warning: 
      Before you start reviewing the following demo, if your organization is an Oracle Database Enterprise Edition customer, nothing in this demo requires the purchase of any additional infrastructure, subscriptions, software, or licenses.
      Your organization has already purchased everything required to replicate this technique.

Create mechid, proxy, and application access schemas Let's start by creating all 5 of our required users. -- in version 18c+ CREATE USER mechid NO AUTHENTICATION DEFAULT TABLESPACE uwdata QUOTA 1G ON uwdata TEMPORARY TABLESPACE temp PROFILE default; /* in versions 12c and earlier SELECT sys_guid() FROM dual; SYS_GUID() -------------------------------- 1112F7969A2742989447204B859D462D CREATE USER mechid IDENTIFIED BY "F7969A2742989447204B859D462D11" DEFAULT TABLESPACE uwdata QUOTA 1G ON uwdata TEMPORARY TABLESPACE temp PROFILE default PASSWORD EXPIRE ACCOUNT LOCK; */ CREATE USER APPDBA IDENTIFIED BY "D462D11F7969a2742989447204B89!" TEMPORARY TABLESPACE temp PROFILE ora_stig_profile; -- Grant to APPDBA only those specific privs required to create -- and maintain the application. Nothing more, nothing less. -- The following system privileges, in the real world, would be -- put into a password protected role that is assigned to appdba. GRANT alter user TO appdba; GRANT audit any TO appdba; GRANT create session TO appdba; GRANT create any synonym TO appdba; GRANT create any table TO appdba; GRANT grant any object privilege TO appdba; -- create a user that owns no objects but will be granted -- privileges and synonyms necessary for working with the -- application's. Because it is created with NO AUTHENTICATION -- no one can connect to it except by proxy. CREATE USER NOOBJ NO AUTHENTICATION TEMPORARY TABLESPACE temp PROFILE default; GRANT create session to noobj; -- note, NOOBJ is the only schema created that has the create session privilege. -- proxya will be used by the application servers until a password change is required. CREATE USER PROXYA IDENTIFIED BY "PROXY65!PWD" TEMPORARY TABLESPACE temp PROFILE default; -- when a password change is required application servers are incrementally -- altered to use proxyb to connect. CREATE USER PROXYB IDENTIFIED BY "PROXY66!PWD" TEMPORARY TABLESPACE temp PROFILE default; -- when all application servers are migrated to proxyb the password for proxya -- can be altered and proxya will be ready to use for the next password change.

Provision schemas, enable auditing, and grant CONNECT THROUGH to enable proxy access -- note access is via the Application DBA user ... not the application owner, -- or SYS, or any account with DBA privs sqlplus appdba@pdbdev Enter Password: -- this is the application table for this demo CREATE TABLE mechid.db_assets AS SELECT * FROM all_all_tables; -- this grants access to MECHID's table to NOOBJ. GRANT READ, INSERT, DELETE ON mechid.db_assets TO NOOBJ; GRANT UPDATE (skip_corrupt, inmemory_priority) ON mechid.db_assets TO NOOBJ; -- this creates a synonym to schemaless access to mechid.db_assets. CREATE OR REPLACE SYNONYM noobj.db_assets FOR mechid.db_assets; -- this enables auditing of the proxy schemas. AUDIT CONNECT BY proxya ON BEHALF OF noobj; AUDIT CONNECT BY proxyb ON BEHALF OF noobj; -- grant proxy access ALTER USER noobj GRANT CONNECT THROUGH proxya; ALTER USER noobj GRANT CONNECT THROUGH proxyb; -- revoke the ability to connect except as a proxy ALTER USER proxyb PROXY ONLY CONNECT; ALTER USER proxyb PROXY ONLY CONNECT;

Access test -- note the following ... proxya tries to connect on its own. conn proxya/"PROXY65!PWD"@pdbdev ERROR: ORA-01045: user PROXYA lacks CREATE SESSION privilege; logon denied Warning: You are no longer connected to ORACLE. SQL> Connected. -- without the CREATE SESSION privilege the user proxya can do nothing. -- if someone phishes the userid and password ... they are worthless. -- proxya can only connect when it proxy's NOOBJ and only has the privs -- granted by NOOBJ ... proxya owns no objects, can create no objects, -- and has all other access limited solely to objects with privs granted -- to PUBLIC. Which is why it is so important to revoke grants to PUBLIC -- to as great an extent as possible. conn proxya[noobj]/"PROXY65!PWD"@pdbdev SELECT COUNT(*) FROM noobj.db_assets; COUNT(*) --------- 2255 If you think this is a lot of complexity and work ... writing this demo took 7 minutes: Testing 5. Expanding this one object application to 100 or 1000 is far less than one days work and here at DBSecWorx we have a tool that completely automates the entire process once we have access to the application's schema(s). But consider, how much complexity and work are you expecting if your organization's databases are broken into and trade secrets, PII, PHI, PCI, and other data is stolen? And how much if those that were victimized by the data loss hire an attorney's decide to subpoena members of the DBA team for depositions and interrogatories? Data and database security are not the result of buying expensive tools. They are the result of properly using and configuring the basic capabilities that Oracle has already included in your existing licenses.

General Information

Data Dictionary Objects

ALL_TS_QUOTAS	DBA_USERS	USER_PASSWORD_LIMITS
ALL_USERS	DEFAULT_PWD$	USER_RESOURCE_LIMITS
CDB_TS_QUOTAS	PROXY_INFO$	USER_TS_QUOTAS
CDB_USERS	PROXY_USERS	USER_USERS
DBA_TS_QUOTAS	USER$

Users Created On Default Installation

ANONYMOUS	GGSYS	OUTLN
APEX_050100	GSMADMIN_INTERNAL	PDBADMIN
APEX_INSTANCE_ADMIN_USER	GSMCATUSER	REMOTE_SCHEDULER_AGENT
APEX_LISTENER	GSMUSER	SCOTT
APEX_PUBLIC_USER	LBACSYS	SI_INFORMTN_SCHEMA
APEX_REST_PUBLIC_USER	MDDATA	SYS
APPQOSSYS	MDSYS	SYS$UMF
AUDSYS	OJVMSYS	SYSBACKUP
CTXSYS	OLAPSYS	SYSDG
DBSFWUSER	ORACLE_OCM	SYSKM
DBSNMP	ORDDATA	SYSRAC
DIP	ORDPLUGINS	SYSTEM
DVF	ORDSYS	WMSYS
DVSYS	ORDS_METADATA	XDB
FLOWS_FILES	ORDS_PUBLIC_USER	XS$NULL

Exceptions

Error Code	Reason
ORA-65096	invalid common user or role name

System Privileges

ALTER USER	CREATE_USER	DROP_USER
BECOME USER

Secure Environment USER Statements

Create User with Complex Password CREATE USER <user_name> IDENTIFIED BY "<password>";

CREATE USER oracle3

      IDENTIFIED BY "N0t!4N0W";

Create user without authentication CREATE USER <user_name> NO AUTHENTICATION DEFAULT TABLESPACE <tablespace_name> [LOCAL] TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> <DISABLE | ENABLE> EDITIONS;

CREATE USER oracle11

      NO AUTHENTICATION;

Include Access To A Default Tablespace

Thanks Teresa Robinson for the correction CREATE USER <user_name> IDENTIFIED BY <password>; DEFAULT TABLESPACE <tablespace_name> QUOTA <integer><K | M | G | T | P | E | UNLIMITED>;

SELECT tablespace_name

      FROM dba_tablespaces

      WHERE contents NOT IN ('TEMPORARY', 'UNDO')

      AND tablespace_name NOT IN (

        SELECT tablespace_name

        FROM dba_rollback_segs)

      AND tablespace_name NOT LIKE 'SYS%';

      

      CREATE USER oracle4

      IDENTIFIED BY oracle4

      DEFAULT TABLESPACE uwdata

      QUOTA 100G
      ON uwdata;

      

      SELECT username, default_tablespace

      FROM dba_users

      WHERE username LIKE 'ORA%'

      ORDER BY 1;

      

      USERNAME    DEFAULT_TABLESPACE

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

      ORACLE4     UWDATA

      ORACLE_OCM  USERS

      

      col max_bytes format 99999999999999

      

      SELECT username, tablespace_name, max_bytes/1024/1024/1024 AS MAX_GB

      FROM dba_ts_quotas

      WHERE username LIKE '%ORA%';

      

      USERNAME        TABLESPACE_NAME         MAX_GB

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

      ORACLE4         UWDATA                     100

Include Access To A Temporary Tablespace CREATE USER <user_name> IDENTIFIED BY <password> [LOCAL] TEMPORARY TABLESPACE <temporary_tablespace_name | temporary_tablespace_group>;

SELECT tablespace_name

      FROM dba_tablespaces

      WHERE contents = 'TEMPORARY';

      

      CREATE USER oracle5

      IDENTIFIED BY oracle5

      DEFAULT TABLE uwdata

      TEMPORARY TABLESPACE temp;

      

      SELECT username, default_tablespace, temporary_tablespace

      FROM dba_users

      ORDER BY 1;

Include Quota On Tablespaces CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> [LOCAL] TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> QUOTA <quota_amount> ON <tablespace_name> QUOTA <quota_amount> ON <tablespace_name>;

CREATE USER oracle6

      IDENTIFIED BY oracle6

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp

      QUOTA 0 ON SYSTEM

      QUOTA 0 ON SYSAUX

      QUOTA UNLIMITED ON uwdata

      QUOTA 10M ON data_med;

      

      SELECT username, tablespace_name, max_bytes, max_blocks

      FROM dba_ts_quotas

      ORDER BY 1;

Include Profile

Follow PROFILE link at page bottom for more information CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> [LOCAL] TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name>;

SELECT DISTINCT profile

      FROM dba_profiles;

      

      CREATE USER oracle7

      IDENTIFIED BY "N0Way!"

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp

      QUOTA 0 ON SYSTEM

      QUOTA 0 ON SYSAUX

      QUOTA UNLIMITED ON uwdata

      PROFILE monitoring_profile;

      

      SELECT username, profile

      FROM dba_users

      ORDER BY 1;

Expire the password on creation forcing the first user connecting to set their own password CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> [LOCAL] TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> PASSWORD EXPIRE;

CREATE USER oracle8

      IDENTIFIED BY oracle8

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp

      QUOTA UNLIMITED ON uwdata

      PASSWORD EXPIRE;

      

      SELECT username, expiry_date, account_status

      FROM dba_users;

Lock or unlock the account on creation CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> [LOCAL] TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> ACCOUNT <LOCK | UNLOCK>;

CREATE USER oracle9

      IDENTIFIED BY oracle9

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp

      QUOTA UNLIMITED ON uwdata

      ACCOUNT LOCK;

      

      SELECT username, lock_date, account_status

      FROM dba_users;

Container specific clause. To specify a local user in a single container specify CURRENT CREATE USER <user_name> IDENTIFIED BY <password> CONTAINER = <ALL | CURRENT>;

-- in the following example uwapp is an Application Root container

conn sys@uwapp as sysdba

      

      CREATE USER  uwapp_user1

      IDENTIFIED BY oracle12

      CONTAINER = CURRENT;

Insecure Environment USER Statements

Common User with Simple Password CREATE USER c##<user_name> IDENTIFIED BY <password>;

conn / as sysdba

      

      CREATE USER oracle1

      IDENTIFIED BY oracle1;

      create user oracle1 identified by oracle1

      *

      ERROR at line 1:

      ORA-65096: invalid common user or role name

      

      CREATE USER c##oracle1

      IDENTIFIED BY oracle1;

PDB User with Simple Password CREATE USER <user_name> IDENTIFIED BY <password>;

CREATE USER oracle2

      IDENTIFIED BY oracle2;

      

      SELECT username,  created, password_versions

      FROM dba_users

      ORDER BY 1;

Create proxy-only user

It is an interesting syntax but not as secure as just not granting CREATE SESSION

Error messages that give an attacker a hint as to how to succeed the next time are not recommended and this error message tells them everything about why they failed. ALTER USER <user_name> PROXY ONLY CONNECT; ALTER USER <user_name> CANCEL PROXY ONLY CONNECT;

CREATE USER oracle13

      IDENTIFIED BY secret;

      

      User created.

      

      SELECT username, created, authentication_type, proxy_only_connect

      FROM dba_users

      WHERE username LIKE '%ORA%';

      

      USERNAME        CREATED              AUTHENTI P

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

      ORACLE_OCM      30-MAY-2019 03:53:38 NONE     N

      ORACLE13        17-DEC-2019 21:48:47 PASSWORD N

      

      ALTER USER oracle13 
      PROXY ONLY CONNECT;

      

      User altered.

      

      USERNAME        CREATED              AUTHENTI P

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

      ORACLE_OCM      30-MAY-2019 03:53:38 NONE     N

      ORACLE13        17-DEC-2019 21:48:47 PASSWORD Y

      

      conn oracle13@pdbdev

      Password:

      

      SQL> conn oracle13@pdbdev

      Enter password:

      ERROR:

      ORA-28058: login is allowed only through a proxy

      

      Warning: You are no longer connected to ORACLE.

Enable editions on user creation

There is nothing about enabling editions that is a security issue in and of itself. But if you do enable editioning then you must rewrite management scripts to look at dba_objects_ae rather than dba_objects as well as any references to the other _ae views.

Edition Based Redefinition, as wonderful as it is, adds the possibility that objects may be hidden in a non-default edition so you must pay close attention. CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> ACCOUNT <LOCK | UNLOCK> <DISABLE | ENABLE> EDITIONS;

SELECT username, editions_enabled

      FROM dba_users

      ORDER BY 2,1;

      

      CREATE USER oracle10

      IDENTIFIED BY oracle10

      ENABLE EDITIONS;

      

      SELECT username, editions_enabled

      FROM dba_users

      ORDER BY 2,1;

Creating Operating System Authenticated User: These Are All Insecure And Should Not Be Used

Required changes to allow external authentication ... and changes we do not recommend you make as operating system authentication compromises security ALTER SYSTEM SET remote_os_authent=TRUE SID='*' SCOPE=SPFILE; ALTER SYSTEM SET os_authent_prefix="OPS$" SID = '*' SCOPE=SPFILE; -- restart the database -- add the following to the sqlnet.ora sqlnet.authentication_services = (NTS)

The syntax for CREATE USER where authentication is performed by the operating system on the server and, again, we do not recommend this due to the security compromise CREATE USER <user_name> IDENTIFIED EXTERNALLY;

Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:

      

      CREATE USER ops$oracle  IDENTIFIED EXTERNALLY;

      

      SELECT username,  external_name

      FROM dba_users

      ORDER BY 1;

      

      GRANT create session TO ops$oracle;

      

      Step 2: Create a user in the operating system named oracle if one does not already exist.

      

      Step 3: Go to command line (terminal window in UNIX, cmd in Windows. Type 'sqlplus' (without the single quotes). You should be connected to the database without having to enter username/password.

The syntax for CREATE USER where authentication is performed by the operating system on the client CREATE USER <machine_name\user_name> IDENTIFIED EXTERNALLY;

Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:

      

      CREATE USER "PC100\USER"  IDENTIFIED EXTERNALLY;

      

      where PC100 is the name of the client computer. Then:

      GRANT CREATE SESSION TO "PC100\USER";

      

      Step 2 - Create a user in Windows named USER.

      

      Step 3 - Log on Windows as USER and go to the C:\> command line.

      

      Type 'sqlplus' (without the single quotes). You should be connected to your database without having to enter any username/password.

Password Changes

Password change by user or DBA ALTER USER <user_name> IDENTIFIED <new_password>;

ALTER USER uwclass IDENTIFIED BY "N0Access!";

      User altered.

Password change by optimized for application submission ALTER USER <user_name> IDENTIFIED <new_password> REPLACE <old_password>;

ALTER USER uwclass IDENTIFIED BY secret REPLACE uwclass;

      User altered.

Password change by DBA using password hash ALTER USER <user_name> IDENTIFIED BY VALUES <'recovered_password_hash'>;

Note ... the password hashes recovered from USER$ in this demo are broken into multiple (5) lines to accommodate the website format.
      To work they need to be reconstructed as a single line from starting single-quote to ending single_quote.

      

      conn sys@pdbdev as sysdba

      Password:

      

      -- set the inital password for user uwclass and capture the password hash

      ALTER USER uwclass IDENTIFIED BY uwclass;

      

      SELECT spare4 FROM sys.user$ WHERE name='UWCLASS';

      

      SPARE4

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

      S:29AA882F714866653497FE679E15ECFD8B46342407C7AE5C229610A76043;

      H:33026143E040882C64C2375E7086F96B;

      T:E78343A31ADD6ADD4BF1B203D50B2B58355F45EBA2A19650C3525

      E7F956EDCDEE1FB9581D5A530232730AC4DEF8B8D9F0EE8ECC51C19

      219AC430A88188694EE72778CAA657281E3F3360F8C8D2951329

      

      -- change the password for user uwclass and capture 
      the new password hash

      ALTER USER uwclass IDENTIFIED BY secret;

      

      SELECT spare4 FROM sys.user$ WHERE name='UWCLASS';

      

      SPARE4

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

      S:C8839A12FA077E78CC63418BC0EF65F0EDCF4CBA39476C225C10408F9EEE;

      H:73B095C3A1DE0547DF2726296C64BC37;

      T:C420AFD3814CA9BE36A3274A941331000650C9816F5F13956DA8E069036

      DDCF95CAC249E35B9302549AEA71BFEEBEC55DFE44954F8C26A

      2319FD252F73614319662CC9FD86A982060E9C75D51E0DFA4F

      

      -- using the hash for the first password return the 
      user password to the first value

      ALTER USER uwclass IDENTIFIED BY VALUES '

      S:29AA882F714866653497FE679E15ECFD8B46342407C7AE5C229610A76043;

      H:33026143E040882C64C2375E7086F96B;

      T:E78343A31ADD6ADD4BF1B203D50B2B58355F45EBA2A19650C3525

      E7F956EDCDEE1FB9581D5A530232730AC4DEF8B8D9F0EE8ECC51C19

      219AC430A88188694EE72778CAA657281E3F3360F8C8D2951329';

      

      -- the user logs on and the first value has been restored

      conn uwclass/uwclass@pdbdev

      Connected.

      

      SQL>

Operating System Accounts

Group membership in UNIX Operating system accounts that are members of the operating system's DBA group are not required to provide a userid and password when logging in.

Group membership in Windows Operating system accounts that are members of the operating system's ORA_DBA group are not required to provide a userid and password when logging in.

Windows logins Automatic logins with the Windows operating system are not secure. Anyone can edit the Oracle configuration file and change their user ID. For security reasons, if users of these systems are logging in over the network, Oracle Corporation recommends that you disable the ops$ logins in the listener.ora.

Related Queries

Active Connected Users SELECT sid, username, action FROM v$session WHERE username IS NOT NULL AND status = 'ACTIVE';

Find dynamic SQL in unwrapped database source code.

In version 18.3 this query returns more than 1,500 rows conn / as sysdba SELECT owner, name, type, line, text, 'DBMS_SQL used' AS REASON FROM dba_source WHERE UPPER(text) LIKE '%DBMS_SQL%' UNION ALL SELECT owner, name, type, line, text, 'Native Dynamic used' FROM dba_source WHERE UPPER(text) LIKE '%EXECUTE IMMEDIATE%' ORDER BY 1,2,3;

Get Connection Information set linesize 141 col authentication_type format a20 col osuser format a20 col client_charset format a20 col network_service_banner format a60 desc gv$session_connect_info; SELECT sid, authentication_type, osuser, network_service_banner FROM gv$session_connect_info; SELECT sid, client_charset, client_connection, client_oci_library, client_version FROM gv$session_connect_info;

Get Idle Time col SID format 999 col IDLE format a20 col PROGRAM format a20 col USERNAME format a20 SELECT sid, osuser, username, status, TO_CHAR(logon_time, 'DAY HH24:MI:SS') LOGON_TIME, FLOOR(last_call_et/3600) || ':' || FLOOR(MOD(last_call_et, 3600) / 60) || ':' || MOD(MOD(last_call_et, 3600), 60) IDLE, program FROM v$session WHERE username IS NOT NULL ORDER BY last_call_et;

Get User Memory Usage SELECT username, program, value || 'bytes' "Current UGA memory" FROM v$session sess, v$sesstat sstat, v$statname sname WHERE sess.sid = sstat.sid AND sstat.statistic# = sname.statistic# AND sname.name = 'session uga memory';

Get User Waits SELECT SUBSTR(s.USERNAME,1,15) USERNAME, SUBSTR(s.status,1,8) STATUS, SUBSTR(s.server,1,10) SERVER, SUBSTR(s.type,1,10) TYPE, SUBSTR(s.event,1,20) "WAIT EVENT", DECODE(s.command, 1,'Create Table', 2,'Insert', 3,'Select', 6,'Update', 7,'Delete', 8,'Drop', 9,'Create Index', 10,'Drop Index', 12,'Drop Table', 17,'Grant', 26,'Lock Table', 42,'Alter Session', 43,'Alter User', 44,'Commit', 45,'Rollback', s.command) COMMAND FROM v$session s, v$session_wait w WHERE s.sid = w.sid AND s.username != 'SYS' ORDER BY 1;

Identify the current session SELECT user, osuser, terminal, program FROM gv$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

Identify current users col name format a20 col process format a12 col program format a17 SELECT sid, serial#, SUBSTR(username,1,10) NAME, SUBSTR(machine,1,10) COMPUTER, command, status, SUBSTR(osuser,1,8) OSUSER, process, program FROM v_$session ORDER BY name;

Identify current users col username format a21 col profile format a10 col "tmp tbs" format a10 SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS", u.profile, r.granted_role, r.admin_option, r.default_role FROM sys.dba_users u, sys.dba_role_privs r WHERE u.username = r.grantee (+) GROUP BY u.username, u.default_tablespace, u.temporary_tablespace, u.profile, r.granted_role, r.admin_option, r.default_role;

Identify default users and passwords col user_name format a30 col pwd_verifier format a20 SELECT * FROM default_pwd$ ORDER BY 1;

Identify privileged users SELECT * FROM gv$pwfile_users;

-- for this to be meaningful you must be using an exclusive password file to authenticate privileged users