Managing Permissions
Permission Levels
- User level (supported)
- Database level (supported)
- Table level (supported)
- Column level (not supported)
- Subprogram level (not supported)
Permission Types
DDM supports different permission types by using the GRANT statement.
Permission Type |
Description |
---|---|
ALL |
All permissions |
DROP |
Deleting a table |
INDEX |
Creating/Deleting an index |
ALTER |
Executing ALTER statements |
CREATE |
Creating a table |
SELECT |
Reading table data |
INSERT |
Inserting data to a table |
UPDATE |
Updating data in a table |
GRANT |
Granting permissions to users |
REVOKE |
Deleting a user permission |
SET |
Setting user's passwords |
FILE |
Uploading database permissions from a file |
CREATE USER |
Creating a user |
Permission Operations
SHOW GRANTS is supported in versions in 3.0.2 or later. Other functions are available in versions 2.4.1.4 or later.
CREATE USER
Syntax:
CREATE USER username IDENTIFIED BY 'auth#string'
Example: Creating an account (username: Jenny; password: Abc_123456)
CREATE USER Jenny IDENTIFIED BY 'Abc_123456';
Each username and password must meet the corresponding requirements.
DROP USER
Syntax:
DROP USER username
Example: Removing user Jenny
DROP USER Jenny;
SET PASSWORD
Syntax:
SET PASSWORD FOR 'username'@'%' = 'auth_string'
To be compatible with the MySQL syntax, the username must be in the format of 'username'@' %'.
Example: Changing the password of Jenny to Abc_1234567
SET PASSWORD FOR 'Jenny'@'%' = 'Abc_1234567'
GRANT
GRANT priv_type[, priv_type] ... ON priv_level TO user [auth_option] priv_level: { | *.* | db_name.* | db_name.tbl_name | tbl_name} auth_option: { IDENTIFIED BY 'auth#string' }
If a GRANT statement provides no accounts and does not specify IDENTIFIED BY, a message No account found will be returned. If IDENTIFIED BY is specified, an account will be created accordingly and permissions will be granted to it.
GRANT ALL [PRIVILEGES] can be used to assign only table-level permissions.
Example 1: Create a user-level account with all permissions. The username is Mike.
Method 1: Create an account and then grant permissions to it.
CREATE USER Mike IDENTIFIED BY 'your#password'; GRANT SELECT, INSERT ON *.* to Mike;
Method 2: Use one SQL statement to create an account and grant it permissions.
GRANT SELECT, INSERT ON *.* to Mike IDENTIFIED BY 'your#password';
Example 2: Create a database-level account with all permissions. Create account david in database testdb and grant the SELECT permissions of database testdb to the account.
Method 1: Create an account and then grant permissions to it.
CREATE USER david IDENTIFIED BY 'your#password'; GRANT SELECT ON testdb.* to david;
Method 2: Use one SQL statement to create an account and grant it permissions.
GRANT SELECT ON testdb.* to david IDENTIFIED BY 'your#password';
Example 3: Create a table-level account with all permissions. Create account hanson in database testdb and grant all permissions of table testdb.employees to the account.
GRANT ALL PRIVILEGES ON testdb.employees to hanson IDENTIFIED BY 'your#password';
REVOKE
Syntax:
REVOKE priv_type [, priv_type] ... ON priv_level FROM user;
Example: Deleting CREATE, DROP, and INDEX permissions of user hanson on table testdb.emp.
REVOKE CREATE,DROP,INDEX ON testdb.emp FROM hanson;
REVOKE can delete actions at each permission level of an account. The permission level is specified by priv_level.
SHOW GRANTS
Syntax:
SHOW GRANTS FOR user;
Example 1: Querying user permissions with any of the following statements:
SHOW GRANTS; SHOW GRANTS FOR CURRENT_USER; SHOW GRANTS FOR CURRENT_USER();
Example 2: Querying other permissions. This operation can be performed only when the current user can grant user-level permissions.
mysql> show grants for david; +-----------------------------+ |Grants for david | +-----------------------------+ |GRANT USAGE ON *.* TO david | +-----------------------------+ 1 row in set (0.00 sec)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.