Controlling User Access

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 19.  Managing Database Security


Controlling User Access

User access is primarily controlled by a user account and password, but that is not enough to access the database in most major implementations . The creation of a user account is only the first step in allowing access to the database, as well as controlling that access.

After the user account has been created, the database administrator, security officer, or designated individual must be able to assign appropriate system-level privileges to a user for that user to be allowed to perform actual functions within the database, such as creating tables or selecting from tables. Furthermore, the schema owner usually needs to grant database users access to objects in the schema so that the user can do his or her job.

There are two commands in SQL that allow database access control involving the assignment of privileges and the revocation of privileges. The following are the two commands used to distribute both system and object privileges in a relational database:

GRANT

REVOKE

The GRANT Command

The GRANT command is used to grant both system-level and object-level privileges to an existing database user account.

The syntax is as follows :

 graphics/syntax_icon.gif GRANT PRIVILEGE1 [, PRIVILEGE2 ][ ON OBJECT ] TO USERNAME [ WITH GRANT OPTION  ADMIN OPTION] 

Granting one privilege to a user is as follows:

 graphics/input_icon.gif  GRANT SELECT ON EMPLOYEE_TBL TO USER1;  graphics/output_icon.gif Grant succeeded. 

Granting multiple privileges to a user is as follows:

 graphics/input_icon.gif  GRANT SELECT, INSERT ON EMPLOYEE_TBL TO USER1;  graphics/output_icon.gif Grant succeeded. 

Notice that when granting multiple privileges to a user in a single statement, each privilege is separated by a comma.

Granting privileges to multiple users is as follows:

 graphics/input_icon.gif  GRANT SELECT, INSERT ON EMPLOYEE_TBL TO USER1, USER2;  graphics/output_icon.gif Grant succeeded. 
graphics/note_icon.gif

Notice the phrase Grant succeeded denoting the successful completion of each grant statement. This is the feedback that you receive when you issue these statements in the implementation used for the book examples (Oracle). Most implementations have some sort of feedback, although the phrase used may vary.


graphics/note_icon.gif

MySQL uses the GRANT command both for creating users and granting privileges to users.


The syntax to create a user in MySQL is as follows:

 graphics/syntax_icon.gif GRANT USAGE ON DATABASE.TABLES TO USERNAME@HOST IDENTIFIED BY 'PASSWORD'; 
GRANT OPTION

GRANT OPTION is a very powerful GRANT command option. When an object's owner grants privileges on an object to another user with GRANT OPTION, the new user can also grant privileges on that object to other users, even though the user does not actually own the object. An example follows:

 graphics/input_icon.gif  GRANT SELECT ON EMPLOYEE_TBL TO USER1 WITH GRANT OPTION;  graphics/output_icon.gif Grant succeeded. 
ADMIN OPTION

ADMIN OPTION is similar to GRANT OPTION in that the user that has been granted the privileges also inherits the ability to grant those privileges to another user. GRANT OPTION is used for object-level privileges, whereas ADMIN OPTION is used for system-level privileges. When a user grants system privileges to another user with ADMIN OPTION, the new user can also grant the system-level privileges to any other user. An example follows:

 graphics/mysql_icon.gif graphics/input_icon.gif  GRANT CREATE TABLE TO USER1 WITH ADMIN OPTION;  graphics/output_icon.gif Grant succeeded. 
graphics/note_icon.gif

When a user that has granted privileges using either GRANT OPTION or ADMIN OPTION has been dropped from the database, the privileges that the user granted are disassociated with the users to which the privileges were granted.


The REVOKE Command

The REVOKE command removes privileges that have been granted to database users. The REVOKE command has two options: RESTRICT and CASCADE. When the RESTRICT option is used, REVOKE succeeds only if the privileges specified explicitly in the REVOKE statement leave no other users with abandoned privileges. The CASCADE option revokes any privileges that would otherwise be left with other users. In other words, if the owner of an object granted USER1 privileges with GRANT OPTION, USER1 granted USER2 privileges with GRANT OPTION, and then the owner revokes USER1's privileges, CASCADE also removes the privileges from USER2.

graphics/newterm_icon.gif

Abandoned privileges are privileges that are left with a user who was granted privileges with the GRANT OPTION from a user who has been dropped from the database or had his/her privileges revoked .

The syntax is as follows:

 graphics/syntax_icon.gif REVOKE PRIVILEGE1 [, PRIVILEGE2 ] [ GRANT OPTION FOR ] ON OBJECT FROM USER { RESTRICT  CASCADE } 

The following is an example:

 graphics/input_icon.gif  REVOKE INSERT ON EMPLOYEE_TBL FROM USER1;  graphics/output_icon.gif Revoke succeeded. 

Controlling Access on Individual Columns

Instead of granting object privileges (INSERT, UPDATE, or DELETE) on a table as a whole, you can grant privileges on specific columns in the table to restrict user access, as shown in the following example example:

 graphics/input_icon.gif  GRANT UPDATE (NAME) ON EMPLOYEES TO PUBLIC;  graphics/output_icon.gif Grant succeeded. 

The PUBLIC Database Account

The PUBLIC database user account is a database account that represents all users in the database. All users are part of the PUBLIC account. If a privilege is granted to the PUBLIC account, all database users have the privilege. Likewise, if a privilege is revoked from the PUBLIC account, the privilege is revoked from all database users, unless that privilege was explicitly granted to a specific user. The following is an example:

 graphics/input_icon.gif  GRANT SELECT ON EMPLOYEE_TBL TO PUBLIC;  graphics/output_icon.gif Grant succeeded. 
graphics/cautions_icon.gif

Extreme caution should be taken when granting privileges to PUBLIC; all database users acquire the privileges granted.


Groups of Privileges

Some implementations have groups of privileges in the database. These groups of permissions are referred to with different names . Having a group of privileges allows simplicity for granting and revoking common privileges to and from users. For example, if a group consists of ten privileges, the group can be granted to a user instead of all ten privileges.

graphics/newterm_icon.gif

SQLBase has groups of privileges called authority levels, whereas these groups of privileges in Oracle are called roles. SQLBase and Oracle both include the following groups of privileges with their implementations:

CONNECT

RESOURCE

DBA

The CONNECT group allows a user to connect to the database and perform operations on any database objects to which the user has access.

The RESOURCE group allows a user to create objects, drop objects he or she owns, grant privileges to objects he or she owns, and so on.

The DBA group allows a user to perform any function within the database. The user can access any database object and perform any operation with this group.

An example for granting a group of privileges to a user follows:

 graphics/mysql_icon.gif graphics/input_icon.gif  GRANT DBA TO USER1;  graphics/output_icon.gif Grant succeeded. 
graphics/note_icon.gif

Each implementation differs on the use of groups of database privileges. If available, this feature should be used for ease of database security administration.



Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net