Several data dictionary views can be used to retrieve information about users. The following MouseOver shows some of the views related to what you have learned in this chapter.
The DBA_USERS view returns one row for each user in the database. Use this view to find out about default and temporary tablespace assignments, profile assignments, account creation date, and password expiration date
The DBA_SYS_PRIVS view returns information about system privileges that have been granted to a user.
The DBA_TS_QUOTAS view returns information about a user's quotas on tablespaces in the database.
Viewing User Information
For the most part, it should be fairly obvious how to query these views. The column names explain their contents quite well.
Listing the privileges for a user
The following query may be used to list all the system privileges that have been granted to a user:
SELECT privilege
FROM dba_sys_privs
WHERE grantee='USERNAME';
Replace username with the name of the user who's privileges you are researching. Usernames should normally be specified in upper-case form. For example, use 'SYSTEM', not 'system'.
Listing a user's Tablespace Quotas
To determine a user's tablespace quotas and find out how much space has actually been used, you can use this query:
SELECT tablespace_name,
bytes space_used,
max_bytes quota
FROM dba_ts_quotas
WHERE username = 'USERNAME';
As with the previous query, the username should be upper-case. A quota of -1 indicates that the user has been given unlimited quota on a tablespace