Solved

oracle queries for password stats and permissions

Posted on 2013-02-06
8
569 Views
Last Modified: 2013-02-07
in oracle 11g, i need to identify a way to list for each db account:

the date the accounts password was set
the last time the account was used to login
the permissions/priveleges granted to the account

any pointers?
0
Comment
Question by:pma111
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 167 total points
ID: 38860874
As far as I know, when the password was last set is in SYS.USER$ in the PTIME column.

As far as I know, Oracle does not store the last login for an account.  You would either need to turn on auditing or write a custom logon trigger to capture that.

You can get privileges from DBA_SYS_PRIVS, DBA_TAB_PRIVS and DBA_ROLE_PRIVS.
0
 
LVL 3

Author Comment

by:pma111
ID: 38860930
are there any specific pointers for identifying database accounts with elevated priveleges? i.e. if we think in terms of an operating system, how to diffrentiate between the users, power users and administrators....?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 38860972
It all depends on what you mean by elevated privileges.  That means different things to different auditors.

You can always look for the DBA role, which should identify your admins:

select grantee from dba_role_privs where granted_role = 'DBA';

Other than that Oracle doesn't give you a way to do it.  However, good application designs contain roles for the different types of users it supports and you should be able to identify which users belong to which roles.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 167 total points
ID: 38860992
The role and priv views are the place to go for permission information.

You can get things like last login times if you enable auditing in the database.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Author Comment

by:pma111
ID: 38861000
ok thanks

just looking at those table names, "..sys_privs", "..role_privs", "..tab_privs" what are the "sys privs", "role privs", "tab privs"? Can you give a bit of a beginners guide to

role priveleges
tab priveles
sys priveleges
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38861034
>>Can you give a bit of a beginners guide to


The online docs have everything you need.  All views are described in-depth.

For example:
ROLE_PRIVS:
http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_4217.htm#REFRN23230
0
 
LVL 34

Expert Comment

by:johnsone
ID: 38861061
As slightwv posted, the docs are the best place to go.

The basic idea here is this:

DBA_ROLE_PRIVS - are roles that are granted to another user or role
DBA_TAB_PRIVS - are object level privileges granted to a user or role
DBA_SYS_PRIVS - are system level privileges granted to a user or role

There is also a good overview in the documentation for the GRANT statement:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9013.htm#i2155015
0
 
LVL 23

Assisted Solution

by:David
David earned 166 total points
ID: 38861447
This looks pretty well covered, with one more add about priv levels from comment 38860930.  My rule of thumb is to segregate in production:

--Overhead accounts such as WMSYS: specialized usage, locked down if not needed
--DBA accounts such as SYS, SYSTEM; and individual users to whom DBA is granted
--Schema accounts, users that own application objects (tables, indexes, packages, etc.)
--Superuser accounts, admin users who can change data belonging to the schemas, such as APPS profile permissions
--Audit accounts, usually locked down, but with SELECT on all dictionaries
--Application user accounts, restricted by roles and object privileges to use certain portions of a schema / application
--Query or read-only accounts, restricted to SELECT object privileges on some views, tables.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Read about achieving the basic levels of HRIS security in the workplace.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now