Menu
I have a series of stored procedures that select data from a db. I have a role (cctc_reader) that has execute permissions granted on the procedures. One of the procedure calls another stored procedure called recControl_system_option which in turn queries Information_schema.columns.
The problem is that in this proc the query
select column_name from information_schema.columns where table_name = 'recControl_manager'
does not return any records. cctc_reader has grant permissions on:
recControl_system_optionso in theory this should work. I have no problems when run under dbo.
If I grant db_datareader to cctc_reader the query is fine, but I don't want to grant reader permissions to all tables (hence why I used stored procs). I've tried granting Select permissions on Information_schema in the Master db as suggested in some articles, but still can't get this to work.
Any suggestions?
945
Click the Database Access tab. In the list at the top, in the Permit column, select the check box for the database to which you want to assign the owner role for the CES administrative account. In the Permit in Database Role list, select db_owner. Click OK.
To grant permissions for the user, switch to the Object Permissions tab. In the Objects block, select the database object on which you want to grant privileges. In the Available Privileges block, select the permissions to be assigned and click Save.
The U1 user has the CREATE VIEW permission on the database and the SELECT permission on the S1 schema. Therefore, the U1 user can create a view in the S1 schema to query data from the denied object T1, and then access the denied object T1 by using the view.
Objects metadata visibility is subject to the VIEW DEFINITION permission:
GRANT VIEW DEFINITION ON ... TO cctc_reader;
The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.
The right securable to grant permission to depends on your scenario. It could be the dbo or some other schema, it could be the database itself, it could be individual tables. If I was in your place, I'd code sign the recControl_system_option procedure and I'd grant VIEW ANY DEFINITION on the signature at server level, a much better and secure way that using roles and granting permission on roles. See Signing an activated procedure for an example of how to sign a procedure and grant a server level permission on the signature.
58
As Remus mentioned, metadata visbility affects data returned when querying system tables and views. If you have no rights on a securable (object, login, whatever) it won't be visible.
Depending on your situation, you would allow the internal call to have EXECUTE AS OWNER, or wrap Information_schema.columns in a udf that as EXECUTE AS OWNER
We use this technique where we query metadata.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
