Format:   REVOKE {SELECT | INSERT | UPDATE | DELETE | REFERENCES} [column_names]
              ON {table_name | view_name}
              TO {user_id | PUBLIC} [CASCADE | RESTRICT];

The privileges that have been granted with the GRANT statement can be removed with the REVOKE statement. The REVOKE statement syntax is very similar to the GRANT syntax. It simply says that you are removing privileges on specific database objects from one or more database users.
> Many RDBMS systems use a REVOKE similar to DB2’s format which allows you to specify a list of tables and a list of user-IDs. This makes it easier to revoke many privileges at once.

Using a view to restrict row access:

 

 

Here the user, JAMES, has been given SELECT, INSERT, UPDATE, and DELETE privileges on the MANAGER_VIEW, while PATRICK has the same privileges on the EMPLOYEE_VIEW. But this does not give either JAMES or PATRICK any access privileges to the base table EMPLOYEE_TBL.

Using a view to restrict column access:

 

Here the user, ACTGUSER, has been given SELECT privileges on the EMPLOYEE_VIEW.
But this does not give ACTGUSER any access privileges to the base table EMPLOYEE_TBL,
so ACTGUSER can not access RATE_HOUR information.

Format:   GRANT ALL PRIVILEGES
             ON table_name
             TO {user_id | PUBLIC} [WITH GRANT OPTIONS];

ALL PRIVILEGES is used to give all privileges the grantor has to grant to another user.
Generally this is not a good idea, because you may presume that a user has certain rights when they don’t.

Format:   GRANT {SELECT | INSERT | UPDATE | DELETE} [column_names]
             ON table_name
             TO {user_id | PUBLIC} [WITH GRANT OPTIONS];

Let us restate: When you create a database object, you own it and you are the only person who can grant privileges to others. When you grant privileges to them, they can use those privileges, but they can not grant those privileges to others. This allows you to give users access to objects, but they can not give access to others.

If you wish to relinquish this control, you can add WITH GRANT OPTIONS when you grant privileges to others. Now it is like they also own the database object and they can grant privileges to as many people as they wish.

Format:   GRANT {SELECT | INSERT | UPDATE | DELETE} [column_names]
             ON {table_name | view_name}
             TO {user_id | PUBLIC} [WITH GRANT OPTIONS];

When you GRANT privileges TO PUBLIC, it means that you are giving privileges on this table to all current and future users who can access the database or schema. Obviously you should only use this privilege with extreme care and some installations never use this option.

Format:   GRANT {SELECT | INSERT | UPDATE | DELETE | REFERENCES} [column_names]
             ON {table_name | view_name}
             TO {user_id | PUBLIC} [WITH GRANT OPTIONS];

When you create a database object, you own it and you are the only person who can grant privileges to others. When you grant privileges to them, they can use those privileges, but they can not grant those privileges to others. This allows you to give users access to objects, but they can not give access to other users.

If you limit the GRANT to specific column_names, this option normally applies only to the INSERT and UPDATE privileges. You limit the ability to SELECT specific columns by creating a VIEW with the specific columns and then GRANT SELECT on that VIEW.
> SQL Server and Sybase do allow specific column_names to be specified in the SELECT, allowing you to define fewer views.

> Many RDBMS systems use a GRANT similar to DB2’s format, which allows you to specify a list of tables and a list of user-IDs. This makes it easier to grant many privileges at once.

The Data Control Language is for data security and has four commands: GRANT, REVOKE, COMMIT, and ROLLBACK.The first line of security is the users for whom you GRANT or REVOKE access and update privileges to your data. Some people should never have access to your data while others should have only restricted access. Some people can only see certain data while others have the right to update that data. Only someone like your Database Administrator (DBA) and the owner of the company should have access to all the data.

If you create a schema, you are designated as the owner of that schema. As the owner of the schema, you can grant access privileges to other users. All privileges are withheld until they are explicitly granted by you. A users must go through an authorization process to prove their identity before they can perform the functions you have authorized. You can revoke any privileges you have previously granted.
If you GRANT use of the SELECT command, a user can see a database object such as a table or view.

If you GRANT use of the INSERT command, a user can add table rows.

If you GRANT use of the UPDATE command, a user can modify table rows.

If you GRANT use of the DELETE command, a user can delete table rows.

If you GRANT use of the REFERENCES command, a user can see data where SELECT has been granted to a table and that table references another table.

If you GRANT use of the USAGE command, a user can allow or disallow use of domains, user-defined character sets, collating sequences, and translations.

If you GRANT the ALL command, you are granting all privileges to this user.
You can REVOKE any of these privileges from users.

COMMIT and ROLLBACK make changes permanent or reverse the changes.

When you create a table with a CREATE TABLE statement, you become the owner of that table and you have full privileges for the table (such as SELECT, INSERT, UPDATE, DELETE, GRANT, REVOKE, etc.). Nobody else has any privileges on your newly created table. If others are to be given access to the table, you must explicitly grant privileges to them using the GRANT statement.When you create a view with a CREATE VIEW statement, you own the view, but you can not create the view unless you already have SELECT privileges on all source tables for the view. Since you already have SELECT privileges on the view source tables, you now have SELECT privileges on the view you created. You do not have INSERT, UPDATE, and DELETE privileges for the view you created unless you have previously been granted INSERT, UPDATE, and DELETE privileges for the source tables of your view.

SELECT privileges on a VIEW can be granted to others without giving them any privileges on the source tables of the VIEW. You can effectively limit the columns and/or rows in the view for security reasons, denying other users access to the source table columns and rows. A view can also combine columns from two or more source tables, making the columns appear to be in one table.

If a user must be able to update data in a VIEW, then that user must be given INSERT, UPDATE, and/or DELETE privileges to the source tables for the VIEW.

How does SQL know to associate your user-ID with the statement or program being executed?When you sign-on to an interactive SQL computer system, you generally enter a user-ID and a password. Once you have signed-on to the system, your user-ID has been established until you sign-off or cancel the computer session.

On a database server, when you sign-on, your user-ID is established by the work station you are using.
If you use a query program or a forms-based system, again your sign-on has established your user-ID.

When several people have identical needs, you can create a user-ID for a user group. If everyone in the payroll department can perform the same commands on the PAYROLL_TBL, then they can be given the same user-ID and password or they can be assigned their own user-ID and password.

For example:
Users from the payroll department may have user-IDs of PRUSER.
Users from the accounts payable department may have user-IDs of APUSER.
The president of the company may have a user-ID of WILLIAMD.
The database administrator may have a user-ID of K3R7CFC or DBA1.
> DB2 allows a single user to have a primary user-ID, and multiple secondary user-IDs.

> SQL Server and Sybase allow a single user to have both a single user-ID and a group user-ID.