[ Pobierz całość w formacie PDF ]
.Book IIIChapter 2Setting Up MySQL AccountsAn account is identified by the account name and the name of the computerallowed to access MySQL from this account.When you create a new account,you specify it as accountname@hostname.You can specify a password whenyou create an account, or you can add a password later.You can also set upprivileges when you create an account or add privileges later.All the account information is stored in a database named mysqlthat s auto-matically created when MySQL is installed.To add a new account or changeany account information, you must use an account that has the proper privi-leges on the mysqldatabase.In the rest of this chapter, we describe how to add and delete accounts andchange passwords and privileges for accounts.If you have an account thatyou received from your company IT department or from a Web hostingcompany, you might receive an error when you try to add an account orchange account privileges as described in this chapter.If your account isrestricted from performing any of the necessary queries, you need to requestan account with more privileges or ask the MySQL administrator to add anew account for you or make the changes you need.AdministeringMySQL17_167779 bk03ch02.qxp 12/17/07 8:32 PM Page 276276 Setting Up MySQL AccountsThe MySQL security databaseWhen MySQL is installed, it automatically cre- designer can insert into all the data-ates a database called mysql.All the infor- bases.If a row in the dbtable shows Nformation used to protect your data is stored in INSERT for the designer account inthis database, including account names, host- the PetCatalog database, the usernames, passwords, and privileges.table overrules it, and designer caninsert in the PetCatalogdatabase.Privileges are stored in columns.The format ofeach column name is privilege_priv, in hosttable: This table controls access towhich privilegeis a specific account priv- a database, depending on the host.Theilege.For instance, the column containing hosttable works with the dbtable.If aALTER privileges is named alter_priv.row in the dbtable has an empty field forThe value in each privilege column is Yor N, the host, MySQL checks the hosttable tomeaning yes or no.So, for instance, in the user see whether the dbhas a row there.In thistable (described in the following list), there way, you can allow access to a db fromwould be a row for an account and a column some hosts but not from others.Forfor alter_priv.If the account field for instance, suppose you have two data-alter_privcontains Y, the account can be bases: db1and db2.The db1databaseused to execute an ALTER query.If has sensitive information, so you want onlyalter_privcontains N, the account does- certain people to see it.The db2databasen t have privilege to execute an ALTERquery.has information that you want everyone tosee.If you have a row in the dbtable forThe mysql database contains the followingdb1with a blank hostfield, you can havetables that store privileges:two rows for db1in the hosttable.Oneuser table: This table stores privileges row can give all privileges to users con-that apply to all the databases and tables.necting from a specific host, whereasIt contains a row for each valid account another row can deny privileges to usersthat includes the columns user name, connecting from any other host.hostname, and password.The MySQLtables_priv table: This table storesserver rejects a connection for an accountprivileges that apply to specific tables.that doesn t exist in this table.columns_privtable: This table storesdbtable: This table stores privileges thatprivileges that apply to specific columns.apply to a particular database.It containsa row for the database, which gives privi- You can see and change the tables in mysqlleges to an account name and a hostname.directly if you re using an account that has theThe account must exist in the usertable necessary privileges.You can use SQL queriesfor the privileges to be granted.Privileges such as SELECT, INSERT, and UPDATE.Ifthat are given in the usertable overrule you re accessing MySQL through yourprivileges in this table.For instance, if the employer, a client, or a Web hosting company,user table has a row for the account you probably don t have an account with thedesignerthat gives INSERTprivileges, necessary privileges.17_167779 bk03ch02.qxp 12/17/07 8:32 PM Page 277277Setting Up MySQL AccountsIdentifying what accounts currently existTo see the account information, you can execute an SQL query, using themysql client or phpMyAdmin (as described in the section about sending SQLqueries in Chapter 1 in this minibook.), or you can use features of phpMyAdmin [ Pobierz całość w formacie PDF ]