Oracle_securiity_settings..

I need to print a screen or oracle database 12c security settings.

Is there a tool or a screen that shows that? if not is there a view or table that can show the settings.
sam15Asked:
Who is Participating?
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
Hi,

Let's take for example user SCOTT, he has been assigned profile DEFAULT.

USERNAME                  ACCOUNT_STATUS            LOCK_DATE            EXPIRY_DATE          CREATED              PROFILE         AUTH_TYPE
------------------------- ------------------------- -------------------- -------------------- -------------------- --------------- ----------
SCOTT                     EXPIRED & LOCKED          13-01-2016 00:12:11  13-01-2016 00:12:11  13-01-2016 00:11:56  DEFAULT         PASSWORD

Open in new window


Answers are in profile definition.
3) how long passwords are active for (i.e 90 days)
PASSWORD_LIFE_TIME defines that.

4) How many tries users can login before they get locked
FAILED_LOGIN_ATTEMPTS defines that.

5) Idle Session timeout settings
IDLE_TIME defines that.

PROFILE                   RESOURCE_NAME                    RESOURCE_TYPE  LIMIT                COMMON
------------------------- -------------------------------- -------------- -------------------- ------
DEFAULT                   COMPOSITE_LIMIT                  KERNEL         UNLIMITED            NO
DEFAULT                   SESSIONS_PER_USER                KERNEL         UNLIMITED            NO
DEFAULT                   CPU_PER_SESSION                  KERNEL         UNLIMITED            NO
DEFAULT                   CPU_PER_CALL                     KERNEL         UNLIMITED            NO
DEFAULT                   LOGICAL_READS_PER_SESSION        KERNEL         UNLIMITED            NO
DEFAULT                   LOGICAL_READS_PER_CALL           KERNEL         UNLIMITED            NO
DEFAULT                   IDLE_TIME                        KERNEL         UNLIMITED            NO
DEFAULT                   CONNECT_TIME                     KERNEL         UNLIMITED            NO
DEFAULT                   PRIVATE_SGA                      KERNEL         UNLIMITED            NO
DEFAULT                   FAILED_LOGIN_ATTEMPTS            PASSWORD       10                   NO
DEFAULT                   PASSWORD_LIFE_TIME               PASSWORD       180                  NO
DEFAULT                   PASSWORD_REUSE_TIME              PASSWORD       UNLIMITED            NO
DEFAULT                   PASSWORD_REUSE_MAX               PASSWORD       UNLIMITED            NO
DEFAULT                   PASSWORD_VERIFY_FUNCTION         PASSWORD       NULL                 NO
DEFAULT                   PASSWORD_LOCK_TIME               PASSWORD       1                    NO
DEFAULT                   PASSWORD_GRACE_TIME              PASSWORD       7                    NO

Open in new window


Regards,
Daniel
0
 
Geert GOracle dbaCommented:
what security settings ?

the database runs on the host ...
security settings of the host ?

whoever aksed you this, needs to be way more explicit
0
 
Daniel_PLDB Expert/ArchitectCommented:
Hi,

I suggest you to download a CIS Benchmark for Oracle 12c. There are recommendations for setting your Oracle databases secure. You can find details of how to set something and explanations for settings being mentioned.
CIS Benchmark - Oracle Database

If you find some settings interesting get back with further questions.

Regards,
Daniel
0
Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

 
slightwv (䄆 Netminder) Commented:
I agree with Geert:  You question can mean very many things.  You need to tell us exactly what you are looking for.

For an overall high level look, you can use Oracle's Database Security Assessment Tool  (DBSAT):
https://docs.oracle.com/cd/E76178_01/SATUG/toc.htm#SATUG-GUID-C7E917BB-EDAC-4123-900A-D4F2E561BFE9

It produces a nice report but it requires Python to run.
0
 
sam15Author Commented:
I know Linux has its own security setting but oracle has its own security settings too. they might be the default.

security settings include the following:
1) Whether auditing  is turned on or off,
2) whether user passwords expire or not
3) how long passwords are active for (i.e 90 days)
4) How many tries users can login before they get locked
5) Idle Session timeout settings
6)
etc.

Is there a screen that can show all of those?
0
 
slightwv (䄆 Netminder) Commented:
When you say "Screen" do you mean Enterprise Manager Grid Control or the Built in version (used to be dbControl and I forget the new 12c name)?

I do not believe there is a single screen that shows all that but I really don't use the GUI.

Check out DBSat.  It has a lot of information and it might cover all your topics.

If you need all that information, I would look at writing a SQL script that queries the data dictionary and you can display the output however you want.

There are many samples of queries for the information you need out there if you look around.  If you need specific help on any one of them, let us know.
0
 
Daniel_PLDB Expert/ArchitectCommented:
Hi,

Easiest would be to create own "screen" with query results :)

2) whether user passwords expire or not
3) how long passwords are active for (i.e 90 days)
4) How many tries users can login before they get locked
5) Idle Session timeout settings

Most answers are to be found in users and profiles views.

set lin 150
col username format a25
col account_status format a25
col lock_date format a20
col expiry_date format a20
col created format a20
col profile format a15
col auth_type format a10

select username
, account_status
, to_char(lock_date, 'DD-MM-YYYY HH24:MI:SS') lock_date
, to_char(expiry_date, 'DD-MM-YYYY HH24:MI:SS') expiry_date
, to_char(created, 'DD-MM-YYYY HH24:MI:SS') created 
, profile
, authentication_type as auth_type
from dba_users;

