Contents
How do you find the tables a user has access to in Oracle?
To determine which users have direct grant access to a table we’ll use the DBA_TAB_PRIVS view: SELECT * FROM DBA_TAB_PRIVS; You can check the official documentation for more information about the columns returned from this query, but the critical columns are: GRANTEE is the name of the user with granted access.
How do I find out what tables are in a database?
Then issue one of the following SQL statement:
- Show all tables owned by the current user: SELECT table_name FROM user_tables;
- Show all tables in the current database: SELECT table_name FROM dba_tables;
- Show all tables that are accessible by the current user:
How to find which tables and views a user has access to?
– Database Administrators Stack Exchange How to find which tables and views a user has access to? We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID. Over the years we have granted back tables and views one at a time based on user need.
How can I find out who has access to a database?
One difficult problem. A Windows user might have access to a database through a login based on a Windows group. So you will need to look for all groups that a user is a member of. Or (the other way around) find all the groups that are users in the databases, and then figure out who is a member of each group.
How to find the last user access to a table?
We can watch these columns and figure out when the table was used last. Which ever value among the last_user_lookup, last_user_scan and last_user_seek is latest is the last user access of the table.
How to find which objects a user has access to?
You can use the function fn_my_permissions and impersonate the user to see which objects they have access to. You may want to expand the types to procedures and the various types of functions… Only tested on compat level 100+; you may need to structure that slightly differently in older compatibility modes (see the first example here ).