6

I have full admin rights on our database and hence can query and see view definitions. I want however query the views with a read-only user in a JDBC Jenkins job magic. The problem: Unlike my admin-user, the read-only user does not see the code/definitions of a view.

This query give me all the view definitions and the meta data I need for all views when I act as Admin:

SELECT name AS VIEW_NAME,
definition,
create_date,
modify_date  
FROM [my_database].[sys].[all_views]
JOIN [my_database].[sys].[sql_modules]
ON [my_database].[sys].[all_views].object_id = [my_database].[sys].[sql_modules].object_id

As a result when executing the query as admin I get entries like:

name        | definition        | create_date | modify_date 
sample_view | SELECT * FROM bla | 01.01.2017 | 02.01.2017

However, not so much when I do it with my read-only user, I get

 name        | definition        | create_date | modify_date 
 sample_view | null              | 01.01.2017  | 02.01.2017

Here you can see my permission configuration for the read only user. Although I have granted him the necessary permission, the view definitions are not visible to the user in a result set.

It is furthermore really strange that after allowing the user to do SELECT and VIEW definition statements and saving the config, a second entry for SELECT and VIEW DEFINITION was added to the config table.

Read_only_user_permission

Bruder Lustig
  • 213
  • 1
  • 3
  • 9

2 Answers2

4

Simply granting VIEW DEFINITION and SELECT permissions on INFORMATION_SCHEMA and sys schema wont give you rights to see the definition of the view.

Permissions that you added will provide you information about all objects in sys schema and information_schema.

I suppose you want to see the view definition which are created in some other schema, in which case you would have to provide the user withVIEW DEFINITION to that particular schema.

Such as : GRANT VIEW DEFINITION ON SCHEMA::SchemaWhereViewsAreCreated TO [YourUser]

S4V1N
  • 1,084
  • 6
  • 12
2

You should grant VIEW DEFINITION directly on the view(s) you are interested in, in order to permit your user to see its(their) definition like this:

grant view definition on object::sch.vw_MyView to MyUser

You should not give a permission on the schema, it will violate the principle of least privilege because in this case your user will be able to see all that schema's object definitions, such as functions/stored procedures

sepupic
  • 11,267
  • 18
  • 27