Solved

oracle queries for password stats and permissions

Posted on 2013-02-06
8
575 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 35

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 35

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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 77

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
 
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 77

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 35

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

617 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