In this video you will learn how to assign or grant roles and privileges in sql anywhere database. The users can be given various roles and privileges to perform different tasks
A role-based access control model is provided for the execution of privileged operations.
A role-based security model provides complete control and granularity for the privileges you want to grant to users. Each privileged operation a user can perform in the database requires one or more system or object-level privileges.
A system privilege is a right to perform an authorized database task. For example, the CREATE TABLE system privilege allows a user to create self-owned tables.
An object-level privilege is a right to perform an authorized task on a specified object. For example, having ALTER privileges on TableA allows a user to alter that table, but not other tables.
A role is a collection of one or more system privileges, object-level privileges, or roles. You can grant roles to other roles to create a role hierarchy. Granting a role to a user is equivalent to granting the user the underlying system privileges for the role.
Each new or migrated database includes a predefined set of roles you can use to get started. These system roles act as a starting point for implementing role-based security.
There are three types of roles in the role-based security model: system roles, user-defined roles (which include user-extended roles), and compatibility roles.
View the roles and privileges a user has in SQL Central by clicking the user and viewing the details that are displayed. You can also retrieve the details using the sp_displayroles system procedure.
A privilege is a right to perform a privileged operation on the system.
For example, altering a table is a privileged operation, depending on the type of alteration you are making. There are two types of privileges: system privileges and object-level privileges. System privileges give you the general right to perform a privileged operation, while object-level privileges restrict you to performing the operation on a specific object. For example, if you have the ALTER ANY TABLE system privilege, you can alter any table in the system. If you do not, you can only edit tables you create or tables on which you have the ALTER TABLE object-level privilege.
System privileges are built in to the database and can be granted or revoked, but not created or dropped. With the exception of the MANAGE ROLES and UPGRADE ROLE privileges, system privileges cannot have system privileges granted to, or revoked from, them. Each system privilege, with the exception of the SET USER system privilege, is granted by default to either the SYS_AUTH_SA_ROLE or SYS_AUTH_SSO_ROLE compatibility role, but not both. The SET USER system privilege is granted to both roles (WITH ADMIN OPTION to SYS_AUTH_SSO_ROLE and WITH NO ADMIN OPTION to SYS_AUTH_SA_ROLE).
You grant and revoke system and object-level privileges by using the GRANT and REVOKE statements.