Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Cool DB Concept/Design

    I like the way operating systems, etc. do their "groups" security. In other words, a user can be a member of zero or more groups, and an object may require membership in an one or more of those groups to use it. So, I could be a member of the RedHeadedUser group and the DullUser group. To access the DullUsers phone list, I must be a member of the DullUser Group. etc....


    Anybody have an implementation strategy for this setup, or know of a white paper on the topic so I can implement it in my little application? I think I understand how to structure it, but I'd love to read how others did it so I don't have to reinvent the wheel.

    Thanks

  2. #2
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Code:
    CREATE TABLE principal
    (
    	principal_id int IDENTITY (1,1) ,
    	username varchar(60)  NOT NULL ,
    	password varchar(128)  NOT NULL ,
    	locked_flag bit  NOT NULL 
    	CONSTRAINT df_principal_locked_flag
    		 DEFAULT  0,
    	enabled_flag bit  NOT NULL 
    	CONSTRAINT df_principal_enabled_flag
    		 DEFAULT  0,
    	login_attempt_count tinyint  NOT NULL ,
    	acct_expiration_date datetime  NULL ,
    	password_expiration_date datetime  NULL ,
    	created_date datetime  NOT NULL 
    	CONSTRAINT df_principal_created_date
    		 DEFAULT  GETDATE(),
    	updated_date datetime  NOT NULL 
    	CONSTRAINT df_principal_updated_date
    		 DEFAULT  GETDATE(),
    	CONSTRAINT PK_principal PRIMARY KEY (principal_id ASC)
    )
    go
    
    ALTER TABLE principal
    	ADD CONSTRAINT  UIX_principal_username UNIQUE (username  ASC)
    go
    
    CREATE TABLE user_group
    (
    	user_group_id int IDENTITY (1,1) ,
    	group_code varchar(30)  NOT NULL ,
    	display_name varchar(30)  NOT NULL ,
    	description varchar(80)  NULL ,
    	created_date datetime  NOT NULL 
    	CONSTRAINT df_user_group_created_date
    		 DEFAULT  GETDATE(),
    	CONSTRAINT PK_user_group PRIMARY KEY (user_group_id ASC)
    )
    go
    
    CREATE TABLE user_role
    (
    	user_role_id int IDENTITY (1,1) ,
    	role_code varchar(30)  NOT NULL ,
    	display_name varchar(30)  NOT NULL ,
    	description varchar(80)  NULL ,
    	created_date datetime  NOT NULL 
    	CONSTRAINT df_user_role_created_date
    		 DEFAULT  GETDATE(),
    	CONSTRAINT PK_user_role PRIMARY KEY (user_role_id ASC)
    )
    go
    
    CREATE TABLE user_group_role_xref
    (
    	user_group_id int  NOT NULL ,
    	user_role_id int  NOT NULL ,
    	CONSTRAINT PK_user_group_role_xref PRIMARY KEY (user_group_id ASC,user_role_id ASC),
    	CONSTRAINT  FK_user_group_role_xref_group FOREIGN KEY (user_group_id) REFERENCES user_group(user_group_id),
    CONSTRAINT  FK_user_group_role_xref_role FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id)
    )
    go
    
    CREATE TABLE principal_user_group_xref
    (
    	principal_id int  NOT NULL ,
    	user_group_id int  NOT NULL ,
    	CONSTRAINT PK_principal_user_group_xref PRIMARY KEY (principal_id ASC,user_group_id ASC),
    	CONSTRAINT  FK_principal_user_group_xref_principal FOREIGN KEY (principal_id) REFERENCES principal(principal_id),
    CONSTRAINT  FK_principal_user_group_xref_group FOREIGN KEY (user_group_id) REFERENCES user_group(user_group_id)
    )
    go
    Is that what you're looking to implement?

    -A

  3. #3
    Join Date
    Apr 2008
    Posts
    2

    Thanx

    Well, I wasn't expecting code, so much as strategy. Thanks for the help. I'll pop it into sql server and study it. My app is already built (in Access), and I'm gonna lay this on top. I use the NT logonid as the authentication key, so I need little in the way of logon stuff. It's fairly informal, but I need to tighten it up and make it more flexible at the same time.

    Thanks again!

    Jim

  4. #4
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    I know you weren't looking for code, but it was simpler than putting up a data model

    -A

Posting Permissions

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