|
Home >> FAQs/Tutorials >> MySQL Tutorials >> Index
MySQL FAQs - Managing User Accounts and Access Privileges
By: FYIcenter.com
Part:
1
2
3
4
5
6
(Continued from previous part...)
How To Give a User Read-Only Access to a Database?
If you want give a user read-only access to a database, you can grant to him/her
only the "SELECT" privilege, so that he/she can not run any DDL statements, and any
INSERT, UPDATE, or DELETE statements. The tutorial exercise gives you a good example:
>cd \mysql\bin
>mysql -u root -pretneciyf
mysql> USE fyi;
Database changed
mysql> CREATE TABLE links (id INTEGER, name VARCHAR(80));
Query OK, 0 rows affected (0.14 sec)
mysql> INSERT INTO links VALUES (1, 'dba.fyicenter.com');
Query OK, 1 row affected (0.05 sec)
mysql> CREATE USER guest IDENTIFIED BY 'pub';
Query OK, 0 rows affected (0.24 sec)
mysql> GRANT SELECT ON fyi.* TO guest;
Query OK, 0 rows affected (0.00 sec)
mysql> QUIT;
>mysql -u guest -ppub
mysql> use fyi;
Database changed
mysql> SELECT * FROM links;
+------+-------------------+
| id | name |
+------+-------------------+
| 1 | dba.fyicenter.com |
+------+-------------------+
1 row in set (0.04 sec)
mysql> INSERT INTO links VALUES (2, 'dev.fyicenter.com');
ERROR 1142 (42000): INSERT command denied to user
'guest'@'localhost' for table 'links'
Where Are User Privileges Stored on the Server?
MySQL server has a system database, which hosts a number of system tables
to system related information like user privileges. Depending on the scope levels,
granted user privileges are stored in different tables:
- "mysql.user" - Stores privileges granted at the global level.
- "mysql.db" - Stores privileges granted at the database level.
- "mysql.table_priv" - Stores privileges granted at the table level.
- "mysql.columns_priv" - Stores privileges granted at the column level.
- "mysql.procs_priv" - Stores privileges granted at the routine level.
The following tutorial exercise shows you an example of granted privileges
at the table level:
>cd \mysql\bin
>mysql -u root -pretneciyf
mysql> SELECT db, user, create_priv, select_priv
-> FROM mysql.db;
+---------+-------+-------------+-------------+
| db | user | create_priv | select_priv |
+---------+-------+-------------+-------------+
| test | | Y | Y |
| test\_% | | Y | Y |
| fyi | guest | N | Y |
| faq | qa | Y | N |
+---------+-------+-------------+-------------+
4 rows in set (0.00 sec)
Part:
1
2
3
4
5
6
|