Authentication And Authorization For Different Tables

I am having a three tables in my project which are listed below.

  • admins

  • users

  • staffs

I need my system to authenticate by the three tables username and password and based on which table it uses, I have to set the authorization role as admin, user, staff respectively.

Isn’t it better to use just one table with extra field in which the role is stored?

create a view like below:(sample for mysql)




CREATE VIEW <anyname you want> AS

SELECT username,password,"ADMIN" as role from admins

UNION

SELECT username,password,"USER" as role from users

UNION

SELECT username,password,"STAFF" as role from staffs



Then authenticate your system against the view.

Hi guys thanks,

           Client have designed the database. I have tried this. it works , but not the best way to do it. 

In UserIdentity.php and in Authenticate method


$record=Users::model()->findByAttributes(array('username'=>$this->username));

		

if(!isset($record->id))

$record=Admins::model()->findByAttributes(array('username'=>$this->username));

		

if(!isset($record->id))

$record=Staffs::model()->findByAttributes(array('username'=>$this->username));

		

if($record===null)

$this->errorCode=self::ERROR_USERNAME_INVALID;

else if($record->password!==$this->password)

$this->errorCode=self::ERROR_PASSWORD_INVALID;

else

{

 $this->_id=$record->id;

 $this->errorCode=self::ERROR_NONE;

}

return !$this->errorCode;