Solved

oracle queries for password stats and permissions

Posted on 2013-02-06
8
572 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 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