KEMBAR78
Database Security Slide Handout | PDF
F0004
* Property of STI
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.
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
F0004
* Property of STI
Page 3 of 11
Database Security
The SQL Security Model
Figure 11.1 Assigning Privileges
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.
F0004
* Property of STI
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
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.
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
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, . . .
F0004
* Property of STI
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 ]
F0004
* Property of STI
Page 10 of 11
Database Security
Granting Privileges
 Example:
GRANT
SELECT, UPDATE ( street )
ON employee
TO Laurel
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, . . . ) ]

Database Security Slide 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.
  • 2.
    F0004 * Property ofSTI 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
  • 3.
    F0004 * Property ofSTI Page 3 of 11 Database Security The SQL Security Model Figure 11.1 Assigning Privileges
  • 4.
    F0004 * Property ofSTI 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.
  • 5.
    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
  • 6.
    F0004 * Property ofSTI 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.
  • 7.
    F0004 * Property ofSTI 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
  • 8.
    F0004 * Property ofSTI 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, . . .
  • 9.
    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 ]
  • 10.
    F0004 * Property ofSTI Page 10 of 11 Database Security Granting Privileges  Example: GRANT SELECT, UPDATE ( street ) ON employee TO Laurel
  • 11.
    F0004 * Property ofSTI 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, . . . ) ]