Introduction to Database Administration

1
Introduction to Database AdministrationSecurity
PART 1
2
DATABASE ADMINISTRATION

• Examine some basic DBA duties
• Starting/stopping a database
• Managing user accounts
• creating users
• assigning users privileges
• removing users

3
The Database Administrator

• Database Administrator (DBA)
• Experienced senior member(s) of the computing
  staff who plan and co-ordinate the development
  and daily maintenance of the entire database
  environment.
• Has an extensive working knowledge of host
  languages and all major sub-language components.
• Usually the role of DBA is shared between 2 or 3
  such employees for security purposes.

4
DBA Duties

• A typical DBAs duties include
• Overseeing new database developments, database
  re-organisation
• Maintaining an acceptable level of technical
  performance for database utilities
• Establishing integrity controls, user access
  privileges
• Overseeing that adequate backup and recovery
  procedures are in place and enforced
• Resource usage monitoring
• Facilitating end-users with required database
  facilities
• Setting standards for system documentation
• Educating the organisation in the use,
  capabilities and availability of the database

5
Managing User Accounts

• The everyday users of a database need to be able
  access manipulate various database objects in a
  safe and controlled manner.
• The DBA creates a USER ACCOUNT for each user by
  following these basic steps
• Create a username/password protected account and
  assign the user to a table space.
• Allocate the user limited disk storage space
  (quota) within the table space
• Grant the user limited privileges to log in/out
  of the account, create/destroy and manipulate
  database objects within the account.

6
Creating User Accounts

• The CREATE USER command (Create a user account)
• CREATE USER username
• IDENTIFIED BY password

7
Creating User Accounts

• Example
• create user opsfarmer
• identified by puma
• NOTE the ops prefix can be used so that the new
  oracle user account is paired with an existing
  operating system account for the same user (via
  the username). Once farmer has logged into the op
  sys account, he can log into the oracle account
  without entering the username password.
• Example
• gt sqlplus /

8
Changing a password for A user

• ALTER USER username
• identified by newpassword
• Example
• Alter user opsfarmer
• identified by sam10

9
Managing User Accounts

• Removing a user account
• DROP USER username CASCADE
• The cascade option drops all objects in the
  users schema before dropping the user. If the
  user owns objects you must specify this parameter
  to drop the user.

10
Introduction to DB Security

• Secrecy Users should not be able to see things
  they are not supposed to.
• E.g., A student cant see other students grades.
• Integrity Users should not be able to modify
  things they are not supposed to.
• E.g., Only instructors can assign grades.
• Availability Users should be able to see and
  modify things they are allowed to.

11
Elements in SQL Security Model
Master db
db
table
view
column
view column
12
Security and Access Control

• Security is the prevention of unauthorized access
  to the database
• Two Access Control mechanisms
• Discretionary access control GRANT/REVOKE
  commands on object and system privileges
• Views

13
Discretionary Access Control

• Based on the concept of access rights or
  privileges for tables and views, and mechanisms
  for giving and revoking privileges users
  privileges.
• Creator of an Oracle object a table or a view,
  etc, automatically gets all privileges on it.
• DMBS keeps track of who subsequently gains and
  loses privileges, and ensures that only requests
  from users who have the necessary privileges (at
  the time the request is issued) are allowed.

14
Schemas

• Global Schema comprises many individual User
  Schemas

• Each user Owns the tables, views etc in their
  own schema
• has ALL privileges on these tables/views

15
Controlling Privileges

• Privileges give users access to data that they do
  not own.
• Two types
• Object Privileges Can be give to tables, views,
  sequences, etc
• For Tables Select, Insert, Update, Delete,
  Create, Drop, Alter.
• System Privileges are stored inside Oracle
  dictionary. To list all of them, use
• select name from system_privilege_map
• In Oracle, Roles - (groups of privileges), are
  available to ease the administration of
  privileges.
• Explicit privileges can be assigned individually
  through the use of the GRANT and REVOKE commands.

16
Controlling Privileges Cont.

• The owner of an object may grant privileges on
  the object to other users
• e.g. Scott may grant select privilege on his EMP
  table to NewUser
• Grant select on Emp to NewUser
• NewUser may now access the data in the EMP table
  in Scotts domain
• select from scott.emp

17
Controlling User Access Privileges

• The GRANT command
• GRANT privilege_list (database_objects)
• ON object_name TO username
• WITH GRANT OPTION
• The GRANT command can be used to grant OBJECT or
  SYSTEM privileges to users as the DBA deems
  necessary.
• When granting OBJECT privileges the optional ON
  clause can be included, when granting SYSTEM
  privileges it can be omitted.

18
Object Privilege List
19
GRANT and REVOKE of Privileges Cont.

• GRANT INSERT, SELECT ON emp TO Horatio
• Horatio can query Emp or insert tuples into it.
• GRANT DELETE ON Emp, Dept TO Yuppy WITH GRANT
  OPTION
• Yuppy can delete tuples, and also authorize
  others to do so.
• GRANT UPDATE (annsalary) ON emp TO Dustin
• Dustin can update (only) the annsalary field of
  Emp tuples.
• GRANT SELECT ON dept TO public
• This allows all users to query Emp, Dept tables
  directly
• REVOKE When a privilege is revoked from X, it
  is also revoked from all users who got it solely
  from X.
• Revoke insert, select on emp from scott
• Revoke all privileges on emp, dept from scott

20
Object Privileges and Roles

• This is OK but very time consuming if many users
  and/or objects
• Use ROLE to group together all users who will be
  given the same privileges on a set of objects
• Default ROLE
• PUBLIC created in ALL Oracle systems
• Grant select on Emp to PUBLIC

21
Object Privileges and Roles cont.

• New roles can be created by DBA and users
  associated with these roles
• create role staff
• Roles can then be granted to users
• grant staff to fred
• The owner of an object can now give privileges to
  the staff role
• grant insert, select
• on stockitem
• to staff

22
Creating new Roles

• The new role can now be granted to a user
• GRANT role_name TO username
• Example
• GRANT application_creator TO opsfarmer

23
System Level Roles

• Oracle DBAs could grant 3 levels of privileges
  to users
• CONNECT
• RESOURCE
• DBA
• In Oracle7 the DBA has the extended ability to
  create Roles (groups of privileges) that can be
  tailored to suit an individuals database needs.

24
Connect, Resource DBA Roles

• Example of granting a traditional role to a user
  account
• GRANT resource TO ubakang WITH GRANT OPTION

25
Creating new Roles

• The CREATE ROLE command
• CREATE ROLE role_name
• GRANT privilege(s) TO role_name
• Example
• CREATE ROLE application_creator
• GRANT CREATE SESSION, CREATE USER
• TO application_creator
• The example above creates a role
  application_creator, then assigns that role the
  privileges of being able to log in (CREATE
  SESION) and to create new user accounts (CREATE
  USER).

26
Revoking Roles Privileges

• To remove a role or particular privileges from a
  user, the REVOKE command is used.
• REVOKE (role_name, privilege)
• FROM username/role_name
• Example 1 revoking a privilege from a user
• REVOKE create user FROM opsfarmer
• Example 2 revoking a privilege from a role
• REVOKE create user FROM application_creator
• Example 3 revoking a role from a user
• REVOKE application_creator FROM opsfarmer