Access Control Discretionary Access Control The primary form

Access Control

Discretionary Access Control The primary form of access control used in DBMSs is discretionary and based on the granting and revocation of privileges. Unfortunately, every DBMS does this in its own special way, so I'll be talking generalizations for the most part. Each client to a database practicing access control must authenticate themselves (usually with a password) to be able to use the privileges associated with an account. Informally there are two non-mutually-exclusive levels for assigning privileges: ◦ The account level: Each account has specific privileges that apply to the entire database. ◦ The relation level: Each account has specific privileges that apply to particular relations/tables.

Account Level Privileges These privileges can be granted to particular accounts: ◦ ◦ ◦ CREATE SCHEMA CREATE TABLE / VIEW / TRIGGER /. . . ALTER / DROP SELECT UPDATE / DELETE / INSERT Ability to grant such privileges on other accounts http: //www. ingeniouspress. com/2014/ 05/20/check-privilege-conversationkaren-straughan/

Relation Level Privileges These are privileges that apply to a specific relation in the database. ◦ SELECT: a. k. a. retrieval or read privilege ◦ Modification: (INSERT, UPDATE, DELETE) Additionally it is often possible to specify which attributes are allowed to be modified ◦ Reference: This gives the account the capability to reference a relation when specifying integrity constraints (foreign key constraints) http: //composing. org/blog/check-your-privilege/

Why would you not give all accounts reference privileges? 1. References can be used to gain read privileges on a table. 2. References can be used to access all the attributes of a table. 3. References can reveal the existence of primary keys of a table. 4. Reference (especially letters of) are a pain because you don't have any job experience yet.

How do you provide access to a subset of attributes of a table? 1. Create a new table with only the allowed attributes and give access to that. 2. Create a view with only the allowed attributes and give access to that. 3. Create triggers that will only allow rows to be selected that the account is authorized to see. 4. Blind folds and pinky promises

Propagation of Privileges The root/superuser/admin account of a database normally has the full set of privileges. They can grant subsets of these privileges on other accounts. However, requiring the admin account to be the only account able to grant privileges is inefficient and a minor security flaw. It is often more efficient to allow other accounts to have the ability to propagate (grant) some of their privileges to other accounts. Privileges can also be revoked (removed) when an account no longer needs them. http: //blog. chilli-willy. com/category/propagationand-germination/

What should happen if the account that gave you privileges has its privileges revoked. 1. You should have those privileges revoked as well. 2. You should have those privileges revoked, unless they were also granted to you by a still valid account. 3. You should still be granted your privileges, as they haven't been revoked directly. 4. You should scorn anyone with less privilege than you.

The admin account creates four accounts: A 1, A 2, A 3, A 4 admin executes: GRANT CREATETAB TO A 1; -- A 1 is allowed to create tables (account level) A 1 executes: CREATE TABLE Employee (Name, SSN, . . . ) -- Because A 1 created the table, it has full privileges on them. CREATE TABLE Department (Name, D_number, . . . ) -- See above GRANT INSERT, DELETE ON Employee, Department To A 2; -- A 2 can insert and delete in the tables GRANT SELECT ON Employee, Department TO A 3 WITH GRANT OPTION; -- A 3 can select and has the ability to propagate this privilege. A 3 executes: GRANT SELECT ON Employee TO A 4; -- A 4 has select privilege (because A 3 is allowed to propagate) A 1 executes: REVOKE SELECT ON Employee FROM A 3; -- A 3 no longer can select on Employee (and neither can A 4).

Role-Based Access Control Often it is simpler to define organizational roles, assign privileges to each role, then assign accounts to a role. Example roles: ◦ ◦ Database Administrator: Full Privileges HR representative: Read and modification privileges on some of the tables Employee: Read privileges on self data but nothing else Sales Rep: Read (but not modify) privileges on product catalogue Accounts can be assigned multiple roles if needed.