SQL User Management

Posts : 85 أهمية العضو : 0 Join date : 18/11/2008

SQL User Management

The system uses user, db, and host tables to determine access control. Also tables, priv, and columns.

The case of letters in commands does not matter. The system is not case sensitive.

User Determination

The system determines the user based on the following two characteristics:

User name.
The host the user connects from.

Tables used for User Access Control

These tables are contained in the database, "mysql". The "mysql" database is used to control user access and privileges.

user - Determines whether the user will be allowed to connect to the database.
db - Determines which users can access the database.
host - Used to allow users access from several hosts.

Functional Privileges

Users are given privileges using the GRANT command and provileges are removed using the REVOKE command.

The system will check each request from each user to determine if they have permission to perform the requested function.

GRANT and REVOKE Commands

Four privilege levels:

Global - Use "ON *.*" with the GRANT command to set global privileges.
Database - Use "ON dbname.*" with the GRANT command to set privileges on a database.
Table
Column

The GRANT statement can be used to add new users. GRANT Syntax:

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]

ON {tbl_name | * | *.* | db_name.*}

TO user_name [IDENTIFIED BY 'password']

[, user_name [IDENTIFIED BY 'password'] ...]

[REQUIRE

[{SSL| X509}]

[CIPHER cipher [AND]]

[ISSUER issuer [AND]]

[SUBJECT subject]]

[WITH GRANT OPTION] EX: Grant ALL on database_name.* TO username

If you use the GRANT statement to add a new user and do not include "IDENTIFIED BY" with a password in the statement, the user will be set up withoout a password. This is not very secure.

REVOKE Syntax:

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]

ON {tbl_name | * | *.* | db_name.*}

FROM user_name [, user_name ...]

Privilege Types :

The following privilege types may be granted or revoked using the GRANT or REVOKE commands.

ALL (ALL PRIVILEGES)
ALTER - Table
CREATE - Table
DELETE - Table
DROP - Table
FILE
GRANT OPTION - Used with the REVOKE command to prevent a user from using the GRANT command. Syntax is "REVOKE GRANT OPTION ON tablename FROM username".
INDEX - Table
INSERT - Table, column
PROCESS
REFERENCES - Not implemented yet.
RELOAD
SELECT - Table, column
SHUTDOWN
UPDATE - Table, column
USAGE - No privileges

Setting Passwords

The SET PASSWORD command is used to set a user's password.

موضوع: رد: SQL User Management الجمعة ديسمبر 05, 2008 2:27 pm

موضوع: رد: SQL User Management السبت سبتمبر 19, 2009 2:39 am

مـــشـــكـــورررررررررررر

» State Management
» SQL User Privileges
» User Privilege Policy
» Passing Variables from User Controls to ASP.NET pages