Правило автоматизации Jira при слиянии запроса pull
Posted by: AJ Welch
Retrieving all user privileges within Oracle can range from a simple task using a basic SQL query to an advanced script, depending primarily on how involved the roles and privileges are configured within the server.
In this brief tutorial, we’ll cover both the basic SQL query method as well as the advanced script method so you’ll have no issue regardless of the complexity of your setup.
За рамками Agile
A database administrator (DBA) for Oracle can simply execute a query to view the rows in DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS to retrieve information about user privileges related to the system
, tables
, and roles
, respectively.
For example, a DBA wishing to view all system
privileges granted to all users would issue the following query:
SELECT
*
FROM
DBA_SYS_PRIVS;
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 theADMIN
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;
You can check the official documentation for more information about the columns returned from this query, but the critical columns are:
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 theGRANTEE
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;
За рамками Agile
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.
За рамками Agile
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.
More information on these scripts and their usage can be found at petefinnigan.com.
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.