• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

oracle queries for password stats and permissions

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
pma111
Asked:
pma111
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
johnsoneSenior Oracle DBACommented:
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
 
pma111Author Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
slightwv (䄆 Netminder) Commented:
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
 
pma111Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
johnsoneSenior Oracle DBACommented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now