Oracle Proxy User Accountss
Versions All

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, subscriptionss, 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;

-- this grants the proxy access privilege.
ALTER USER noobj GRANT CONNECT THROUGH proxya;
ALTER USER noobj GRANT CONNECT THROUGH proxyb;
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

Related Topics
-