Grant privileges to a role

After you create a role, you must grant privileges to the role so that users can use the privileges through the role.

Prerequisites

To grant privileges to a role, the current user must have the privileges to be granted and the GRANT OPTION privilege. For information about how to view the privileges of the current user, see View user privileges. If you do not have the required privileges, contact your administrator to grant privileges to you. For information about how to directly grant privileges to a user, see Directly grant privileges.

Procedure

• Grant the ALTER SYSTEM privilege to the role1 role.

  GRANT ALTER SYSTEM ON *.* TO role1;

• Grant the SELECT privilege on all objects in the dbtest database to the role1 role.

  GRANT SELECT ON dbtest.* TO role1;

  You can grant one or more privileges to multiple roles at a time.

  GRANT SELECT,UPDATE ON dbtest.* TO role1,role2;

• Grant the SELECT privilege on the test1.tbl1 table to the role1 role.

  GRANT SELECT ON test1.tbl1 TO role1;

• Grant the SELECT privilege on the c1 column of the test1.tbl1 table to the role1 role.

  GRANT SELECT(c1) ON test1.tbl1 TO role1;

References

• For more information about the GRANT statement, see GRANT.

• For information about all supported privileges, see Privilege types.