{"id":5142,"date":"2024-11-01T07:00:00","date_gmt":"2024-11-01T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=5142"},"modified":"2025-02-01T18:03:12","modified_gmt":"2025-02-01T15:03:12","slug":"pos-vriskoyme-ti-dikaiomata-echei-enas","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-vriskoyme-ti-dikaiomata-echei-enas\/","title":{"rendered":"How do we find what permissions a user has in an Oracle Database?"},"content":{"rendered":"

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 the right to create users. At first we will see one by one the views that exist with this information by simply looking at the query and then we will see some queries that I have created that we can see all the information together very easily.<\/p>\n\n\n\n

Before we start looking at how we find the rights we should know that permissions are not only given to users but also to user roles<\/strong>. User roles are a group of permissions that can include permissions on objects such as tables as well as system permissions such as the right to create a new user. We use roles so that we don't have to give each user the permissions we want each time we create a user, but we can simply add that user to a role that already has them. We should also know that there are some roles with system permissions that are created by default, but we can also create our own. Finally we do not forget that each user has full rights to their objects<\/strong>, e.g. the user Scott <\/code>can do whatever he wants with the data in his table Scott.Dept<\/code>, for this reason, in the views we will see, the permissions on the objects of the same user are not displayed.<\/p>\n\n\n\n

<\/p>\n\n\n\n

How do we see which roles a user belongs to?<\/h2>\n\n\n\n

For our example, we want to check if the user belongs to a role, to see this we run the following query from the view dba_role_privs<\/code>:<\/p>\n\n\n\n

select * from DBA_ROLE_PRIVS where GRANTEE = 'SCOTT'<\/pre>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
So we see that the user (GRANTEE<\/code>) has the two roles connect and resource (GRANTED_ROLE<\/code>).<\/p>\n\n\n\n
<\/p>\n\n\n\n
How do we see what permissions a user has and in which tables?<\/h2>\n\n\n\n
To see what permissions the user has on objects \/ tables, we run the following query from the view dba_tab_privs<\/code>:<\/p>\n\n\n\n
select * from DBA_TAB_PRIVS where GRANTEE = 'SCOTT';<\/pre>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
To understand the above where GRANTEE <\/code>is the username, OWNER <\/code>is the name of the schema to which the object belongs, TABLE_NAME <\/code>the name of the object \/ table, PRIVILEGE <\/code>the right he has, GRANTABLE <\/code>if he can give it to someone else and that's it TYPE<\/code> What type of object is it, e.g. a table?<\/p>\n\n\n\n
But what if this 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 would have to run the following query that brings the permissions for the roles he belongs to:<\/p>\n\n\n\n
select * from DBA_SYS_PRIVS WHERE 1=1\nAND (GRANTEE = 'SCOTT' OR GRANTEE IN (SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'SCOTT')); <\/pre>\n\n\n\n
<\/p>\n\n\n\n
How do we see in a table which users have permissions and what they are? <\/h2>\n\n\n\n
Correspondingly, to see which users or roles and what permissions they have in a table, we run the following query:<\/p>\n\n\n\n
select * from DBA_TAB_PRIVS where TABLE_NAME = 'DEPT';<\/pre>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
How do we see what system permissions a user has?<\/h2>\n\n\n\n
To see what system permissions the user has, we run the following query from the view dba_sys_privs<\/code>:<\/p>\n\n\n\n
select * from DBA_SYS_PRIVS where GRANTEE = 'SCOTT'<\/pre>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
However, to get the system permissions that the user and role may have, we run the following query:<\/p>\n\n\n\n
select * from DBA_SYS_PRIVS WHERE 1=1\nAND (GRANTEE = 'SCOTT' OR GRANTEE IN (SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'SCOTT')); <\/pre>\n\n\n\n
<\/p>\n\n\n\n
How do we see overall what rights a user has?<\/h2>\n\n\n\n
Let's now move on to the most useful queries in the article, combining what we saw before.<\/p>\n\n\n\n
With the following query that I have written, we can find all the permissions a user has, whether they are systemic or on objects, as well as the roles they may have:<\/p>\n\n\n\n
select \nfake.OWNER,fake.GRANTEE, fake.TABLE_NAME, fake.PRIVILEGE,\ncase \n  WHEN fake.OWNER is null then 'grant '||fake.privilege||' to '||grantee||';' \n  ELSE 'grant '||fake.privilege||' on '||fake.owner||'.'||fake.table_name||' to '||grantee||';'\nend \"GRANT STATEMENTS\"\nfrom(\nSELECT OWNER,GRANTEE, TABLE_NAME ,PRIVILEGE FROM DBA_TAB_PRIVS  \nunion all\nSELECT null,GRANTEE, ' IT IS A SYSTEM PRIV', PRIVILEGE FROM DBA_SYS_PRIVS \nunion all\nSELECT null, GRANTEE, ' IT IS A ROLE', GRANTED_ROLE from DBA_ROLE_PRIVS \n) fake\nwhere 1=1\n--and fake.OWNER='HR'\nand GRANTEE='SCOTT'\norder by fake.TABLE_NAME;\n<\/pre>\n\n\n\n
\"\"
05<\/figcaption><\/figure>\n\n\n\n
As we 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 under another user.<\/p>\n\n\n\n
<\/p>\n\n\n\n
How do we see collectively what type of permissions a user has?<\/h2>\n\n\n\n
With the following query I have created, we can see in aggregate what type of permissions a user has and whether they can grant them to someone else. For example, we can see if a user has the right to update, insert into any objects in a schema, or if they have the right to create users:<\/p>\n\n\n\n
select fake.owner,fake.GRANTEE,fake.PRIVILEGE,fake.ADMIN_OPTION,fake.GRANTABLE \nfrom (\nselect owner,GRANTEE,PRIVILEGE,null as ADMIN_OPTION, GRANTABLE from dba_tab_privs\n--where OWNER = 'HR'\nunion all\nselect null,GRANTEE,PRIVILEGE, ADMIN_OPTION, null as GRANTABLE from dba_sys_privs \n) fake\nWHERE 1=1\n--and fake.PRIVILEGE in ('INSERT','DELETE','UPDATE','ALTER')\nand fake.GRANTEE='SCOTT' \nor GRANTEE IN(SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'SCOTT')\nGROUP BY fake.owner,fake.GRANTEE,fake.PRIVILEGE,fake.ADMIN_OPTION,fake.GRANTABLE\nORDER BY fake.GRANTEE;\n<\/pre>\n\n\n\n
\"\"
06<\/figcaption><\/figure>\n\n\n\n
By removing and adding comments from filters, we can see specific permissions for all users or for specific schemas (OWNERS<\/code>).<\/p>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n