SQL/MySQL/User Permission/SQL Privilege
Содержание
- 1 Adding Users to Database
- 2 Check out the privilege for a user
- 3 Check the privilege
- 4 Grant select and update on two column in a table
- 5 Grant select and update to all tables
- 6 Grant select and update with time limitation
- 7 Grant with user name and password
- 8 List the host, DB, table name and column name for a user
- 9 Read column privilege
- 10 Read host, db, user, table name and table privilege from privilege table
- 11 Read the user table
Adding Users to Database
<source lang="sql">
GRANT SELECT, UPDATE (FieldName1, FieldName2, FieldName3) ON databaseName.tableName TO "myuserName"@"localhost" IDENTIFIED BY "myPassword" WITH MAX_CONNECTIONS_PER_HOUR 25;
</source>
Check out the privilege for a user
<source lang="sql">
/* oost is the usr name */ SELECT host, db, user, table_name, table_priv, column_priv
FROM tables_priv WHERE user="oost"; </source>
Check the privilege
<source lang="sql">
/* oost is the usr name */ SELECT host, user, select_priv, update_priv FROM user WHERE user="oost";
</source>
Grant select and update on two column in a table
<source lang="sql">
/* BookTitle and Copyright are field names */ GRANT SELECT, UPDATE (BookTitle, Copyright) ON databaseName.tableName TO "usrName"@"domain1.ru" IDENTIFIED BY "password" REQUIRE SSL;
</source>
Grant select and update to all tables
<source lang="sql">
/* "*" means all tables */ GRANT SELECT, UPDATE ON test.* TO "userName"@"domain1.ru" IDENTIFIED BY "password" WITH GRANT OPTION MAX_QUERIES_PER_HOUR 50 MAX_UPDATES_PER_HOUR 50;
</source>
Grant select and update with time limitation
<source lang="sql">
GRANT SELECT, UPDATE ON databaseName.* TO "userName"@"domain1.ru" IDENTIFIED BY "pw1" WITH GRANT OPTION MAX_QUERIES_PER_HOUR 50 MAX_UPDATES_PER_HOUR 50;
</source>
Grant with user name and password
<source lang="sql">
GRANT SELECT, UPDATE (columnName1, columnName2) ON databaseName.tableName TO "userName"@"domain1.ru" IDENTIFIED BY "password" REQUIRE SUBJECT "test client cert."
AND ISSUER "Test C.A.";
</source>
List the host, DB, table name and column name for a user
<source lang="sql">
/* oost is the usr name */ SELECT host, db, user, table_name, column_name, column_priv
FROM columns_priv WHERE user="oost";
</source>
Read column privilege
<source lang="sql">
SELECT host, db, user, table_name, column_name, column_priv
FROM columns_priv WHERE user="myuserName"; </source>
Read host, db, user, table name and table privilege from privilege table
<source lang="sql">
SELECT host, db, user, table_name, table_priv, column_priv
FROM tables_priv WHERE user="myuserName"; </source>
Read the user table
<source lang="sql">
SELECT host, user, select_priv, update_priv FROM user WHERE user="myuser1";
</source>