Error 1221 hy000 incorrect usage of db grant and global privileges

В данной статье речь пойдет об ошибке Incorrect usage of DB GRANT and GLOBAL PRIVILEGES, которую вы ...


Программное обеспечение

  • 03.06.2016
  • 7 667
  • 0
  • 18.03.2019
  • 1
  • 1
  • 0

Исправляем ошибку: ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

  • Содержание статьи
    • Описание
    • Добавить комментарий

В данной статье речь пойдет об ошибке «Incorrect usage of DB GRANT and GLOBAL PRIVILEGES», которую вы можете получить в процессе настройки репликации Mysql и том, как от нее избавиться.

Описание

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Настраивая репликацию Mysql баз, столкнулся с проблемой, что при попытке добавить пользователя с правами на репликацию одной конкретной базы (из мануалов в интернете), получал данную ошибку.

mysql@master> GRANT replication slave ON "mydb".* TO "replication"@"192.168.1.1" IDENTIFIED BY "password";

После небольшого гугления, обнаружил, что данные привилегии даются пользователю глобально, а не на конкретную базу, поэтому, чтобы избежать данной ошибки, запрос должен быть такого вида:

mysql@master> GRANT replication slave ON *.* TO "replication"@"192.168.1.1" IDENTIFIED BY "password";

Источник

In previous articles, we have learned about user creation and grants in MySQL in detail, but there are a few privileges called global privileges that are meant to be granted to DBA accounts only
for example
PROCESS privileges is a global privilege. 
we will try to grant it to user tech_user on author database

mysql>grant process on author.* to
tech_user;

ERROR
1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES


Here we see it failed with error 1221 (HY000).


In order to resolve this error, we need to reconsider this grant statement and its usage.
we must know global privileges can not be granted to the individual database, instead, it should be on all databases. i.e. use of *.* instead of an author.*

so the statement should look like this

grant process on *.* to tech_user;

Below are some of the well know global privileges

  • FILE
  • PROCESS
  • REPLICATION CLIENT
  • REPLICATION SLAVE
  • SUPER

In the below way, grants can be executed using the root account without error. 

mysql> grant file on *.* to
tech_user;

Query
OK, 0 rows affected (0.01sec)

mysql>flush
privileges;

Query
OK, 0 rows affected (0.01sec)

Источник

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

<<Back to MySQL & MariaDB How To Page

MariaDB [(none)]> grant file on test.* to ‘test’@’localhost’;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Cause:
File privilege is a global privilege and you can not grant it at database/schema level. Modify the command as shown below and rerun.

Solution:
 MariaDB [(none)]> grant file on *.* to ‘test’@’localhost’;
Query OK, 0 rows affected (0.01 sec)

Popular posts from this blog

<<Back to DB Administration Main Page Oracle ROWID Explained From  Oracle 8 onwards Oracle ROWID format is on 10 bytes and represented as OOOOOOFFFBBBBBBRRR. Bits 1 to 32 (bytes 1 to 4) presents data object id (Possible unique DB Object 4294967295) Bits 33 to 44 (byte 5 and half byte 6): represent file number inside the tablespace (Possible unique datafile 4095) Bits 45 to 64 (half byte 6 and bytes 7 and 8): presents block number inside the file ( possible unique block ids 1048575) Bits 65 to 80 (bytes 9 and 10):presents  row number inside the block ( possible number of rows in a block 65535) When printed, each field is displayed in radix 64 (A-Za-z0-9+/): The first 6 characters of the ROWID presents Object ID, Next 3 characters presents datafile# the object belongs to, next 6 characters presents Block ID in the datafile and the last 3 character presents actual row number in the block. That’s said lets understand this with an example. SQL> select row

<<Back to Oracle DATAPUMP Main Page How to check the progress of  export or import Jobs You can attach to the export/import  job using ATTACH parameter of oracle datapump utility. Once you are attached to the job you check its status by typing STATUS command. Let us see how Step1>  Find the Export/Import Job Name You can find the datapump job information from  DBA_DATAPUMP_JOBS or  USER_DATAPUMP_JOBS view. SQL> SELECT OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE from DBA_DATAPUMP_JOBS; OWNER_NAME JOB_NAME                       OPERATION            JOB_MODE   STATE ———- —————————— ——————— ———- ———- SYSTEM     SYS_EXPORT_FULL_02             EXPORT               FULL       EXECUTING OR You can also find the job name for export/import in logfile in beginning itself. Step2>Attach to the Job and check status One you get the Export/Import Job Name attach the job and check its status. You can attach or det

<<Back to Oracle DB Security Main Page ORA-46665: master keys not activated for all PDBs during REKEY SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY xxxx WITH BACKUP CONTAINER = ALL ; ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY xxxx WITH BACKUP CONTAINER = ALL * ERROR at line 1: ORA-46665: master keys not activated for all PDBs during REKEY I found following in the trace file REKEY: Create Key in PDB 3 resulted in error 46658 *** 2019-02-06T15:27:04.667485+01:00 (CDB$ROOT(1)) REKEY: Activation of Key AdnU5OzNP08Qv1mIyXhP/64AAAAAAAAAAAAAAAAAAAAAAAAAAAAA in PDB 3 resulted in error 28374 REKEY: Keystore needs to be restored from the REKEY backup.Aborting REKEY! Cause: All this hassle started because I accidently deleted the wallet and all wallet backup files too and also forgot the keystore password. There was no way to restore the wallet back. Fortunately in my case the PDB which had encrypted data was supposed to be deco

<<Back to DB Administration Main Page There are instances when a DBA need to start the database using pfile, for example to trouble an instance startup error or to validate init file post parameter changes etc. In such situations you can create a pfile from spfile and once you are done with your changes you can create spfile from updated/modified pfile to start the database. How to Create Pfile from Spfile As sysdba execute following command  SQL> create pfile=’/tmp/initOrcl.ora’ from spfile; How to Create SPfile from Pfile As sysdba execute following command  SQL> create spfile from  pfile=’/tmp/initOrcl.ora’; You can also create the pfile directly from memory How to Create Pfile from Memory As sysdba execute following command  SQL> create  pfile=’/tmp/initOrcl.ora’ from memory;

<<Back to Oracle ASM Main Page ORA-15040: diskgroup is incomplete SQL> startup ORA-00099: warning: no parameter file specified for ASM instance ASM instance started Total System Global Area 1140850688 bytes Fixed Size                  8629704 bytes Variable Size            1107055160 bytes ASM Cache                  25165824 bytes ORA-15110: no diskgroups mounted Reason: The reason of this error is simply the ASM is not able to find the some or all the disks. Solution: Investigate and make all the disks available to ASM to mount the disk group. Make sure the disks has proper permissions. If you are using AFD check following services are online oracleacfs oracleadvm oracleoks  oracleafd   Source of Problem : Issue started after restart of the server After restarting the server when I tried to start the ASM instance its started throwing error.  ORA-15110: no diskgroups mounted Investigation in my Case Step1> ASM Logfile Scanning  Looked i

<<Back to Oracle RAC Main Page How to Find Out VIP of an Oracle RAC Cluster Login clusterware owner (oracle) and execute the below command to find out the VIP hostname used in Oracle RAC $ olsnodes -i node1     node1-vip node2     node2-vip OR $ srvctl config nodeapps -viponly Network 1 exists Subnet IPv4: 10.0.0.0/255.255.0.0/bondeth0, static Subnet IPv6: Ping Targets: Network is enabled Network is individually enabled on nodes: Network is individually disabled on nodes: VIP exists: network number 1, hosting node node1 VIP Name: node1-vip VIP IPv4 Address: 10.0.0.1 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes: VIP exists: network number 1, hosting node node2 VIP Name: node2-vip VIP IPv4 Address: 10.0.0.2 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes:

<<Back to DB Administration Main Page ORA-28365: wallet is not open Encountered while Starting the Database $ srvctl start instance -d CDB001 -i CDB0011 PRCR-1013 : Failed to start resource ora.cdb001.db PRCR-1064 : Failed to start resource ora.cdb001.db on node node1.oracle.com CRS-5017: The resource action «ora.cdb001.db start» encountered the following error: ORA-28365: wallet is not open . For details refer to «(:CLSN00107:)» in «/u01/app/oracle/diag/crs/node1.oracle.com/crs/trace/crsd_oraagent_oracle.trc». CRS-2674: Start of ‘ora.cdb001.db’ on ‘node1.oracle.com’ failed Solution : Start the instance in mount mode SQL> startup mount; ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size                  2926472 bytes Variable Size            1392511096 bytes Database Buffers          738197504 bytes Redo Buffers               13848576 bytes Database mounted. Check Wallet status set linesiz

