none
How to use Column Level Security in c# file RRS feed

Answers

  • Hi Vipul Langalia,

    Welcome to MSDN Forum.

    If the database table has permission limitation to access the columns with different users. What we need to do is passing different connection string to DataContext's constructor to create different instance. @TinMgAye has provided the code, but it is Entity Framework. Here's a discussion about how to change the connection string at runtime with Linq to SQL, please refer here.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, June 20, 2012 8:43 AM
    Moderator

All replies

  • Hi

    I have following Table 

    MyTable  <- Table Name

    It has following columns

    Id int not null pk

    Name Varchar(50)

    Notes Varchar(50)

    Surname Varchar(50)

    Now I set deny permiission on Surname & Note Column for select

    Now in Cs file

    Dataclass1DataContext dc = new Dataclass1DataContext ();

    dc.MyTables.First();

    When I try this it gives error that it has not permission for selection

    I want it to be work 

    I f user try this then it should not give error 

    It should show  data  with columns on which user has select permission

    Can you please help me for it?

    Here I can not do

    Dc.MyTables.First();

    It will give error because of permission problem.

    I f I do select new like

    from p in dc.MyTables select new {id=p.Id,Name=p.Name};

    It will work But it will be data of anonimaqs type

    So it will not do process written in Partial class of MyTable

    So is there any way to solve it?

    In short 

    I have given column Level security in sql

    Now I want  to use it in c# file

    If I try to do  dc.Table1.Select() or Dc.Table1.First() Dc.Table1.Any() then it gives runtime error  regarding permission issues

    I want to handle this things.

    can you please help me for it?

    Meance 
    I have given Select permission for userA on x & Y column
    UserB ha selection permission on Z & R column

    Now in c# file I want to handle this
    I want such a michanism that
    1) I got original table type object
    2) some kind of mechanism that will give columns for selection based on user & his permissions
        Automaticaly

     I know how to create Column level Security in sql server 

    But I have problem when try to do dc.Table1.Select() or Dc.Table1.First() Dc.Table1.Any() 

    then it is giving error

    I want to get Table type object not anonimas type object

    second

    at coding side how to do mechanism that based on permission columns will be shown to user 

    Can you please help me for it? 

    • Merged by Bob Shen Wednesday, June 20, 2012 7:56 AM duplicate
    Tuesday, June 19, 2012 3:38 AM
  • Try this forum for your question

    Data providers Linq to SQL

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/threads


    Success
    Cor

    Tuesday, June 19, 2012 6:30 AM
  • I do not think Entity Framework provide this kind of permission control over entities. What you can do is create a logic layer and switch the connection string for each user. You can form a dynamic connection string this way;

    EntityConnectionStringBuilder entityBuilder =new EntityConnectionStringBuilder();
    entityBuilder.Provider = "provider";
    entityBuilder.ProviderConnectionString = "ConnectionString with diff user id and pwd";
    
    EntityContext(entityBuilder.ToString());
    
    Hope this helps.

    • Proposed as answer by TinMgAye Tuesday, June 19, 2012 5:23 PM
    Tuesday, June 19, 2012 3:51 PM
  • Hi Vipul Langalia,

    Welcome to MSDN Forum.

    If the database table has permission limitation to access the columns with different users. What we need to do is passing different connection string to DataContext's constructor to create different instance. @TinMgAye has provided the code, but it is Entity Framework. Here's a discussion about how to change the connection string at runtime with Linq to SQL, please refer here.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, June 20, 2012 8:43 AM
    Moderator
  • Hi

    I am creating connection string for any user who login

    But my question is If user A login

    then how can I code in generaliz way that it can do select query based on current user's permissions?

    Thursday, July 5, 2012 11:57 AM
  • I think you can only achieve this by having preference table for each uses. something similar to User<->Role kind of things you need to keep as reference.
    Thursday, July 5, 2012 3:22 PM