Manage account permissions - AnalyticDB - Alibaba Cloud Documentation Center

By default, standard accounts do not have permissions. Privileged accounts (including the initial account) can grant operation permissions on database objects to standard accounts.

You can use one of the following methods to grant permissions to database accounts:

Grant operation permissions on database objects to database accounts.
Grant account permissions based on roles.
This method simplifies permission management for multiple database accounts. For example, if you want to grant read permissions on a table to multiple database accounts, you can grant read permissions on the table to a role and then assign the role to multiple accounts.

Grant operation permissions on database objects to database accounts

When a database object such as a database, schema, table, view, sequence, or function is created, all permissions of the object belong to the owner of the object. By default, only the object owner and the database account that has the RDS_SUPERUSER permission have permissions to perform operations on the object. If other database accounts want to perform operations on the object, the account must have the required permissions.

The following table describes the permissions on different database objects.

Database object	Permission
Tables, views, and sequences	SELECT INSERT UPDATE DELETE RULE ALL
Table columns	SELECT INSERT UPDATE
External tables	SELECT RULE ALL
Databases	CONNECT CREATE TEMPORARY \| TEMP ALL
Functions	EXECUTE
Procedural Languages	USAGE
Schemas	CREATE USAGE ALL

Important

You must separately grant permissions to each type of objects. For example, if you grant the GRANT ALL permission on a database, you cannot access tables in the database. You have the CONNECT, CREATE, and TEMP permissions only on the database.

Grant the INSERT permission on the mytable table to jsmith. Sample statement:
```
GRANT INSERT ON mytable TO jsmith;
```
Grant the SELECT and TRUNCATE permissions on all tables in the myschema schema to jsmith. Sample statement:
```
GRANT SELECT,TRUNCATE ON ALL TABLES IN SCHEMA myschema TO jsmith;
```
Grant the SELECT permission on the col1 column of the mytable table to jsmith. Sample statement:
```
GRANT SELECT (col1) on TABLE mytable TO jsmith;
```
Revoke all permissions on the mytable table from jsmith. Sample statement:
```
REVOKE ALL PRIVILEGES ON mytable FROM jsmith;
```

Grant account permissions based on roles

You can grant database account permissions based on roles. You can create a role, grant permissions to the role, and then assign the role to one or more database accounts. After you complete the operations, the account inherits all the permissions of the role. The following section shows an example on how to grant account permissions based on roles.

Create a role named admin that has the CREATEROLE and CREATEDB permissions.

CREATE ROLE admin CREATEROLE CREATEDB;

Assign the admin role to john, sally, and bob.

GRANT admin TO john, sally, bob;

Revoke the admin role from bob.

REVOKE admin FROM bob;

Grant permissions on database objects to the admin role. This way, all database accounts that have the admin role inherit the permissions.

GRANT ALL ON TABLE mytable TO admin;
GRANT ALL ON SCHEMA myschema TO admin;
GRANT ALL ON DATABASE mydb TO admin;

FAQ

How do I grant the DROP TABLE permission?

You cannot execute the GRANT statement to grant database accounts the permissions to delete tables. However, the owner of a table or schema can delete the table.

You can use a privileged account or the owner of a table or schema to change the owner of the table or schema to another database account. This way, the new owner can delete the table.

-- Change the owner of the mytable table to new_owner. This way, new_owner can delete the mytable table. 
ALTER Table mytable OWNER TO new_owner; 

-- Change the owner of the myshema schema to new_role. This way, new_owner can delete the mytable table and all the other tables in the myschema schema. 
ALTER SCHEMA myschema OWNER TO new_owner;

If multiple database accounts need to have the permission to delete the same table or schema, you can leverage role permission inheritance to change the owner of the table or schema to a specific role and assign the role to multiple accounts.

-- Create a role named new_role. 
CREATE ROLE new_role; 

-- Change the owner of the mytable table to new_role. This way, new_role can delete the mytable table. 
ALTER Table mytable OWNER TO new_role; 

-- Change the owner of the myshema schema to new_role. This way, new_owner can delete the mytable table and all the other tables in the myschema schema. 
ALTER SCHEMA myschema OWNER TO new_role; 

-- Grant the permissions of new role to myuser. 
GRANT new_role To myuser;

References

For information about how to create privileged accounts and standard accounts, see Create and manage a database account.
For information about how to grant account access and revoke account access permissions, see the "GRANT" and "REVOKE" sections of the SQL syntax topic.