Java Reference
In-Depth Information
Granting and revoking user privileges
The SQL GRANT command is used to grant users the necessary access privileges to
perform various operations on the database. In addition to granting a user specified
access privileges, the GRANT command can be used to allow the user to grant a
privilege to other users. There is also an option allowing the user to grant privileges
on all subtables and related tables. These two versions of the GRANT command look
like this:
GRANT privilege ON table_name TO user_name;
GRANT SELECT ON PRODUCTS WITH GRANT OPTION TO jdoe;
The REVOKE command is used to revoke privileges granted to a user. Like the
GRANT command, this command can be applied at various levels.
The REVOKE command is used to revoke privileges from users so that they cannot
do certain tasks on the database. Just like the GRANT command, this command can
be applied at various levels. It is important to note that the exact syntax of this
command might differ as per your database. For example, the following command
revokes the SELECT privileges from John Doe, on the Products Table.
REVOKE SELECT ON PRODUCTS FROM jdoe
Creating and Using Stored Procedures
A stored procedure is a saved collection o f SQL statements that can take and return
user-supplied parameters. You can think of a stored procedure as a method or
function, written in SQL. There are obviously a number of advantages to using stored
procedures, including:
Stored procedures are precompiled, so they will execute fast.
Stored procedures provide a standardised way of performing common tasks.
Almost any SQL statement can be used as a stored procedure. All that is required is
to provide a procedure name and a list of variables:
CREATE PROCEDURE procedure_name
@parameter data_type,
@parameter data_type = default_value,
@parameter data_type OUTPUT
AS
sql_statement [ ...n ]
Variable names are specified using an at sign @ as the first character. Otherwise the
name must conform to the rules for identifiers. Variable names cannot be used in
