5

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

  • 1
    So your question is why do the GUI doubles the rows or what? You resolved your problem by granting VIEW DEFINITION,so what is your question? – sepupic Jul 13 '17 at 14:09
  • @sepupic Thanks for your helpful remark, you earned an upvote. I was not precise enough, forgive me. I edited my question regarding that. The problem was not solved although I have set the - in my opinion - necessary permissions. That the SELECT and VIEW DEFINITION are duplicated in the config table is a mere side effect but not the problem itself. – Bruder Lustig Jul 13 '17 at 14:25
  • 1
    Your tangential question can be answered at this other DBA.SE question. – LowlyDBA - John McCall Jul 13 '17 at 14:45
3

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]

| improve this answer | |
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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.