<<Back to Oracle DB Security Main Page How to Configure a Software Keystore A software keystore is a container that stores the Transparent Data Encryption master encryption key. To configure a software Keystore follow the steps below. Step 1: Set the Keystore Location in the sqlnet.ora File You can store the software keystore (also known as wallet) in file system or in ASM Diskgroup. Does not matter where you want to store the keystore you have modify the sqlnet.ora and make an entry accordingly Make an entry as shown below in $ORACLE_HOME/network/admin/sqlnet.ora file Example1: If Storing the Wallet in ASM ENCRYPTION_WALLET_LOCATION=  (SOURCE=(METHOD=FILE)    (METHOD_DATA=     (DIRECTORY= +DG_TST_DATA/$ORACLE_SID/wallet )    )  )   Example2: If Storing the Wallet in File System ENCRYPTION_WALLET_LOCATION=  (SOURCE=(METHOD=FILE)    (METHOD_DATA=     (DIRECTORY= /u01/dbatst1/admin/wallet/$ORACLE_SID)    )  ) NOTE: Ensure that the path you entered in  DIREC

<<Back to DB Administration Main Page How to Upgrade from 11g,12c,18c to 19c DB Upgrade Compatibility Matrix Below is minimum version of the database that can be directly upgraded to Oracle 19c. Source Database Target Database 11.2.0.4 19c 12.1.0.2 19c 12.2.0.1 19c 18.1.0.0 19c NOTE: In this post I will demonstrate the upgrade from 12.1.0.2 to 19c. The steps are same for any other version to 19c upgrade. Upgrading the database from 12.1.0.2 to 19c I will be using DBUA its really easy to use and self explanatory as well as recommended options. It automates many things including upgrading Time zone, gathering dictionary statistics , taking restore RMAN backup or creating restore point , starting the listener with new OH etc. etc. Although DBUA can do many thing its recommended to everything possible that can be done upfront to minimize the overall downtime. General Requirement Ensure All DB Components and objects are valid.

<<Back to Oracle ASM Main Page How to Use renamedg Utility to Rename ASM DiskGroup In this post I will change the name of diskgroup from DG_TEST to DG01 Step 1: dismount the DG on all nodes  Step 2: Validate rename DG operation by running  remamedg  command with check  options  verbose=true check=true Step 3: Rename DG using renamedg utility Step 4: mount the new DG  Check out the current DG Configuration $ asmcmd lsdg DG_TEST State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name MOUNTED  EXTERN  N         512             512   4096  1048576    102400   102346                0          102346              0             N  DG_TEST/ $ asmcmd lsdsk -k Total_MB  Free_MB  OS_MB  Name     Failgroup  Site_Name  Site_GUID                         Site_Status  Failgroup_Type  Library                                      Label    Failgroup_Label  Site_Label  UDID  Product  Redun

Источник

Всем привет! Не могу понять почему у меня не делается бекаб базы. Помогите пожауйста! Команда:

MYSQL_HOST='127.0.0.1';
MYSQL_USERNAME='admin_root';
MYSQL_DB='main_data';
MYSQL_PWD='123456'
MYSQL_PWD=${MYSQL_PWD} mysqldump -h "${MYSQL_HOST}" -u "${MYSQL_USERNAME}" "${MYSQL_DB}" > "/tmp/${FILENAME}

Получаю такую ошибку

‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces

что не так?


  • Вопрос задан

    более двух лет назад

  • 5226 просмотров

GRANT PROCESS ON *.* TO 'admin_root'@'localhost'

Пригласить эксперта

Если не используете TABLESPACE, то новых прав добавлять НЕ НАДО!
Нужно при вызове mysqldump указать опцию —no-tablespaces.


  • Показать ещё
    Загружается…

09 февр. 2023, в 14:22

1500 руб./за проект

09 февр. 2023, в 13:58

2000 руб./за проект

09 февр. 2023, в 13:28

777 руб./за проект

Минуточку внимания

Источник

How to fix the mysqldump process privilege error after applying a recent MySQL update.

You may receive a new ‘Access denied’ error when trying to dump your MySQL database:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

You were able to export database before so what caused this? Here’s the answer: updates for MySQL 5.7.31 and MySQL 8.0.21 in July 2020 introduced an incompatible change:

Incompatible Change: Access to the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege.

This change affects users of the mysqldump command, which accesses tablespace information in the FILES table, and thus now requires the PROCESS privilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the --no-tablespaces option. (Bug #30350829)

This error appears when running mysqldump directly from the command line, exporting the database using a client like MySQL Workbench or if you’re managing the WordPress database through WP-CLI’s export command.

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

In my case, I encountered the problem when running a routine Python script for a Drupal to WordPress migration client. My script uses WP-CLI to export a database dump file and deploy it to a remote server.

Solutions for fixing the mysqldump process privilege error

The mysqldump command requires at least the following privilege assigned to the user:

  • SELECT privilege for dumped tables
  • SHOW VIEW for dumped views
  • TRIGGER for dumped triggers
  • LOCK TABLES if you don’t use the --single-transaction option
  • PROCESS if you don’t use the --no-tablespaces option

The last PROCESS privilege is new as of MySQL 5.7.31 and MySQL 8.0.21 and may be the root source of your problem. You can solve the mysqldump process privilege error in two ways:

  1. Updating the privileges for your database user.
  2. Runing mysqldump with the --no-tablespaces option.

Solution 1: Update the user privileges

Granting the PROCESS privilege for the user is perhaps the simplest option for fixing the mysqldump process privilege error. Keep in mind that this option presents security issues. You should therefore really only use this option for your own local development server installation.

To grant the PROCESS privilege, log in as an administrator user and run the following query:

GRANT PROCESS ON *.* TO [email protected];

Note that PROCESS is a global level privilege. It can’t apply to individual databases. Global privileges are either administrative or apply to all databases on your MySQL server. Trying to grant them on individual databases deplays the following error:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

To grant the privilege to all databases you must use the ON *.* ... syntax.

Solution 2: Use the --no-tablespaces option

If you cannot assign global level privileges to your user, for example, when doing so presents unacceptable security issues, you must specify the --no-tablespaces option when dumping your database.

mysqldump --no-tablespaces -u user -ppass dbname > db_backup_file.sql

What are MySQL tablespaces?

We are usually only concerned with logical database objects when working with databases. However, the data must be physically stored somewhere. This is where tablespaces come in. Tablespaces are physical datafiles stored in the host file system holding data for one or more tables and indexes.

The diagram below provides a handy illustration. It’s from the Oracle Concepts documentation, Introduction to Tablespaces, Datafiles, and Control Files and refers to Oracle databases. Nevertheless, it may help you understand how tablespaces relate to logical database objects and datafiles.

Oracle documentation: Introduction to Tablespaces, Datafiles, and Control Files

Diagram source: Oracle Concepts documentation

You can therefore use the --no-tablespaces option if you don’t need to dump tablespace information. This may be the case for routine database dumps, for example when exporting databases for WordPress migrations.

About the access mysqldump denied PROCESS privilege error

mysqldump accesses tablespace information in the FILES table. Prior to MySQL 5.7.31 and 8.0.21, your user could run mysqldump without the PROCESS privilege. However, users running mysqldump after the update need PROCESS privileges to access the INFORMATION_SCHEMA.FILES table. Running mysqldump without PROCESS privilege ends up giving you an Access denied error.

Be careful with the PROCESS privilege

According to the MySQL documentation, the PROCESS privilege controls access to information about statements being executed by sessions.

It is a server administration privilege and should not be given to all users. This is because it may show text from currently executing queries. Any user with the PROCESS privilege may therefore see queries issued by others. Here’s the danger: these queries, such as UPDATE user SET password=PASSWORD, may show secrets.

For more information, see General Security Issues and the MySQL Access Privilege System from the O’Reilly MySQL Reference Manual.

Источник

Понравилась статья? Поделить с друзьями:
  • Error 1146 42s02 at line 1 table zabbix images doesn t exist
  • Error 1140 sql
  • Error 1136 sql
  • Error 1136 21s01 column count doesn t match value count at row 1
  • Error 1130 hy000 host is not allowed to connect to this mariadb server