0

On MySQL 5.1 for Windows, why can't I assign DBA role to "root" user? The MySQL Workbench allows me to add all the other roles except for DBA. Also, when I "alter schema" on any table, while logged in as root, I dont see all the tabs that show me all the database properties... I only see the first tab that allows me to change collation only.

What is wrong with this picture? How do i give root all priveleges? I've tried a few variations of GRANT ALL PRIVILEGES etc. from the command line but nothing works.

My root account is unable to alter column names, indexes, or options of any given table that I create. I can create tables and delete them but I can't alter them.

djangofan
  • 4,182
  • 10
  • 46
  • 59

1 Answers1

0

root has all privileges by default. Why do you think you need to give it more?

Try running these SQL statements to verify. Compare against almost every other account and you will see that root has far more privileges in the mysql.user table then any other account unless you have done something unusual.

show grants for 'root'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '...' WITH GRANT OPTION
select * from mysql.user where user='root';

*************************** 3. row ***************************
                 Host: 127.0.0.1
                 User: root
             Password: ...
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
Zoredache
  • 130,897
  • 41
  • 276
  • 420
  • Well, when I connect to a Amazon cloud instance of SQL (RDS) I can see all the tabs showing all the properties of each table (via MySQL Workbench). For this reason it appears that my local MySQL instance has less permissions, despite the fact that I am logged in as "root". I am almost certain I dont have all permissions and I dont know why. for example, right click on a table and 'alter schema' and I do not see the tabs for altering indexes, columns, etc. – djangofan Mar 14 '11 at 23:18
  • @djangofan, I don't know much about the Amazon cloud or how you got that setup, but the standard mysql privileges don't even permit you to remotely connect as root. So whoever, or whatever set that up the system to permit you remote root access, may not have granted all the privileges correctly. If you don't have enough privileges you probably aren't going to be able to grant yourself more. Posting the output of the two sql statements I showed you may tell us a lot more. – Zoredache Mar 14 '11 at 23:24
  • Those sql queries seem to have fixed it for me. Thanks. I don't know why the UPDATE needed to have a 40 char encoded password, but it did... i never saw that before. examples on the net always seem to use the clear text password. – djangofan Mar 14 '11 at 23:29
  • I didnt have any problems with the MySQL RDS on the amazon cloud. This question was all about my local instance. – djangofan Mar 14 '11 at 23:30
  • @Zoredache - if root has all privileges, when why isnt the DBA checkbox checked in the Administrative roles tab of the MySQL Workbench account management? – djangofan Mar 14 '11 at 23:33
  • @djangofan, I wouldn't know, I never actually used the MySQL Workbench. I use the cli mysql client or scripts for everything. – Zoredache Mar 14 '11 at 23:45