Overview of SQL Access Privileges

Go Up to Planning Security

SQL security is controlled at the table level with access privileges, a list of operations that a user is allowed to perform on a given table or view. The GRANT statement assigns access privileges for a table or view to specified users, to a role, or to objects such as stored procedures or triggers. GRANT can also enable users or stored procedures to execute stored procedures through the EXECUTE privilege and can grant roles to users. Use REVOKE to remove privileges assigned through GRANT.

GRANT can be used in the following ways:

• Grant SELECT, INSERT, UPDATE, DELETE, and REFERENCES privileges for a table to users, triggers, stored procedures, or views (optionally WITH GRANT OPTION).
• Grant SELECT, INSERT, UPDATE, and DELETE privileges for a view to users, triggers, stored procedures, or views (optionally WITH GRANT OPTION).
• Grant SELECT, INSERT, UPDATE, DELETE, DECRYPT, and REFERENCES privileges for a table to a role.
• Grant SELECT, INSERT, UPDATE, DECRYPT, and DELETE privileges for a view to a role.
• Grant ENCRYPT ON ENCRYPTION permission to a user.
• Grant a role to users (optionally WITH ADMIN OPTION).
• Grant EXECUTE permission on a stored procedure to users, triggers, stored procedures, or views (optionally WITH GRANT OPTION).

Topics

• Default Security and Access
• Privileges Available
• SQL ROLES