Hello All ; Please help me in knowing below queries which are in Mysql to Postgresql.
1. SELECT user,host,password FROM mysql.user WHERE password = ''; SET PASSWORD FOR <user>@<host> = PASSWORD ('newpass'); 2. SELECT user,host,password FROM mysql.user WHERE user = ''; DELETE FROM mysql.user WHERE user = ''; FLUSH PRIVILEGES; 3. SELECT user,host,password FROM mysql.user WHERE user = 'root' AND host = '%'; DROP USER root@'%'; FLUSH PRIVILEGES; 4. SELECT user,host,password FROM mysql.user WHERE length(password) <> 41; SET PASSWORD FOR <user>@<host> = PASSWORD ('newpass'); 5. Do not enable insecure password generation option Setting can be verified by viewing the MySQL config file as per the Recommended settings, OR by issuing the following command: mysqladmin var | grep old_passwords 6. Enable secure password authentication option by blocking connections from all accounts that have passwords stored in it. Setting can be verified by viewing the MySQL config file as per the Recommended settings, OR by issuing the following command: mysqladmin var | grep secure_auth 7. Binary logging should be enabled to log all completed transactions, and allow for point-in-time recovery. This can be enabled via the log-bin status variable in the mysql configuration file. As an example, the following entry will place all binary log files in the /var/lib/mysql/logs directory, and use 'binlog' as the filename prefix to get binary log files names such as binlog.000001: log-bin=/var/lib/mysql/logs/binlog Setting can be verified by viewing the MySQL config file as per the Recommended settings, OR by issuing the following command: mysqladmin var | grep log_bin 8. Prevent the grant statement from creating new users unless a non-empty password is specified (v5.0.2+) Setting can be verified by viewing the MySQL config file as per the Recommended settings, OR by issuing the following command: mysqladmin var | grep sql_mode 9. Do not allow new user creation by a user that does not have write access to the mysql.user table. Note that this setting may not appear when viewing status variables via "show variables" or "mysqladmin var", and should therefore be validated by checking the MySQL config file. Disable global access to test% databases by issuing the following sample SQL statements: SELECT user,host,db FROM mysql.db WHERE db LIKE 'test%'; DELETE FROM mysql.db WHERE db LIKE 'test%'; FLUSH PRIVILEGES; 9. Identify and remove privileges on non-existent database objects by issuing the following sample SQL statements: SELECT d.host, d.db, d.user FROM mysql.db AS d LEFT OUTER JOIN information_schema.schemata AS s ON s.schema_name LIKE d.db WHERE s.schema_name IS NULL; DELETE FROM mysql.db WHERE db=<obsolete_db>; SELECT d.host, d.db, d.user, d.table_name FROM mysql.tables_priv AS d LEFT OUTER JOIN information_schema.schemata AS s ON s.schema_name LIKE d.db WHERE s.schema_name IS NULL; DELETE FROM mysql.tables_priv WHERE db=<obsolete_db>; SELECT d.host, d.db, d.user, d.table_name, d.column_name FROM mysql.columns_priv AS d LEFT OUTER JOIN information_schema.schemata AS s ON s.schema_name LIKE d.db WHERE s.schema_name IS NULL; DELETE FROM mysql.columns_priv WHERE db=<obsolete_db>; 10. SUPER privileges can be verified by issuing the following SQL statement: SELECT user, host, super_priv AS 'SUPER' FROM mysql.user WHERE super_priv = 'Y'; 11. Additional global admin privileges can be reviewed by issuing the following sample SQL statement: SELECT user, host, super_priv AS 'SUPER', shutdown_priv AS 'SHUTDOWN', process_priv AS 'PROCESS', grant_priv AS 'GRANT', create_user_priv AS 'CREATE_USER', file_priv AS 'FILE', reload_priv AS 'RELOAD', show_db_priv AS 'SHOW_DATABASES', lock_tables_priv AS 'LOCK_TABLES', repl_slave_priv AS 'REPL_SLAVE', repl_client_priv AS 'REPL_CLIENT' FROM mysql.user WHERE super_priv = 'Y' OR shutdown_priv = 'Y' OR process_priv = 'Y' OR grant_priv = 'Y' OR create_user_priv = 'Y' OR file_priv = 'Y' OR reload_priv = 'Y' OR show_db_priv = 'Y' OR lock_tables_priv = 'Y' OR repl_slave_priv = 'Y' OR repl_client_priv = 'Y'; 12. SHUTDOWN privileges can be verified by issuing the following SQL statement: SELECT user, host, shutdown_priv AS 'SHUTDOWN' FROM mysql.user WHERE shutdown_priv = 'Y'; PROCESS privileges can be verified by issuing the following SQL statement: SELECT user, host, shutdown_priv AS 'PROCESS' FROM mysql.user WHERE process_priv = 'Y'; GRANT privileges can be verified by issuing the following SQL statement: SELECT user, host, shutdown_priv AS 'GRANT' FROM mysql.user WHERE grant_priv = 'Y'; These privileges can be verified by issuing the following SQL statement to display all global database privileges, inclusive of the mysql database. SELECT user, host, select_priv AS 'SELECT', insert_priv AS 'INSERT', update_priv AS 'UPDATE', delete_priv AS 'DELETE', show_db_priv AS 'SHOWDB', create_priv AS 'CREATE', drop_priv AS 'DROP', index_priv AS 'INDEX', alter_priv AS 'ALTER', create_routine_priv AS 'CREATE_ROUTINE', alter_routine_priv AS 'ALTER_ROUTINE', create_view_priv AS 'CREATE_VIEW', show_view_priv AS 'SHOW_VIEW', create_tmp_table_priv AS 'CREATE_TEMP', execute_priv AS 'EXECUTE', references_priv AS 'REFERENCES' FROM mysql.user WHERE select_priv = 'Y' OR insert_priv = 'Y' OR update_priv = 'Y' OR delete_priv = 'Y' OR show_db_priv = 'Y' OR create_priv = 'Y' OR drop_priv = 'Y' OR index_priv = 'Y' OR alter_priv = 'Y' OR create_routine_priv = 'Y' OR alter_routine_priv = 'Y' OR create_view_priv = 'Y' OR show_view_priv = 'Y' OR create_tmp_table_priv = 'Y' OR execute_priv = 'Y' OR references_priv = 'Y'; These privileges can be verified by issuing the following SQL statements to display additional privileges granted against database/table/column objects of the mysql database. SELECT user, host, db FROM mysql.db WHERE db='mysql'; SELECT user, host, db, table_name FROM mysql.tables_priv WHERE db='mysql'; SELECT user, host, db, table_name, column_name FROM mysql.columns_priv WHERE db='mysql'; What is the SQL Query or command for Postgres to check the following points? 1.No blank passwords 2.No anonymous-user accounts 3.No remotely-accessible root accounts 4.No insecure passwords 5.MySQL config file setting: old-passwords = 0 OR old_passwords = 0 6.MySQL config file setting: secure-auth [= 1] OR secure_auth [= 1] 7.MySQL config file setting: log-bin [= /path/to/log/file-prefix] OR log_bin [= /path/to/log/file-prefix] 8.MySQL config file setting: sql-mode = no_auto_create_user OR sql_mode = no_auto_create_user 9.MySQL config file setting: safe-user-create [=1] OR safe_user_create [=1] 10.No global access to test% databases 11.Remove obsolete privileges -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-Help-tp5767001.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general