Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2009
    Posts
    66

    Permission - Role Based data model

    I'm trying to design a role based permission data model. I'm not sure how vb bulletin or phpbb does it (anyone know?).

    I have a roles table - Guests, Members, Forum Moderators, Forum Administrators, System Administrators

    Account Permissions: approve accounts, lock accounts, ban accounts, unlock accounts etc
    System Permissions: change system settings
    Forum Permissions (based on forum) - create topics, post polls etc
    Blogs - create blog, post comment etc

    Members can have multiple roles (highest permission overrides).

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Moved to Database Concepts & Design
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    phpbb is free - you can download that (or any one of a lot of open source forum software) to see exactly how they do it.

    Other than that, there doesn't appear to be a question in there....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    As a general rule of thumb, permission systems apply to groups and users. Permissions are required to performa certain (often any) actions. Permissions come in two flavors, permit and deny. The presence of any deny ends the security check, one deny trumps any permit. Otherwise, you check to see if the user or any groiup that the user belongs to has a suitable permission to determine if they can perform the requested action.

    Different systems implement the security model differently, but the general principles seem to be the same everywhere.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2009
    Posts
    66
    phpbb and most other forum systems are based off the eav model for access role permissions.

    I'm also going to use the eav model. I'm using all stored procedures and checking the permissions in each stored procedure.

    For an example, in the stored procedure "NewMember", I check to make sure that the current member has permissions to create new members etc.

    The permissions table has about 200 rows (forum_create, forum_edit, group_create etc), the groups table has about 100K rows (but 10 of them are system groups like administrators, members that has actual permissions defined).

    This is the actual "HasPermission" query that fires everytime an "action" occurs inside a SP: (any optimizations?)

    Code:
    ALTER PROCEDURE [dbo].[HasPermission] 
    	@mem_id INT,
    	@perm_type VARCHAR(25),
    	@perm_name VARCHAR(25)
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	SELECT 1 FROM dbo.group_permissions a WHERE  
    		EXISTS
    		(
    			SELECT group_id FROM dbo.group_members b WHERE b.mem_id = @mem_id AND
    			b.group_id =a.group_id 
    		) AND
    		EXISTS
    		(
    			SELECT perm_id FROM dbo.permissions c WHERE c.perm_type = @perm_type AND c.perm_name = @perm_name
    			AND c.perm_id = a.perm_id
    		);
    Also, what is the long term impact of using this model?
    Last edited by sqlguru; 06-29-09 at 15:14.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've only glanced at phpbb, but it isn't EAV.

    Based on your query, your design isn't EAV either.

    Your query looks fine. The only thing is that it implies that you can have multiple permissions of the same name but different types - is that correct? Also, I would return a status code rather than a result set (more efficient).

    Your sproc looks like it should handle all permission checking - so why
    Quote Originally Posted by sqlguru
    in the stored procedure "NewMember", I check to make sure that the current member has permissions to create new members etc.
    ? You don't need or even want one procedure for each permission.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to give you another option to consider - you may decide you don't like it and dismiss it - but permissions are also often stored as bitmaps.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by pootle flump
    Just to give you another option to consider - you may decide you don't like it and dismiss it - but permissions are also often stored as bitmaps.

    Can you give an example of this (or a link)?

  9. #9
    Join Date
    Jun 2009
    Posts
    66
    Well, take a social networking application as an example. The forums have permissions that depend on "each" forum AND they can inherit the parent forum permissions etc.
    Then there are "member" based stuff like "change username", "leave comments", "enable blog"
    Then there are "system" based stuff like "approve members", "change sys settings".

    I have all of these permissions in a permissions table with a perm_type (forums, system, members etc) and perm_name (leave comment, enable blog, can post).

    The thing is that inside the stored procedures (say NewMember), it checks to make sure that the currently logged in user has proper permissions to create a new member (it calls HasPermission 'members', 'can_create', @currentuserid). The application layer also calls HasPermission. Like, if there is a "create post" button, if the current user does not have permission to post....the application checks the permission and hides the button. The stored procedure for creating the post also checks the permission.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - I understand. I thought you meant a sproc for each permission, not each action.

    Quote Originally Posted by sqlguru
    Can you give an example of this (or a link)?
    The below essentially does this, just imagine it was a column in the Users table rather than a function
    PERMISSIONS (Transact-SQL)
    Where you stand on atomicity of column values may influence your thoughts on this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by sqlguru
    Can you give an example of this (or a link)?
    Quick SQL Server example
    Code:
    /*
    2  = can edit posts
    4  = can move posts
    8  = can delete posts
    16 = can ban users
    */
    
    DECLARE @permissions int
    
    DECLARE @edit_posts   bit
          , @move_posts   bit
          , @delete_posts bit
          , @ban_users    bit
          
    SET @edit_posts   = 0
    SET @move_posts   = 1
    SET @delete_posts = 0
    SET @ban_users    = 1
    
    SET @permissions = Power(2 * @edit_posts  , 1)
                     + Power(2 * @move_posts  , 2)
                     + Power(2 * @delete_posts, 3)
                     + Power(2 * @ban_users   , 4)
    
    PRINT @permissions 
    
    IF 2  & @permissions = 2  PRINT 'Can Edit Posts' 
    IF 4  & @permissions = 4  PRINT 'Can Move Posts' 
    IF 8  & @permissions = 8  PRINT 'Can Delete Posts' 
    IF 16 & @permissions = 16 PRINT 'Can Ban Users'
    George
    Home | Blog

  12. #12
    Join Date
    Jun 2009
    Posts
    66
    I don't think that will work.

    How about this model:
    Fine Grained Role Based Access Control (RBAC) system - SQL Recipes a FREE cookbook of SQL queries and examples

    I haven't built an RBAC system before (usually use simpler ACL system) but does the above model have any performance implications?

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sqlguru
    I don't think that will work.
    ...because?

    Quote Originally Posted by sqlguru
    How about this model:
    Fine Grained Role Based Access Control (RBAC) system - SQL Recipes a FREE cookbook of SQL queries and examples

    I haven't built an RBAC system before (usually use simpler ACL system) but does the above model have any performance implications?
    Not used. More importantly, does this meet requirements your current model does not? Because this is driven by a business need, not a technical one. It is your model just extended with more granular controls AFAICT.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •