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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
