Query to find oracle database version

The DBA_SYS_PRIVS view contains three columns of data:

• GRANTEE is the name, role, or user that was assigned the privilege.
• PRIVILEGE is the privilege that is assigned.
• ADMIN_OPTION indicates if the granted privilege also includes the ADMIN option.

To determine which users have direct grant access to a table we’ll use the DBA_TAB_PRIVS view:

SELECT * FROM DBA_TAB_PRIVS;

• GRANTEE is the name of the user with granted access.
• TABLE_NAME is the name of the object (table, index, sequence, etc).
• PRIVILEGE is the privilege assigned to the GRANTEE for the associated object.

Finally, querying the DBA_ROLE_PRIVS view has much of the same information but applicable to roles instead, where the GRANTED_ROLE column specifies the role in question:

SELECT * FROM DBA_ROLE_PRIVS;

Querying the Current User’s Privileges

If DBA access isn’t possible or necessary, it is also possible to slightly modify the above queries to view the privileges solely for the current user.

This is done by alternatively querying USER_ versions of the above DBA_ views. Thus, instead of looking at DBA_SYS_PRIVS we’d query USER_SYS_PRIVS, like so:

SELECT * FROM USER_SYS_PRIVS;

Since the USER_ privilege views are effectively the same as their DBA_ counterparts, but specific to the current user only, the type of returned data and column names are all identical to those when querying DBA_ views intead.

Advanced Script to Find All Privileges

While the above methods will work for basic system configurations, things start to become messy in Oracle when many roles exist which are in turn granting role privileges to other roles, and so on down the rabbit hole. Since the DBA_ and USER_ privilege views only display GRANTEES with directly assigned access, often privileges that are inhereted through other roles will not be readily shown.

To resolve this, it is advisable to use an advanced script such as the trusted work of Pete Finnigan and his find_all_privs.sql script. You may also opt for a modified version by David Arthur, find_all_privs2.sql.

In either case, the purpose of these scripts is to allow you to recursively locate all privileges granted to a particular user. When the script locates a role for the user, it recursively searches for other roles and privileges granted to that role, repeating the process all the way down the chain. The results of the script can be output to the screen or to a file as desired.

• free oracle database client
• Query to delete database in MySQL
• Query to find database version in oracle