Based on the last above links in my edit to my question I found my answer. I cannot tweak the system account since our logons to our system are from an LDAP and I cannot control the groups and what not.
So I did the following:
- Opened the DB2 GUI (was easiest to do this way).
- Connected to the desired database as db2admin.
- Added my logon to the database as a user.
- GRANT all authorities to that id (my id).
- Disconnect.
- Connect to the desired database as my id.
- GRANT all authorities to db2admin.
- Disconnect.
Voila! The db2admin logon now as all authorities.
EDIT: I'm going to leave the above as it helped me learn how to do some interesting stuff in DB2. However, I have learned that the
DBADM/SECADM with DATAACCESS and ACCESSCTRL authorities granted the
instance owner (in my case the db2admin id) have all the authority
needed to interact with the database. I could have actually just
commented out those grant lines above in the script. Those were left
over from a script which ran against an older version of DB2. I have
also found if I need to have the instance owner DBADM after doing a
restore to database A from database B, it is easiest just to set the
registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITY to YES (available
in Fix Pack 2 and above). Then I don't have to try to grant instance
owner DBADM. It automatically is granted that to any database restored
into the instance. If you are not at Fix Pack 5, you have to bounce
the instance for this to take affect.
When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';
, mysqld wants to insert a row into the grant table mysql.tables_priv
. Here is mysql.tables_priv:
mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)
mysql>
Since you want to insert a row into mysql.table_priv
where user='wordpress' and host='%', there has to exist a row in mysql.user
where user='wordpress' and host='%'.
You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'
. That would usually have all rights and a password.
If you want to just allow anonymous SELECT against that table, first run this:
GRANT USAGE ON *.* TO 'wordpress'@'%';
This will place wordpress@'%'
into mysql.user
. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%'
should run just fine.
You will have to see what other wordpress entries are in mysql.user
. This should show what SQL GRANT commands you need:
SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
SELECT CONCAT('''',user,'''@''',host,'''') userhost
FROM mysql.user WHERE user='wordpress'
) A;
Best Answer
I hope It should work, Please have a look at following
Above shows that all privileges are granted to myuser on all databases.Now grant privileges for single db.
Now revoke unnecessary privleges
Now check which privileges still exits
That's what you need !!