Menu
I have MySQL Database and have several tables in it. One, of course, is the users table for storing the username and passwords (which also has the rest of their information). For all the tables in the schema, I want to be able to grant users (or groups) permission to individual fields. Maybe an example will help:
There;'s a table called ACCOUNTS. In this table, the user fills out (and keeps up to date) all the data about their company (name, address, POC, etc). But I also want to have fields attached to this table that are read only for those users, such as LastPaymentDate (can't have them changing that!). Further, among those users, permissions differ. For example, the admin/superuser can change the name and address of the company, but standard users should not.
I'm thinking this might need to be done by making several Views for the table, one for each level of permission (group). I'm relatively new to MySQL, so I don't know if this is the best way. I can also see a lookup table that says which fields is allowed to view/edit.
My initial thought was to include in the comments (or the name of the field) a value from 0-5, and then the user would have a permission level (0-can't see; 1-Read only; 2-Read-write; 3-(not used); 4-(not used); 5-Edit/Delete the field itself.
Any suggestions? Views? Lookup table to determine which fields to display? Again, it'd not for the whole table, for each column within a table.
322
To grant a privilege with GRANT , you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. (Alternatively, if you have the UPDATE privilege for the grant tables in the mysql system schema, you can grant any account any privilege.)
In this syntax: First, specify one or more privileges after the GRANT keyword. If you grant multiple privileges, you need to separate privileges by commas. Second, specify the privilege_level that determines the level to which the privileges apply.
Privilege levels in MySQL There are six privilege levels used for granting privileges to the user: global, database, table, column, stored procedure or function, and proxy, as shown in the below image.
Now that you are at the mysqlcli prompt, you need only issue the GRANT command with the necessary options to apply the appropriate permissions. The GRANT command is capable of applying a wide variety of privileges, everything from the ability to CREATE tables and databases, read or write FILES, and even SHUTDOWN the server.
You can create a user with table level permissions in MySQL by performing the following: Connect to MySQL as a user with the Create_user_priv and Grant_priv. Determine which users have these privileges by running the following query.
Granting Privileges. Now that you are at the mysqlcli prompt, you need only issue the GRANT command with the necessary options to apply the appropriate permissions. The GRANT command is capable of applying a wide variety of privileges, everything from the ability to CREATE tables and databases, read or write FILES, and even SHUTDOWN the server.
Give the user a secure password. Now you can safely access your database with this user and be assured it only has permissions to the specified tables. The procedure for granting column level permissions on a specific table is very similar to granting table level permissions.
You can GRANT the rights to individual columns to a user, using this code:
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost'; Example taken from here:
http://dev.mysql.com/doc/refman/5.1/en/grant.html
Also there is no support for groups of users or SQL ROLES (which are groups of privileges) in MySQL.
103
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
