Skip to Main Content
  • Questions
  • Finding privileges that allows object SELECT access in the absence of granted privileges

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Glenn.

Asked: October 15, 2024 - 5:42 pm UTC

Last updated: October 16, 2024 - 2:42 pm UTC

Version: 19.0.0.0.0

Viewed 1000+ times

You Asked

I have a materialized view in my Oracle database (v$instance.version=19.0.0.0.0) from which a user is able to select records. However, I can't find any permissions that would give the user that ability.

Using the following queries, I have checked for privileges, but I haven't found any. Where else should/could I look?

In this post, I've changed the values for anonymity.
Username = 'ADAM'
MView Owner = 'SCHEMANAME'
MView Name = 'MVIEWNAME'

-- Check for system privileges
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='ADAM';

/* There are no privileges, such as SELECT ANY TABLE, that would grant this access */

-- Check for direct object privileges
SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME='MVIEWNAME' AND GRANTEE='ADAM';

/* There are no direct privs granted for this user */

-- Check for direct object privileges for roles
SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME='MVIEWNAME' AND GRANTEE IN (SELECT ROLE FROM DBA_ROLES);

/* There are no direct privs granted for any roles. This rules out ADAM being a member of a role with permission */

Where else could ADAM's access be coming from, and how would I revoke it?

Thanks for your help.

and Chris said...

A couple of thoughts:

Have you checked if the user has been granted a role with SELECT ANY?

select * from dba_sys_privs 
where grantee in ( 
  select granted_role
  from   dba_role_privs 
  where  grantee = 'ADAM' 
);


Could they have the READ ANY privilege?

Is this answer out of date? If it is, please let us know via a Comment