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.
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?