8. Users, Authorization & Security

Creating and Dropping users

To create a user in MySQL

Username user can be followed by @ and the IP or the hostname from which the user is allowed to log in. Example:

To drop a user: DROP USER user

Authorization

control of what users can do in the database what they can see, create, modify, delete etc

Exercise 1

What privileges are needed to be able to execute the following query?

Answer:

1. UPDATE (graduated) ON student

2. SELECT(id) on student

3. SELECT(student_id, course_id, grade) on record

4. SELECT (id, credit) on course

Exercise 2

Answer:

1. DELETE on project

2. SELECT (project_id) on employee_project

3. SELECT (id) on project

→ 1, 2, 6

Privileges

View Privileges

What if we want to control the access to certain rows? We can use updatable views.

Privileges for views are exactly the same as for tables

Example : Each department manager can see, add, modify and delete only his/her own employees.

For the planning department: Grand SELECT, INSERT, UPDATE and DELETE on employees_planning

Granting privileges in SQL

User who created a table is its owner and can grant privileges to other users:

WITH GRANT OPTION : user can also grant the privileges to other users

💡

다른 유저에게 권한을 주는 것이다.

It is possible to grant all privileges using

Revoking privileges in SQL

1. CASCADE : revoke the previously granted privilege from user and from all users which were granted the privilege by a chain of grants started by the user

   💡

   즉 user가 권한을 준 것 까지 전부 다 뺏어온다는 것이다.

2. RESTICT (default) : do nothing if user granted the privilege to other users

Quiz 1

Answer : 4

Quiz 2

Answer : WITH GRANT OPTION : 해당 priviledge를 받은 것이 다른 사람에게 previledge를 제공할 수 있는 것이다.

→ 컨셉은 previledge를 준 것만 뺏을 수 있다 따라서 bob은 뻇을 수 없다. 따라서 정답은 2번

💡

물론 specification마다 다르다. 여기서는 postgresql

SQL injection

How to prevent SQL injection in your applicaitons?

1. If you use SQL directly : Always use prepared statements

   

2. Consider using ORM (object-relational mapping)

3. Disallow multiple statements in your applications

4. Use LIMIT to prevent larger disclosures

Passwords

When we did the SQL injection to get the users in the MySQL database, one of the rows was

Passwords must never be stored in plain text but as “hashed”

Password hash is calculated using one-way hash function. To check if the password is correct : calculate hash and compare it to the stored hash.

However, it is possible to apply Brute-force attack (i.e. using pre-calculated result of hash function)

💡

즉 다시 말해서 무작위로 hash 함수에 돌리고 비밀번호를 추적하려는 것이다.

To prevent this, we store salted hashes

1. Generate random string salt

2. password_hash = hash(concat(salt, password))

3. Store both salt and password_hash