Oracle SQL in 10 Minutes - Asim Abbasi
CH1: SQL Basics | CH2: SQL Operators | CH3: SQL Built-in Functions | CH4: SQL Joins | CH5: UPDATE, INSERT & DELETE Statements | CH6: CREATE, ALTER & DROP Statements | CH7: Constraints | CH8: Linking Tables vs Joining Tables | CH9: SQL Statements for Other Database Objects | CH10: SQL Statements for Database Security
Chapter 10: SQL Statements for Database Security
Next very important concept I would like to give you is the “role”. Role
in database jargon means the group of privileges bundled together and
given a name or alias. So what is the benefit of creating roles? For
example, let say Human Resources (HR) department of a company has 65
people and out of those 65 people, 35 were having SELECT, INSERT,
UPDATE, DELETE privileges on Payroll department tables and we are
assuming a scenario where you have assigned each of these 35 people
privileges not role. After a month change of business rule comes up and
its described that those 35 HR would only be having SELECT privilege on
Payroll tables. Without using role you have to run REVOKE statement 35
times and if you would go by “role” technique one statement can
implement that change no matter whether there are 35 people or 3500
people.
Privileges can be granted to users as well as roles.
Example:
CREATE ROLE hr_emps
IDENTIFIED BY evergreen;
By default a role is created with the password and is enabled. One that
is created with the password is disabled by default and can be enabled
using SET ROLE statement.
SET ROLE hr_emps
IDENTIFIED BY evergreen;
SET ROLE statement can be used to enable or disable a gole. Once we have
created the role, next thing is to assign this role, privileges.
GRANT SELECT, UPDATE
ON emp_test
TO hr_emps;
Chapter 10: SQL Statements for Database Security
CH1: SQL Basics | CH2:
SQL Operators |
CH3: SQL Built-in Functions | CH4:
SQL Joins | CH5:
UPDATE, INSERT & DELETE Statements |
CH6: CREATE, ALTER & DROP Statements | CH7:
Constraints | CH8:
Linking Tables vs Joining Tables | CH9:
SQL
Statements for Other Database Objects | CH10:
SQL Statements for
Database Security Share with others: