This has always happened, back to SQL Server 2000.
Without schema, how does SQL Server know you want to put it in the dbo
schema?
The only way to specify a default schema is to :
- use SQL logins (not Windows)
- run as "sysadmin"
Neither of these is acceptable
Best practice is to always qualify schema for every object reference for DDL and DML. There are clear performance benefits because of plan re-use.
Also, deliberate schema use is better for SQL Server 2005:
- tables in
Data
- other tables in
Archive
, Staging
etc
- code in schemas per client permissions:
Desktop
, WebGUI
etc
Using the dbo schema is so last millenium :-) Links:
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
MongoDB 3.2 and earlier require downtime and a coordinated restart of your deployment and application to enable access control: all clients and members of a deployment must use authentication once enabled. There is a localhost exception that allows you to create the first user on the
admin
database after authentication is enabled.With planning this can be a relatively quick process, but to minimize potential downtime I strongly recommend first testing in a representative staging environment.
Enabling access control and authentication is an obvious security measure but still leaves you vulnerable to other possible attacks. For example, you should also Configure TLS/SSL to secure your network communication and restrict remote network access via firewall or perhaps a VPN/VPC between your application servers and your MongoDB deployment.
For a full list of security measures and links to relevant tutorials, see the Security Checklist in the MongoDB manual.
There are several steps that you can test in a staging environment to help ensure the transition goes smoothly, including:
MongoDB 3.4
--transitionToAuth
Another option worth mentioning for future consideration (although generally not the fastest path to enabling authentication on an existing deployment) would be upgrading to MongoDB 3.4.
MongoDB 3.4 includes a new
--transitionToAuth
option that enables a rolling upgrade to authentication; see: Enforce Keyfile Access Control in a Replica Set without Downtime.