SQL offers several commands to implement data security and to restrict data access.
The Oracle DBMS offers two types of privileges: system privileges and object privileges.
The following table 2-2 lists the most important Oracle object privileges.
|Object Privilege||Allowable Action|
|ALTER||Change the table structure with ALTER TABLE|
|EXECUTE||Execute stored functions or procedures|
|FLASHBACK||Go back in time with FLASHBACK TABLE|
|INDEX||Create indexes on the table|
|INSERT||Insert new rows|
|REFERENCES||Create foreign key constraints to the table|
|SELECT||Query the table or view|
|UPDATE||Change column values of existing rows|
The Oracle DBMS can group privileges into roles.
You will need to be given the 'create role' privilege by your DBA.
The following are the SQL commands used to manage these privileges and roles:
A typical scenario is the following:
--creates a new (empty) role. CREATE ROLE <role name> -- populates the role with a mix of object and system privileges. GRANT privileges TO <role name> --grants the role and all its privileges to a user in a single step. GRANT <role name> TO user(s)