Table of Contents
Access to each database or even specific tables or data fields should be restricted to certain users.
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:
|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|
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.
All the privileges of connect users plues the ability to create, alter, drop and index own tables.
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 privileges allow users to add new data to a table.
Delete privileges allow users to remove data from a table.
Select privileges allow users to select data from a table. They can be granted at the table level or at specific column levels.
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;
Users can change an entire row in the table or just specific fields.
You can grant users the ability to force referential constraints on the entire row or specific columns of a table.
The index privilege grants users the ability to create and drop indexes related to a table.
The alter privilege allows users to change the layout of the columns within the table.
Using the all keyword grants or revokes any table privileges that the user might have.
You can grant or revoke different combinations of table and column privileges in one command.
GRANT INSERT, DELETE, UPDATE
ON customer_table TO PUBLIC;
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.
A view is a logical representation of physical columns from one or multiple tables.