How do we find what permissions a user has in an Oracle database
Certifications:
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
- How do we find what permissions a user has on an Oracle database - 1 November 2024
- How we enable Unified Auditing in Oracle Database - 7 October 2024
In this article we will see how we can easily find what privileges a user has either on objects such as tables, or system rights such as e.g. the right to create users. At the beginning we will see one by one the views that exist with this information by simply seeing queries and then we will see some queries that I have made that we can see all the information very easily.
Before we start to see how we find the rights we should know that permissions are not only given on users but also on user roles. User roles are a group of rights that can contain both rights on objects such as tables and system rights such as the right to create a new user. We use their roles so that every time we create a user we don't have to give them the rights we want one by one, but we can simply add this user to a role that already has them. We should also know that there are some roles with system rights that are created by default, but we can also create our own. End we do not forget that each user has full rights to his objects, e.g. the user Scott he can do whatever he wants to the data in his array Scott.Dept, for this reason the rights to the objects of the same user do not appear in the views we will see.
How to see which roles a user belongs to
For our example we want to find if the user belongs to a role, to see this we run the following query from the view dba_role_privs:
select * from DBA_ROLE_PRIVS where GRANTEE = 'SCOTT'
So we see that the user (GRANTEE) has the two roles connect and resource (GRANTED_ROLE).
How we see what rights and on which tables a user has
To see what rights the user has on objects / tables, run the following query from the view dba_tab_privs:
select * from DBA_TAB_PRIVS where GRANTEE = 'SCOTT';
To understand the above where GRANTEE is the username, OWNER is the name of the schema to which the object belongs, TABLE_NAME the name of the object / table, PRIVILEGE the right he has, GRANTABLE if he can give it to someone else and that's it TYPE what type of object is it, e.g. panel.
But what if that user belonged to a custom role that gave him access to more objects? Then the above query would not bring it, to see this we should run the following query which brings the rights for the roles it belongs to:
select * from DBA_SYS_PRIVS WHERE 1=1 AND (GRANTEE = 'SCOTT' OR GRANTEE IN (SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'SCOTT'));
How to see in a table which users have rights and what they are
Accordingly, to see which users or roles and what rights they have in a table, we run the following query:
select * from DBA_TAB_PRIVS where TABLE_NAME = 'DEPT';
How we see what system rights a user has
To see what system rights the user has, run the following query from the view dba_sys_privs:
select * from DBA_SYS_PRIVS where GRANTEE = 'SCOTT'
However, in order to capture the system rights that the user and role may have, we run the following query:
select * from DBA_SYS_PRIVS WHERE 1=1 AND (GRANTEE = 'SCOTT' OR GRANTEE IN (SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'SCOTT'));
How do we see what rights a user has in total?
Let's go now to the most useful queries of the article combining what we saw before.
With the following query that I have written, we can find all the rights that a user has, whether they are systemic or on objects, as well as the roles that he can have:
select fake.OWNER,fake.GRANTEE, fake.TABLE_NAME, fake.PRIVILEGE, case WHEN fake.OWNER is null then 'grant '||fake.privilege||' to '||grantee||';' ELSE 'grant '||fake.privilege||' on '||fake.owner||'.'||fake.table_name||' to '||grantee||';' end "GRANT STATEMENTS" from( SELECT OWNER,GRANTEE, TABLE_NAME ,PRIVILEGE FROM DBA_TAB_PRIVS union all SELECT null,GRANTEE, ' IT IS A SYSTEM PRIV', PRIVILEGE FROM DBA_SYS_PRIVS union all SELECT null, GRANTEE, ' IT IS A ROLE', GRANTED_ROLE from DBA_ROLE_PRIVS ) fake where 1=1 --and fake.OWNER='HR' and GRANTEE='SCOTT' order by fake.TABLE_NAME;
As you can see in the last column, I have added the command that we can run to recreate them, either to run in another environment or for another user.
How we see aggregated what type of rights a user has
With the following query that I have made, we can see in aggregate what type of rights a user has and whether he can give them to another. For example, we can see if a user has the right to update, insert in some objects of a shape, or if he has the right to create users:
select fake.owner,fake.GRANTEE,fake.PRIVILEGE,fake.ADMIN_OPTION,fake.GRANTABLE
from (
select owner,GRANTEE,PRIVILEGE,null as ADMIN_OPTION, GRANTABLE from dba_tab_privs
--where OWNER = 'HR'
union all
select null,GRANTEE,PRIVILEGE, ADMIN_OPTION, null as GRANTABLE from dba_sys_privs
) fake
WHERE 1=1
--and fake.PRIVILEGE in ('INSERT','DELETE','UPDATE','ALTER')
and fake.GRANTEE='SCOTT'
or GRANTEE IN(SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'SCOTT')
GROUP BY fake.owner,fake.GRANTEE,fake.PRIVILEGE,fake.ADMIN_OPTION,fake.GRANTABLE
ORDER BY fake.GRANTEE;
By removing and adding comments from the filters, we can see specific permissions for all users or for specific schemas (OWNERS).