col profile format a25
col resource_name format a32
col resource_type format a14
col limit format a20
col common format a6

select profile
,resource_name
,resource_type
,limit
,common
from dba_profiles;

Open in new window


Explanation of users columns:
https://docs.oracle.com/database/121/REFRN/GUID-309FCCB2-2E8D-4371-9FC5-7F3B10E2A8C0.htm#REFRN23302

Open in new window


Explanation of profile options:
https://docs.oracle.com/database/121/SQLRF/statements_6012.htm#SQLRF01310

Open in new window


Auditing options:
SHOW PARAMETER AUDIT

Open in new window


Auditing settings:
col user_name format a15
col audit_option format a20
col privilege format a20
col success format a10
col failure format a10

select user_name
, audit_option 
, success
, failure
from dba_stmt_audit_opts
union
select user_name
, privilege
, success
, failure
from dba_priv_audit_opts;

Open in new window


I encrourage you to review CIS for databases posted previosly.

Regards,
Daniel
0
 
Geert GOracle dbaCommented:
the "etc." is a very interesting item
that can still mean anything.

only give auditors what they asked, and nothing else
auditors usually have little knowledge of the database itself

they can test you too:
they requested the contents of user$ once ... which includes the password column with hashed contents

I responded as this:
"Your request looks a lot like what a hacker would request to try and find passwords. Are you really from the audit department ?
Please be very specific in what you are asking."

The requestor turned out to be a newbie auditor ... and not a hacker
0
 
sam15Author Commented:
yes, a screen using OEM, SQL Developer, TOAD. I assume no GUI would have a feature that shows oracle security settings and I have to run several queries as show above to get answers for each question. It seems the screen request might be someone thinking the screen cant be changed but the SQL output can be manipulated by anyone.
0
 
Daniel_PLDB Expert/ArchitectCommented:
In any of tools you pointed you need to take a look at parts of information which all make the whole picture. The same is with queries. As pointed by Geert, it seems you're under some kind of audit, they tend to ask for screen proofs.
It is good to know some about settings they might ask you, therefore once again I'm going to encourage for some reading.
0
 
slightwv (䄆 Netminder) Commented:
Even if there was a GUI, I'm thinking the screen can be changed.

If you are under an audit and that is the reason behind this question:  For our audits I was able to use with screen prints of sqlplus output.

Did you check out DBSAT?  I believe it will generate reports in different formats.  PDF is one of them.

However, even PDFs can be easily changed these days.

2.The DBSAT Reporter analyzes the collected data and reports its findings and recommendations in multiple formats: PDF, Excel, and Text. The Reporter can run on any machine: PC, laptop, or server. You are not limited to running it on the same server as the Collector.
0
 
sam15Author Commented:
Daniel,

I checked the 3 views you listed above:

1) dba_users
2) dba_profiles
3) dba_priv_audit_opts

to see how it can provide answers to  the questions you listed

 2) whether user passwords expire or not (DBA_USERS.ACCOUNT_STATUS)
 3) how long passwords are active for (i.e 90 days)
 4) How many tries users can login before they get locked
 5) Idle Session timeout settings
 
 
 I do not see the answers for #3, #4 and #5.
 
 Can you specify the view columns that answer that?
0
 
sam15Author Commented:
okay great answer.

one last thing on auditing. I assume the oracle audits database connections and success/fail by default and you don't turn that ON after install. What view do you check for the audit log in since audit_trail is set to DB?
0
 
Daniel_PLDB Expert/ArchitectCommented:
Hi,
Everything is in dba_audit_trail:

select 
   os_username,
   username,
   terminal,
   action_name,
   to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'),
   returncode,
   sql_text
from
   dba_audit_trail;

Open in new window


For failed logons you'll have returncode of 28000, when account is locked you'll observe 1017.

Please also take a note on new Oracle 12c feature called unified auditing.
https://blogs.oracle.com/imc/oracle-database-12c-security:-new-unified-auditing

Open in new window

0
 
Geert GOracle dbaCommented:
oracle database doesn't audit by default
you'll need to switch everything on which has to be audited
you'll also have to cleanup the audit trail

nowadays, in 12 you can actually get the last logon time without have to write a logon trigger yourself :)

i have added failed logon attempts in my auditing, just to be aware of anyone trying to hack for passwords
or automated processes continuously causing  a user to get locked

audit session by access whenever not successful;

Open in new window


and each week i mail myself this output:
select username, os_username, terminal, returncode, min(timestamp) first_attempt, max(timestamp) last_attempt, count(*)
from dba_audit_trail
where timestamp > sysdate-7
  and action = 100
  and returncode in (1017, 28000)
group by username, os_username, terminal, returncode
order by 5;

Open in new window

0
 
Daniel_PLDB Expert/ArchitectCommented:
To be specific it is:

*Connections to the instance with administrator privileges
*Database startup
*Database shutdown

What is being audited:

select * from dba_obj_audit_opts
 where ALT != '-/-'
 or AUD != '-/-'
 or COM != '-/-'
 or DEL != '-/-'
 or GRA != '-/-'
 or IND != '-/-'
 or INS != '-/-'
 or LOC != '-/-'
 or REN != '-/-'
 or SEL != '-/-'
 or UPD != '-/-'
 or REF != '-/-'
 or EXE != '-/-'
 or CRE != '-/-'
 or REA != '-/-'
 or WRI != '-/-';

select privilege, success, failure from dba_priv_audit_opts;

Open in new window

0
 
sam15Author Commented:
Excellent!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.