CODEWITHSUNDEEP
mysql
Erwan
Erwan

Reputation: 1073

Get mysql.user unique id?

In order to record the author credentials of a database item operation (such as creation or update operations), I am using the following architecture:

CREATE TABLE Items
(id integer not null primary key auto_increment
, Item_data varchar(50) not null
, Item_lastmodified_author_name varchar(50) not null
, Item_lastmodified_author_host varchar(100) not null
, foreign key (Item_lastmodified_author_name) references mysql.user (User)
, foreign key (Item_lastmodified_author_host) references mysql.user (Host)
);

Is it not possible to make this easier with a unique mysql.user id reference ?

One MySQL server is used for 3 teams. Each team has its own database. As the MySQL queries will be done from the client side.I don't want a user, member of one team, being able to change anything in another team database.

Am I doing it the right way ?

Upvotes: 1

Views: 72

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562811

No. Your foreign keys won't work the way you think for several reasons:

  • The mysql.user table is MyISAM, not InnoDB table in current versions of MySQL. Foreign keys are not supported in MyISAM.
  • You misunderstand the syntax for a multi-column foreign key. You wouldn't make a separate foreign key to each column, you'd make one foreign key comprised of both columns, referencing the primary key of the target table.
  • Even if you could make a foreign key to mysql.user, that would allow any team to reference any user in that table, not just their own user.

Why not just restrict the privileges of each team's user?

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, 
  EXECUTE ON `team1`.* TO 'team1user'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, 
  EXECUTE ON `team2`.* TO 'team2user'@'%';

Etc.

Note that they may need other privileges, but you should NOT grant them SUPER privilege, or SHUTDOWN or some others. :-)

Re your comment:

MySQL doesn't have any concept of data being "owned" by a particular user. Users have privileges to read or change data, but they don't own it.

You can use columns to store data values that look like user & host, but how would you make sure your teams can't set those values to any value they want? You could design a trigger to do that, but the team could modify the trigger.

Anyway, why would you record the user & host in the table, given that you would already grant privileges so that only the proper team has access to their respective database?

Upvotes: 1

Related Questions