0

I have created Product entity in MDS. It's having the following values: Bike 1 ABC Car 2 XYZ Cycle 3 RRR

owner XYZ can change the record of RRR. But if owner XYZ or any other owner in this entity tried to update the ABC's record, access should be denied. That means, no one should have the permission to change the records which are entered by ABC. for this I have executed following :

CREATE TRIGGER mdm.product_readonly
ON mdm.tbl_1034_1215_en 
AFTER UPDATE, INSERT AS
If exists (system_user!='ABC') 
and EXISTS (SELECT * FROM deleted a, inserted b, mdm.vw_product c
WHERE a.code=c.code
or b.code=c.code
And c.owner = 'ABC')
BEGIN
   ROLLBACK TRANSACTION
   RAISERROR ('Attempt to change a read-only row', 16, 1)
   RETURN
END

After executing this, If I trying to update the record as ABC owner I can update all the records. If I try to update as other than ABC owner I couldnt update the ABC record. But from MDS I couldnt update any of the record. It showing like Database error.

How can we achieve this. Please help me out in this regard. Thanks!

user3607647
  • 51
  • 2
  • 14

1 Answers1

0

Please have a look on how to set permissions for MDS.

http://msdn.microsoft.com/en-us/library/hh231026.aspx

You can set permissions for users and/or groups.

I would recommend to assign permissions using AD groups as this is much easier to mantain on the long run.

If you need a more complex permission concept which is row based you can set permissions using derived hierarchies.

Permission settings are also stored in tables of MDSDB database. If you need to set permissions for many users I would recommend using code to copy permissiond. I can provide some snippets if needed.

Narti
  • 181
  • 7
  • Thank you for the useful information. I have figured out trigger to do the same: CREATE TRIGGER tripro on mytbl AFTER UPDATE, INSERT AS IF EXISTS (SELECT * FROM deleted a, inserted b,mdm.vw_product c WHERE a.code=c.code or b.code=c.code And c.owner = 'XYZ' ) BEGIN ROLLBACK TRANSACTION RAISERROR ('Attempt to change a read-only row', 16, 1) RETURN END; It is some what working as expected. Still I'm working on it. But when other user trying to edit, the error message came like "data base error". How I can display the message which is given inside RAISERROR in MDS UI. Please help me out. Thanks! – user3607647 Jun 18 '14 at 19:24