Data control language for Aurora MySQL

This topic provides reference information foruser permissions and access control in Amazon Aurora MySQL compared to Microsoft SQL Server. You can understand the similarities and differences in how these database systems manage user privileges, including the types of permissions available, the granularity of access control, and the commands used to grant or revoke permissions.

SQL Server Usage

The ANSI standard specifies, and most Relational Database Management Systems (RDBMS) use GRANT and REVOKE commands to control permissions.

However, SQL Server also provides a DENY command to explicitly restrict access to a resource. DENY takes precedence over GRANT and is needed to avoid potentially conflicting permissions for users having multiple logins. For example, if a user has DENY for a resource through group membership but GRANT access for a personal login, the user is denied access to that resource.

SQL Server allows granting permissions at multiple levels from lower-level objects such as columns to higher level objects such as servers. Permissions are categorized for specific services and features such as the service broker.

Permissions are used in conjunction with database users and roles.

For more information, see Users and Roles.

Syntax

The following examples show the simplified syntax for SQL Server DCL commands:

GRANT { ALL [ PRIVILEGES ] } | <permission> [ ON <securable> ] TO <principal>

DENY { ALL [ PRIVILEGES ] } | <permission> [ ON <securable> ] TO <principal>

REVOKE [ GRANT OPTION FOR ] {[ ALL [ PRIVILEGES ] ]|<permission>} [ ON <securable> ] { TO | FROM } <principal>

For more information, see Permissions Hierarchy (Database Engine) in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) supports the ANSI Data Control Language (DCL) commands GRANT and REVOKE.

Administrators can grant or revoke permissions for individual objects such as a column, a stored function, or a table. Administrators can grant permissions to multiple objects using wildcards.

Only explicitly granted permissions can be revoked. For example, if a user was granted SELECT permissions for the entire database using the following command:

GRANT SELECT
ON database.*
TO UserX;

It isn’t possible to REVOKE the permission for a single table. Instead, revoke the SELECT permission for all tables using the following command:

REVOKE SELECT
ON database.*
FROM UserX;

Aurora MySQL provides a GRANT permission option, which is very similar to the WITH GRANT OPTION clause in SQL Server. This permission gives a user permission to further grant the same permission to other users.

GRANT EXECUTE
ON PROCEDURE demo.Procedure1
TO UserY
WITH GRANT OPTION;

The following table identifies Aurora MySQL privileges:

Syntax

GRANT <privilege type>...
ON [object type] <privilege level>
TO <user> ...

REVOKE <privilege type>...
ON [object type] <privilege level>
FROM <user> ...

Examples

Attempt to REVOKE a partial permission that was granted as a wild card permission.

CREATE USER TestUser;
GRANT SELECT
    ON Demo.*
    TO TestUser;
REVOKE SELECT ON Demo.Invoices
    FROM TestUser

For the preceding example, the result looks as shown following.

SQL ERROR [1147][42000]: There is no such grant defined for user TestUser on host '%'
on table 'Invoices'

Grant the SELECT permission to a user on all tables in the demo database.

GRANT SELECT
ON Demo.*
TO 'user'@'localhost';

Revoke EXECUTE permissions from a user on the EmployeeReport stored procedure.

REVOKE EXECUTE
ON Demo.EmployeeReport
FROM 'user'@'localhost';

For more information, see GRANT Statement in the MySQL documentation.