To grant privilege to create a view:
GRANT CREATE VIEW TO STD01;
To grant the DML privileges:
GRANT SELECT,UPDATE,INSERT,DELETE ON STD00.CUSTOMER TO STD01;
But that's not all of the object privileges. If you did:
GRANT ALL ON STD00.CUSTOMER TO STD01;
you would also give other privileges such as ALTER, INDEX, FLASHBACK, etc.
Use this script to help you create the grant script syntax :
Run a root od mysql admin user.
select 'grant all privileges on ', table_name, 'to
someuser@somehost;' from information_schema.tables where table_schema = 'database name' and
table_name not in ('table without access', 'table without access');
This will create an output that you can run after.
Just to replicate the error :
mysql> grant select on BASE_BIB.* to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> use BASE_BIB;
Database changed
mysql> show tables ;
Now revoke select from a table form inside the BASE_BIB database:
mysql> revoke select on BASE_BIB.users from test123@'%';
ERROR 1147 (42000): There is no such grant defined for user 'test123'
on host '%' on table 'users'
No the right way to do it :
mysql> grant select on BASE_BIB.users to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> revoke select on BASE_BIB.users from test123@'%';
Query OK, 0 rows affected (0.00 sec)
No error now !!
Managing access in mysql can be quite dificult !!
Once you gave him database.* you cannot revoke access for an object that is in that class.
MySQL doesn't expand the Hotels.* wildcard to the individual tables
The permissions tables store the granted permissions. Therefore, since you didn't actually grant anything on Hotels.AllHotels , there's nothing for MySQL to revoke.
In this case you need to do it granular form the start !
Remove all privileges on database, table, column levels, etccc.
- Grant privileges to EACH table, except 'you choose'.
- Grant privilege to specified fields in table 'you choose'.
Best Answer
There is a way to do it in previous versions of MySQL referring to anonymous users with the following query, however I think it does not work for your version of MySQL.
I recommend you create a ROLE and assign this role to all users, after that you can assign a permission to that ROLE and it will have an effect on all the users to whom it is assigned. Administration in this way is much easier. For example: