Posted on

Secure oracle database users


When we connect to the Oracle Database instance, the user account must be authenticated
Authentication – validating the identity of the user and confirming that they have the authority to use the database resources
Oracle offers three authentication methods – password authentication (mostly used), global authentication, external authentication
1) Password authenticated users :
When a user connects to the database using password authentication method, database verifies that the username is a valid database account and the password stored in the database matches the password supplied.Most common method. Password authenticated accounts are referred to as database authenticated accounts.The database stores the encrypted password in the data dictionary
CREATE USER LR IDENTIFIED BY LR;
The clause identified by password tells the database that this user account is a password authenticated account
2) External authentication :
Database trusts external authentication. OS performs the authentication.When a user attempts to connect to the database using external authentication method, the database verifies that the account is a valid datlineabase account and trusts that the OS has performed the authentication.External authenticated user accounts don’t store or validate a password in the database.Oracle introduced these accounts in Oracle 6 and these accounts have to be prefixed with OPS$. So they may be referred to as OPS$ accounts
From Oracle 6 to Oracle 10g we can configure this is init.ora / SPFILE
Set OS_AUTHENT_PREFIX parameter.Default value is Os_AUTHENT_PREFIX =“” (No spaces)
Oracle manages the user privileges at the OS level but user authentication is performed external to the database at OS level
CREATE USER username identified externally;
No password is needed. As long as the user is able to loginto OS , he can log into database also
Advantage – We’ll need a single username for both the operating system and database connections
Helps in auditing user actions – as database names and OS account name match
Externally authenticated accounts are frequently used for administrative scripts so that password doesn’t have to be embedded in a human readable script
3) Global Authentication :
When a globally authenticated user tries to connect to the database, the database verifies that the username is valid and passes the connection information to the advanced security option for authentication
The advanced security options support several mechanisms for authentication including biometrics, X.509 certificates, kerberos, RADIUS
Globally authenticated user accounts don’t store or validate a password in the database as a normal user account does
These accounts rely on the authentication provided by a service supported through a advanced security option
This is also referred to as centralized user authorization. We can make use of OID for this purpose
Centralized management enables the use of single-sign on – users need only sign in once to access all the databases they need to use
Advanced security option from oracle, and not widely spread
Create user username identified globally as ‘CN=master, OU=tier2, O=security, C=US’;
4) Proxy authentication :
We can use several middle-tier products to facilitate user interaction with the database
Web server is often used as middle/application tier connecting the clients to the database
We can choose to have the middle tier authenticate the users, or we can choose the middle tier to pass the username/password to database for authentication
SQL> alter user username grant connect through middletier authenticated using password; – Authorize the middletier to act as proxy for a user, with authentication by password
SQL> alter user username grant connect through middletier; – We can authorize the middle tier to connect as a user;
While Oracle database software is installed and database is created, they have default passwords assigned to them. One such popular user is SCOTT with default password TIGER. The data dictionary view dba_users_with_defpwd will list the users with default password in oracle database 11g.
SQL> desc dba_users_with_defpwd;
Name                                      Null?    Type
—————————————– ——– ———————-
USERNAME                                  NOT NULL VARCHAR2(30)
SQL> select username from dba_users_with_defpwd;
USERNAME
——————————
DIP
XS$NULL
MDSYS
SPATIAL_WFS_ADMIN_USR
OUTLN
CTXSYS
OLAPSYS
SPATIAL_CSW_ADMIN_USR
OWBSYS
ORACLE_OCM
EXFSYS
USERNAME
——————————
SCOTT
ORDSYS
ORDPLUGINS
MDDATA
PM
APPQOSSYS
XDB
ORDDATA
IX
BI
WMSYS
USERNAME
——————————
SI_INFORMTN_SCHEMA
23 rows selected.

SCOTT with default password TIGER is displayed.
Oracle database has lots of inbuilt views that lets us determine teh list of users/username at SQL prompt level. Use this simple command to determine the usernames at DB level

select * from v$session;

To be exact :

select username from v$session;

In a RAC environment use the following command

select username from gv$session;