?
Solved

oracle queries for password stats and permissions

Posted on 2013-02-06
8
Medium Priority
?
577 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 668 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 668 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 664 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 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