Table of Contents
|
Introduction
Access to each database or even specific tables or data fields should be restricted to certain users.
Privileges
Database-Level Privileges
All users must have access to a database to use data within a server. The three database-level privileges are connect, resource, and DBA.
The different autority levels are:
Privileges | Connect | Resource | DBA |
---|---|---|---|
Select, insert, update, delete, use temporary tables, and use views. | Yes | Yes | Yes |
Create, alter, drop, and index own tables. | No | Yes | Yes |
Grant, revoke, drop other owned tables, and start and stop server. | No | No | Yes |
Connect
The minimum database-level privilege.
Users with connect can perform select, insert, update, and delete statements, run stored procedures against tables, create views on tables, and create temporary tables with or without indexes.
Resource
All the privileges of connect users plues the ability to create, alter, drop and index own tables.
DBA
The creator and owner of a database is automatically given DBA privileges. A DBA has the same privileges as the connect and resource users with added abilities. The added abilities include granting and revoking connect, resource, and DBA privileges to and from other users, and dropping and altering other users' tables and views. Users with DBA privilege can also drop, start, stop, and recover the database.
Granting and Revoking Database-Level Privileges
The user who creates the database is automatically given DBA privileges, which is the only level that can perform grants and revokes.
PUBLIC: Keyword that represents all users who access the database server.
To grant connect privileges to everybody:
GRANT CONNECT TO PUBLIC;
To grant connect privileges to user1:
GRANT CONNECT TO user1;
To revoke connect privileges from everybody:
REVOKE CONNECT FROM PUBLIC;
Table-Level and Column-Level Privileges
Insert
Insert privileges allow users to add new data to a table.
Delete
Delete privileges allow users to remove data from a table.
Select
Select privileges allow users to select data from a table. They can be granted at the table level or at specific column levels.
Examples:
user1 can look at ay column or any row of the customer_table.
GRANT SELECT ON customer_table TO user1;
PUBLIC can query only the customer_id and balance columns of the customer_table.
GRANT SELECT (customer_id, balance) ON customer_table TO PUBLIC;
Update
Users can change an entire row in the table or just specific fields.
References
You can grant users the ability to force referential constraints on the entire row or specific columns of a table.
Index
The index privilege grants users the ability to create and drop indexes related to a table.
Alert
The alter privilege allows users to change the layout of the columns within the table.
All
Using the all keyword grants or revokes any table privileges that the user might have.
Combinations
You can grant or revoke different combinations of table and column privileges in one command.
GRANT INSERT, DELETE, UPDATE
ON customer_table TO PUBLIC;
Other Keywords
WITH GRANT OPTION: The user receiving the privileges can also grant the same privileges to other users.
GRANT INSERT, DELETE, SELECT, UPDATE
ON customer_table TO user1
WITH GRANT OPTION;
AS: The AS keyword allows you to perform a grant as if another user performs the grant
Stored Procedures and Triggers
Stored procedures are considered separate database entities, and because they are separate, users must have the appropriate privileges to create, edit, and execute them. Stored procedures can have access to restricted areas, so they provide a mean for users to have access to restricted areas.
Triggers provide the means to automatically process a task when other events occur in the database, such as specific data access or creation.
Views
A view is a logical representation of physical columns from one or multiple tables.