Database Security Handout

Database Security Handout

• 1.
  F0004 * Property ofSTI Page 1 of 11 Database Security Introduction to Database Security  The security issues that the database users or administrators must address are broken down into basic situations as follows:  Accessing data in a table should be limited only to qualified users.  Modifying tables must be limited only to qualified users with administrator rights.  Accessibility to columns/rows of a table must be defined for certain users. 1 _________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ F0004 * Property of STI Page 3 of 11 Database Security The SQL Security Model Figure 11.1 Assigning Privileges 3 __________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ F0004 * Property of STI Page 2 of 11 Database Security The SQL Security Model  The SQL security model provides a basic syntax used to specify security restrictions.  There are three concepts of SQL security as follows:  Users  Objects  Privileges 2 _________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ F0004 * Property of STI Page 4 of 11 Database Security User IDs  Setting up security begins with creating user IDs and passwords that varies enormously from DBMS to DBMS.  In a secure DBMS, a user ID is a name/password pair that allows some entity to perform actions in the database. 4 __________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________
• 2.
  F0004 * Property ofSTI Page 5 of 11 Database Security Objects  The SQL standard defines security in terms of objects to which actions are performed.  In the SQL1 standard, the only objects that security is applied are tables and views. Figure 11.2 Using Grant 5 _________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ F0004 * Property of STI Page 7 of 11 Database Security Privileges  There are two types of privileges:  System privileges  Object privileges  SQL supports the following object privileges:  SELECT  INSERT  DELETE  UPDATE  REFERENCES  ALTER  INDEX 7 __________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ F0004 * Property of STI Page 6 of 11 Database Security Privileges  Privileges are issued via the GRANT command and are taken away via the REVOKE command.  The privileges that can be granted are divided into four groups:  Column privileges relate to one specific column of a table.  Table privileges relate to all data of one specific table.  Database privileges relate to all tables of one specific database.  User privileges relate to all databases that are known to SQL. 6 _________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ F0004 * Property of STI Page 8 of 11 Database Security Adding Users  Some DBMSs have SQL statements, extensions to the SQL standard specific to that DBMS that allow creating users.  In Oracle the statement is as follow: CREATE USER username IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS external_name} Options  In Sybase, the syntax is as follow: GRANT CONNECT TO userid , . . . [ AT starting-id ] IDENTIFIED BY password, . . . 8 __________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________
• 3.
  F0004 * Property ofSTI Page 9 of 11 Database Security Granting Privileges  The basic GRANT statement is used to grant security privileges on database objects to specific users or, in some DBMS implementations, to groups.  The syntax is as follow: GRANT { ALL [ PRIVILEGES ], ALTER, DELETE, INSERT, REFERENCES [ ( column- name, . . . ) ], SELECT [ ( column-name, . . . ) ], UPDATE [ ( column-name, . . . ) ], } ON [ owner.]table-name TO userid , . . . [ WITH GRANT OPTION ] [ FROM userid ] 9 _________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ F0004 * Property of STI Page 11 of 11 Database Security Revoking Privileges  Use the REVOKE statement if you have granted privileges and later you need to revoke these privileges.  Syntax 1 REVOKE special-priv , . . . FROM userid , . . . special-priv : CONNECT  Syntax 2 REVOKE table-priv , . . . ON [ owner.]table-name FROM userid , . . . table-priv : ALL [PRIVILEGES] | ALTER | DELETE | INSERT | REFERENCES [ ( column-name, . . . ) ] | SELECT [ ( column-name, . . . ) ] | UPDATE [ ( column-name, . . . ) ] 11 _________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ F0004 * Property of STI Page 10 of 11 Database Security Granting Privileges  Example: GRANT SELECT, UPDATE ( street ) ON employee TO Laurel 10 ________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________ ___________________