I have a table tbl_accounts with fields account_id,account_name,username.
Another table tbl_account_share with fields share_id,account_id,shared_with,share_permission to store the account_id that are shared with other users .How do I assign the users in the shared_with to access the records in tbl_accounts?
Kinldy guide.
Your post is still not clear enough, since you don’t include the fields type and some example records in it. You should post your tables schema (CREATE TABLE …) including some records (INSERT INTO …) so others could help you straightforward.
/*Table structure for table `tbl_accounts` */
CREATE TABLE `tbl_accounts` (
`account_id` int(11) NOT NULL AUTO_INCREMENT,
`account_name` varchar(50) DEFAULT NULL,
`username` varchar(50) DEFAULT NULL,
PRIMARY KEY (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*Data for the table `tbl_accounts` */
insert into `tbl_accounts`(`account_id`,`account_name`,`username`) values
(1,'CASH-USER1','user1'),
(2,'CASH-USER2','user2'),
(3,'BANK-USER1','user1');
/*Table structure for table `tbl_account_share` */
CREATE TABLE `tbl_account_share` (
`share_id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) DEFAULT NULL,
`shared_with` varchar(50) DEFAULT NULL,
`share_permission` varchar(50) DEFAULT NULL,
PRIMARY KEY (`share_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*Data for the table `tbl_account_share` */
insert into `tbl_account_share`(`share_id`,`account_id`,`shared_with`,`share_permission`) values
(1,1,'user2','F');
in addition to the above i have another table tbl_transactions which stores the transactions pertaining to the relevant accounts. tbl_accounts is the master table and account_id is the foreign key in the tbl_transactions. user2 should have access to the transactions pertaining to account 1
CREATE TABLE `tbl_transactions` (
`trans_id` int(11) NOT NULL AUTO_INCREMENT,
`trans_date` datetime DEFAULT current_timestamp(),
`trans_remarks` varchar(50) DEFAULT NULL,
`account_id` int(11) DEFAULT NULL,
`amount` float DEFAULT NULL,
`username` varchar(50) DEFAULT NULL,
PRIMARY KEY (`trans_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*Data for the table `tbl_transactions` */
insert into `tbl_transactions`(`trans_id`,`trans_date`,`trans_remarks`,`account_id`,`amount`,`username`) values
(1,'2021-12-15 04:57:22','TEST1',1,100,'user1'),
(2,'2021-12-15 04:57:22','TEST2',2,50,'user2'),
(3,'2021-12-15 04:57:45','TEST3',3,10,'user1');
There are three accounts in tbl_accounts. account with id 1 and 3 belongs to user1 and account with id 2 belongs to user2username is the userid field set in the Advanced Security. Only user1 can access the account with id 1 and 3.I want to share the account with account id 1 belonging to user1 to user2 with restricted permissions which is defined in share_permission. However account id 3 should not be accessible to user2.
mdfaisalpapa wrote:
I want to share the account with account id 1 belonging to user1 to user2 with restricted permissions which is defined in share_permission. >
You may use UserID_Loaded server event, so the user can access more or less records that he/she can access by its originally loaded User IDsFor more info, please read Server Events → Global → All Pages → UserID_Loadedmdfaisalpapa wrote:
However account id 3 should not be accessible to user2.
You may use Recordset_Selecting server event to restrict access to account id 3. In other words, since this is a special condition, then you may also add your filter in that event in order to exclude account id 3.For more info, please read Server Events → Table-Specific → Common → Recordset_Selecting