Run SQL Profiler to identify who, where etc. You have various columns with hostname, ntusername etc that will allow you to see usage.
For example: is if just one client or user who notices? Are they running a different ADP?
The view call can be embedded in some Form properties too as the RecordSource for that form, or as the source for some control. It may not be in VBA. You'd have to write some VBA to iterate through the forms, controls and reports testing assorted properties.
List all users who have been assigned a particular role
-- Change 'DBA' to the required role
select * from dba_role_privs where granted_role = 'DBA'
List all roles given to a user
-- Change 'PHIL@ to the required user
select * from dba_role_privs where grantee = 'PHIL';
List all privileges given to a user
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html
List which tables a certain role gives SELECT access to?
-- Change 'DBA' to the required role.
select * from role_tab_privs where role='DBA' and privilege = 'SELECT';
List all tables a user can SELECT from?
--Change 'PHIL' to the required user
select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';
List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.
-- Change 'TABLENAME' below
select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;
Best Answer
Unquoted identifiers in SQL are implicitly converted to upper case, so the statement
create view remotetabletest
creates the view named'REMOTETABLETEST'
, not'remotetabletest'
.In other words, the following should return the expected result: