Grant access to procedure and DENY/GRANT to view

Grant access to procedure and DENY/GRANT to view

Post by X_Aki Nieminen_ » Wed, 04 Apr 2001 00:23:23



[SQL Server 7.0 / Windows 2000 Server]
I have received an old SQL Server 6.5 application, which has a very
interesting user security model. Every data handling is done through stored
procedures. Users cannot access tables directly. Several views are created
to display given category.

Update procedure makes a foodoo update to given view and terminates if error
is encountered.  I have a grant.sql scriptfile, which basically makes the
following commands. It grants access to given procedures and category views.

1) Grant access to given procedures:
GRANT EXECUTE on prSelectData TO gReader1
GRANT EXECUTE on prUpateData TO gEditorS, gEditorB

2) Grant access to given view:
GRANT UPDATE ON vDataB to gEditorB
GRANT UPDATE On vDataS to gEditorS

Now:
I am told, that gEditorS can only edit category "S", but not category "B"
records. This due to a granted access to only an appropriate category view.
Update procedure makes a foodoo attemp to update appropriate view, before
continue updating the table.

-> This is not what I get as a result. If I grant execute to update
procedure, then user can update any records. It seems, that procedure has
"internally db_owner access" to all objects. I don't have to grant access to
a table object. Procedure can still update the table as long as user has
rights to execute this procedure.

Procedure rights is fine and should do what ever it attempts to do. It is
good, that I don't have to explicitly grant access to tables used by this
procedure.

BUT: How can I follow the initial plan to block updates only to certain
category?
gEditorB should update only B category.
gEditorS should updated only S category.

=====

prSelectData:
Select * from tData

=====

prUpdateData(int a, category c):
If (c = 'B') Begin
   Execute ('update vDataB set id = 1 where 1 = 0')

End Else If (c = 'S') Begin
   Execute ('update vDataS set id = 1 where 1 = 0')

End Else Begin
   RETURN 0
END
Update tData Set id = a Where category = c

=====

vDataB:
Select * from tData Where category = 'B'

=====

vDataS:
Select * from tData Where category = 'S'

=====

 
 
 

Grant access to procedure and DENY/GRANT to view

Post by X_Aki Nieminen_ » Thu, 05 Apr 2001 18:10:48


False alarm. This indeed works as expected. Don't know what I did wrong
previously, but now it works.
Users can only edit categories given by appropriate view grants.

X_Aki Nieminen_X kirjoitti viestiss?

Quote:>[SQL Server 7.0 / Windows 2000 Server]
>I have received an old SQL Server 6.5 application, which has a very
>interesting user security model. Every data handling is done through stored
>procedures. Users cannot access tables directly. Several views are created
>to display given category.

>Update procedure makes a foodoo update to given view and terminates if
error
>is encountered.  I have a grant.sql scriptfile, which basically makes the
>following commands. It grants access to given procedures and category
views.

>1) Grant access to given procedures:
>GRANT EXECUTE on prSelectData TO gReader1
>GRANT EXECUTE on prUpateData TO gEditorS, gEditorB

>2) Grant access to given view:
>GRANT UPDATE ON vDataB to gEditorB
>GRANT UPDATE On vDataS to gEditorS


 
 
 

1. Granting access to views

Question:

For ease of use I intend to create a datatabase that contains only views.
These views are based on complex joins from tables that are spread over
several databases and  some are joined to tables from linked servers. Users
will use MSAccess  to view these views.  How can we grant them SELECT
permission to these views without giving them access to the source
databases?

Regards

Carlos

2. SQL 7 cannot see local disk drive anymore

3. Difference between grant to user and grant to role

4. Relational Object Modeler Question

5. direct grant vs. grant via role

6. DATE

7. Granting access to tables and views

8. QRRichtText

9. create proc grant execute with grant

10. grant update with grant option

11. permission prob: granted, but still denied

12. DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string

13. permission prob: granted, but still denied