-3

I need help with SQL Information_Schema. I need to find: Tables to which all users have the right to write queries. Maybe someone have some ideas?

Dominika Barilovich
  • 1
  • 1
  • 2
    Add your database you are using.. – dwir182 Oct 22 '18 at 07:52
  • 1
    I believe that all you're given access to via the information_schema is information about *your own permissions* (that you are granted or have granted to others). I don't think you're guaranteed information about other users. – Damien_The_Unbeliever Oct 22 '18 at 07:57
  • Have you checked this? https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database – Faran Saleem Oct 22 '18 at 07:59
  • It's so complicated there... I just need few lines of code :( – Dominika Barilovich Oct 22 '18 at 08:03
  • A regular user has no right to list all other users. – jarlh Oct 22 '18 at 08:10
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product and not all database products support `information_schema`. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  Oct 22 '18 at 08:12
  • Check if `PUBLIC` has `SELECT` privilege, something like `SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE = 'PUBLIC'` – jarlh Oct 22 '18 at 08:13

2 Answers2

0

Try this one,

    select sys.schemas.name 'Schema'
   , sys.objects.name Object
   , sys.database_principals.name username
   , sys.database_permissions.type permissions_type
   ,     sys.database_permissions.permission_name
   ,      sys.database_permissions.state permission_state
   ,     sys.database_permissions.state_desc
   ,     state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS 
from sys.database_permissions join sys.objects on sys.database_permissions.major_id =      sys.objects.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id join sys.database_principals on sys.database_permissions.grantee_principal_id =      sys.database_principals.principal_id order by 1, 2, 3, 5
Faran Saleem
  • 404
  • 1
  • 7
  • 31
  • The question is currently just tagged [tag:sql], which means that any answers (in the absence of an additional tag) should follow the ANSI Standard and not contain product-specific code. The `INFORMATION_SCHEMA` views mentioned in the question are the only thing close to a standard for metadata across databases. This answer appears to be using product-specific metadata tables/views. – Damien_The_Unbeliever Oct 22 '18 at 08:26
0

Here is an answer :)

SELECT table_name AS "Table name", privilege_type AS "Permission", grantee AS "User"
FROM information_schema.table_privileges
WHERE privilege_type = 'SELECT' 
  AND grantee = 'PUBLIC';
Community
  • 1
  • 1
Dominika Barilovich
  • 1
  • 1