Syntax Examples for ROLE

ROLE

  1. CREATE ROLE

Example 1. Create a role

USE ROLE SECURITYADMIN;

CREATE ROLE END_USER_ROLE COMMENT='Read Only Role for End users';
Role END_USER_ROLE successfully created.

2. ALTER ROLE

Example 2. Rename a role

USE ROLE SECURITYADMIN;


ALTER ROLE END_USER_ROLE RENAME TO SUPPORT_USER_ROLE;
Statement executed successfully.

Example 3. Update or Set a comment

USE ROLE SECURITYADMIN;

ALTER ROLE SUPPORT_USER_ROLE SET COMMENT='Support User Role';
Statement executed successfully.

Example 4. Rename a Role if it exists

USE ROLE SECURITYADMIN;

-- DUMMY role does not exist
ALTER ROLE DUMMY_ROLE RENAME TO PROD_USER_ROLE;
Role 'DUMMY_ROLE' does not exist or not authorized.


-- Dummy role still does not exist but this time command is success
ALTER ROLE IF EXISTS DUMMY_ROLE RENAME TO PROD_USER_ROLE;
Statement executed successfully.

3. DROP ROLE

Example 5. Drop role

USE ROLE SECURITYADMIN;

DROP ROLE SUPPORT_USER_ROLE;
Statement executed successfully.

-- Can also use IF EXIST clause, note DUMMY_ROLE never ever existed
DROP ROLE IF EXISTS DUMMY_ROLE;
Drop statement executed successfully (DUMMY_ROLE already dropped).

4. GRANT ROLE

Example 6. Grant role to another role

USE ROLE SECURITYADMIN;

GRANT ROLE APPLICATION_SUPPORT_RO TO ROLE APPLICATION_SUPPORT;
Statement executed successfully.

Example 7. Grant role to a user

USE ROLE SECURITYADMIN;

GRANT ROLE APPLICATION_SUPPORT TO USER PSAMMY;
Statement executed successfully.

5. REVOKE ROLE

Example 8. Revoke role from a role

USE ROLE SECURITYADMIN;

REVOKE ROLE APPLICATION_SUPPORT_RO FROM ROLE APPLICATION_SUPPORT;
Statement executed successfully.

Example 9. Revoke role from a user

USE ROLE SECURITYADMIN;

REVOKE ROLE APPLICATION_SUPPORT FROM USER PSAMMY;
Statement executed successfully.

6. SHOW ROLES

Example 10. List all roles available

USE ROLE SECURITYADMIN;

SHOW_ROLES;

-- also pattern match with LIKE
SHOW ROLES LIKE '%ACCOUNT%';

7. USE ROLES

Example 11. Set active role

USE ROLE SECURITYADMIN;
Statement executed successfully.

8. USE SECONDARY ROLES

Example 12. Enable all secondary roles

USE SECONDARY ROLE ALL;
Statement executed successfully.

-- Also disable all secondary roles
USE SECONDARY ROLE NONE;
Statement executed successfully.