Some security aspects in oracle database


1. When did a user change his/her password?

The table sys.user$ contains the field ptime, which keeps the time when the password was changed the last time over. Do not confound it with ctime, which is the “creation time”, nor with ltime, which is the time the account has been locked (if any).
SELECT NAME, ptime AS “LAST TIME CHANGED”, ctime “CREATION TIME”, ltime “LOCKED”
FROM USER$
WHERE ptime IS NOT NULL
ORDER BY ptime DESC;

2. Users with deadly system privileges assigned to them

Sometimes users have system privileges inheritated before a strickly secure system was build. To find that users execute:

select grantee, privilege, admin_option
from sys.dba_sys_privs
where (privilege like ‘% ANY %’
or privilege in (‘BECOME USER’, ‘UNLIMITED TABLESPACE’)
or admin_option = ‘YES’)
and grantee not in (‘SYS’, ‘SYSTEM’, ‘OUTLN’, ‘AQ_ADMINISTRATOR_ROLE’,
‘DBA’, ‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’,
‘OEM_MONITOR’, ‘CTXSYS’, ‘DBSNMP’, ‘IFSSYS’,
‘IFSSYS$CM’, ‘MDSYS’, ‘ORDPLUGINS’, ‘ORDSYS’,
‘TIMESERIES_DBA’);

3. Users with deadly roles assigned to them

select grantee, granted_role, admin_option
from sys.dba_role_privs
where granted_role in (‘DBA’, ‘AQ_ADMINISTRATOR_ROLE’,
‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’,
‘OEM_MONITOR’)
and grantee not in (‘SYS’, ‘SYSTEM’, ‘OUTLN’, ‘AQ_ADMINISTRATOR_ROLE’,
‘DBA’, ‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’,
‘OEM_MONITOR’, ‘CTXSYS’, ‘DBSNMP’, ‘IFSSYS’,
‘IFSSYS$CM’, ‘MDSYS’, ‘ORDPLUGINS’, ‘ORDSYS’,
‘TIMESERIES_DBA’);

4. Init parameters for security

To find the initialization parameters for setting up security in Oracle database execute:

SELECT SUBSTR(NAME,0,25) NAME, SUBSTR(VALUE, 0,10) VALUE, SUBSTR(description, 0, 60) description
FROM v$parameter
WHERE NAME IN (‘remote_login_passwordfile’, ‘remote_os_authent’,
‘os_authent_prefix’, ‘dblink_encrypt_login’,
‘audit_trail’, ‘transaction_auditing’);

NAME VALUE DESCRIPTION
————————- ———- ————————————————————
transaction_auditing TRUE transaction auditing records generated in the redo log
remote_os_authent FALSE allow non-secure remote clients to use auto-logon accounts
remote_login_passwordfile EXCLUSIVE password file usage parameter
dblink_encrypt_login FALSE enforce password for distributed login always be encrypted
audit_trail DB enable system auditing
os_authent_prefix ops$ prefix for auto-logon accounts

6 rows selected.
To find the password file users execute:

SQL> select * from sys.v_$pwfile_users;

USERNAME SYSDB SYSOP
—————————— —– —–
SYS TRUE TRUE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s