Authentication
Authentication is the process of validating whether proper user is accesing the database Authentication has been done mostly
Password Authentication
A password profile is a mechanism in the database that forces a user to follow guidelines when creating or changing passwords. The guidelines help provide stronger security in the system by not allowing weak passwords. You can create your own password verify function and attach it to a profile. A password verify function is a program written in PL/SQL that examines passwords when they’re chosen and accepts or rejects them based on criteria. If you have special password requirements, you can write your own password verify function and assign it to your password profile using the PASSWORD_VERIFY_FUNCTION attribute of the profile.
To use Oracle’s provided password verify function, Do the following ->
$ sqlplus as SYSDBA
SQL> @/rdbms/admin/utlpwdmg.sql
This creates the default password verify function and assigns it to the DEFAULT profile. Using PL/SQL, you can even take Oracle’s example file and modify it to fit your needs.
Creating a password profile
$ sqlplus as sysdba
SQL>CREATE PROFILE writer_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/96
PASSWORD_LIFE_TIME 90;
PASSWORD_LOCK_TIME 1/96 means 1 day/96 = 15 min
Now, assign a user in the above profile using following command.
SQL>alter user HR profile writer_profile ;
If you don't assign a user to any profile it will be assigned to default profile. By default in Oracle 11g, the DEFAULT profile limits the following.
✓ FAILED_LOGIN_ATTEMPT = 10
✓ PASSWORD_GRACE_TIME 7 (DAYS)
✓ PASSWORD_LIFE_TIME 180 (DAYS)
✓ PASSWORD_LOCK_TIME 1 (DAY)
✓ PASSWORD_REUSE_MAX UNLIMITED
✓ PASSWORD_REUSE_TIME UNLIMITED
You can edit your profile or the DEFAULT profile. For example, to change the failed login attempts setting to 3 on the DEFAULT profile, type the following.
SQL> alter profile default limit Failed_login_attempts 3;
Operating system authentication
Operating system authentication recognizes a user as logged into the OS and waives the password requirement. Type this code to create an OS-authenticated user in Oracle for someone named REPORTS.
SQL> create user oracle_user$solaris_user identified externally;
oracle_user = user name of oracle
solaris_user = OS user by which oracle_user will be identified
System privileges
System privileges are the first privileges any user needs.
The CREATE SESSION privilege gives users access to the database.
SQL> grant create session to oracle_user$solaris_user;
To restrict the operating system authentication revoke create session privilage from the above user.
SQL> revoke create session from oracle_user$solaris_user;
Object privileges
You can grant only eight object privileges:
✓ SELECT lets the recipient select rows from tables.
✓ INSERT lets the recipient insert rows into tables.
✓ UPDATE lets the recipient change existing rows in tables.
✓ DELETE lets the recipient remove existing rows from tables.
✓ REFERENCES lets a user create a view on, or a foreign key to, another user’s table.
✓ INDEX lets one user create an index on another user’s table.
✓ ALTER lets one user change or add to the structure of another user’s table.
✓ EXECUTE lets the recipient run procedures owned by another user.
Role
The following commands set create the role.
SQL> create role SALES_ROLE;
Grant system and object privileges to the role:
SQL> grant INSERT on SALES to SALES_ROLE;
SQL> grant UPDATE on INVENTORY to SALES_ROLE;
SQL> grant DELETE on ORDERS to SALES_ROLE;
grant the above roles to some users
SQL> grant SALES_ROLE to user1, user2;
Oracle supply the following roles during installation.
✓ CONNECT = includes the privileges needed to connect to the database.
✓ RESOURCE = includes many of the roles a developer might use to create and manage an application, such as creating and altering many types of objects including tables, view, and sequences.
✓ EXP_FULL_DATABASE/IMP_FULL_DATABASE = allows the grantee to do logical backups of the database.
✓ RECOVERY_CATALOG_OWNER = allows grantee to administer Oracle Recovery Manager catalog.
✓ SCHEDULER_ADMIN = allows the grantee to manage the Oracle job scheduler.
✓ DBA = gives a user most of the major privileges required to administer a database. These privileges can manage users, security, space, system parameters, and backups.
Auditing Oracle Database
All the following database actions are automatically audited by default in Oracle 11g.
- CREATE EXTERNAL JOB
- CREATE ANY JOB
- GRANT ANY OBJECT
- PRIVILEGE
- EXEMPT ACCESS POLICY
- CREATE ANY LIBRARY
- GRANT ANY PRIVILEGE
- DROP PROFILE
- ALTER PROFILE
- DROP ANY
- PROCEDURE
- ALTER ANY
- PROCEDURE
- CREATE ANY
- PROCEDURE
- ALTER DATABASE
- GRANT ANY ROLE
- CREATE PUBLIC
- DATABASE LINK
- DROP ANY TABLE
- ALTER ANY TABLE
- CREATE ANY TABLE
- DROP USER
- ALTER USER
- CREATE USER
- CREATE SESSION
- AUDIT SYSTEM
- ALTER SYSTEM
- SYSTEM AUDIT
- ROLE
the database parameter AUDIT_TRAIL is also set to DB by default. Alternatively it can be changed to OS for file based auditing. The output file location can be tracked as.
SQL> show parameter audit_file_dest;
Turn auditing on and off with the AUDIT or NOAUDIT command.
Example is you can turn on auditing for any CREATE TABLE statement. You might want to track how often and who is creating tables in the database. Auditing CREATE TABLE this way means an audit entry is generated every time someone creates a table.
SQL> audit create table; or SQL> audit create table by user1;
Turn off auditing for CREATE TABLE statements:
SQL> noaudit create table;
other examples.
SQL> audit drop any table by user1 whenever not successful;
SQL> audit select on hr.employees by access;
SQL> audit select on hr.employees by session
View Audit Information
To verify the audits you have already implemented use the following command.
SQL> select * from DBA_PRIV_AUDIT_OPTS;
View the audits turned on for objects owned by HR for the SELECT, INSERT, UPDATE, and DELETE privileges.
SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE, SEL, INS, UPD, DEL
from DBA_OBJ_AUDIT_OPTS
where owner = ‘HR’;
DBA_AUDIT_TRAIL = shows all audit entries in the system.
DBA_AUDIT_OBJECT = shows all audit entries in the system for objects.
DBA_AUDIT_STATEMENT = shows audit entries for the statements GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM.
DBA_AUDIT_SESSION = shows audit entries for the CONNECT and DISCONNECT actions.
Oracle Database Encryption
Just because someone can’t get into your database doesn’t mean she doesn’t have access to your data. A clever hacker who gains access to your Oracle database’s raw files can extract plain text data from these files.
Raw files include the following.
✓ Data files
✓ Recovery Manager backup files
✓ Data Pump export files
Encrypt these file by using Oracle Wallet and an Encryption Key. Depending on the strength of an encryption key, you can make these files virtually indecipherable to anyone.
Data encryption can enhance security both inside and outside the database. A user may have a legitimate need for access to most columns of a table, but if one of the columns is encrypted and the user does not know the encryption key, the information is not usable. The same concern is true for information that needs to be sent securely over a network. The techniques I presented so far in this chapter, including authentication, authorization, and auditing, ensure legitimate access to data from a database user but do not prevent access to an operating system user that may have access to the operating system files that compose the database itself. Users can leverage one of two methods for data encryption: using the package DBMS_CRYPTO, an Oracle Database 10g replacement for the package DBMS_OBFUSCATION_TOOLKIT found in Oracle9i, and transparent data encryption, which stores encryption keys globally and includes methods for encrypting entire tablespaces.
DBMS_CRYPTO Package
New to Oracle 10g, the package DBMS_CRYPTO replaces the DBMS_OBFUSCATION_TOOLKIT and includes the Advanced Encryption Standard (AES) encryption algorithm, which replaces the Data Encryption Standard (DES). Procedures within DBMS_CRYPTO can generate private keys for you, or you can specify and store the key yourself. In contrast to DBMS_OBFUSCATION_TOOLKIT, which could only encrypt RAW or VARCHAR2 datatypes, DBMS_CRYPTO can encrypt BLOB and CLOB types.
Transparent Data Encryption
Transparent data encryption is a key-based access control system that relies on an external module for enforcing authorization. Each table with encrypted columns has its own encryption key, which in turn is encrypted by a master key created for the database and stored encrypted within the database; the master key is not stored in the database itself. The emphasis is on the word transparent—authorized users do not have to specify passwords or keys when accessing encrypted columns in a table or in an encrypted tablespace. Although transparent data encryption has been significantly enhanced in Oracle Database 11g, there are still a few restrictions on its use; for example, you cannot encrypt columns using foreign key constraints, since every table has a unique column encryption key. Thisshould typically not be an issue, since keys used in foreign key constraints should be system-generated, unique, and unintelligent. Business keys and other business attributes of a table are more likely candidates for encryption and usually do not participate in foreign key relationships with other tables. Other database features and types are also not eligiblefor transparent data encryption
- Index types other than B-tree
- Range-scan searching of indexes
- BFILEs (external objects)
- Materialized view logs
- Synchronous Change Data Capture
Creating an Oracle Wallet
You can create a wallet for Transparent Data Encryption using Oracle Enterprise Manager. Select the Server tab, and then click the Transparent Data Encryption link under the Security Heading. You will see the page in Figure 9-10. In this example, there is no wallet created yet. The file sqlnet.ora stores the location of the wallet using the ENCRYPTION_WALLET_LOCATION variable. If this variable does not exist in sqlnet.ora, the wallet is created in $ORACLE_HOME/admin/database_name/wallet, which in this example is /u01/app/oracle/admin/dw/wallet. To create the encryption key and place it in the wallet, create a wallet password that is at least ten characters long, a mix of upper- and lowercase letters, numbers, and punctuation.
.
The equivalent SQL commands to create, open, and close a wallet are very straight forward and probably take less time to type than using Oracle Enterprise Manager! To create a new key, and create the wallet if it does not already exist, use the alter system command as follows.
SQL> alter system set encryption key identified by "Uni123#Lng";
System altered.
Note the importance of putting the wallet key within double quotes; otherwise, the password will map all lowercase characters and the wallet will not open. After the database instance is shut down and restarted, you need to open the wallet with the alter system command if this task is not automated otherwise.
SQL> alter system set encryption wallet open identified by "Uni123#Lng";
System altered.
Finally, you can easily disable access to all encrypted columns in the database at any time by closing the wallet.
SQL> alter system set encryption wallet close;
System altered.
Make frequent backups of your wallet and don’t forget the wallet key (or the security administrator—which can be a separate role from the DBA’s role—should not forget the wallet key), because losing the wallet or the password to the wallet will prevent decryption of any encrypted columns or tablespaces.
Encrypting a Table
You can encrypt a column or columns in one or more tables simply by adding the encrypt keyword after the column’s datatype in a create table command or after the column name in an existing column. For example, to encrypt the SALARY column of the EMPLOYEES table, use this command.
SQL> alter table employees modify (salary encrypt);
Table altered.
Any users who had the privileges to access this column in the past still have the same access to the SALARY column—it’s completely transparent to the users. The only difference is that the SALARY column is indecipherable to anyone accessing the operating system file containing the EMPLOYEES table.
Encrypting a Tablespace
To encrypt an entire database, the COMPATIBLE initialization parameter must be set to 11.1.0.0.0—the default for Oracle Database 11g. If the database has been upgraded from an earlier release, and you change the COMPATIBLE parameter to 11.1.0.0.0, the change is irreversible. An existing tablespace cannot be encrypted; to encrypt the contents of an existing tablespace, you must create a new tablespace with the ENCRYPTION option and copy or move existing objects to the new tablespace.
CREATE SMALLFILE TABLESPACE "USERS_CRYPT"
DATAFILE'+DATA' SIZE 500M LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO NOCOMPRESS ENCRYPTION
USING 'AES256' DEFAULT STORAGE(ENCRYPT)
How to create and manage a Oracle User?
The following command create a new user.
SQL> create user user1 identified by user1 account unlock
default tablespace users
temporary tablespace temp;
To restrict the user with a space quota in a tablespace, use the following command.
SQL> alter user user1 quota 250M on users;
Give basic privilages to a user for work.
SQL> grant create session, create table to sking;
Drop a user.
SQL> drop user queenb cascade;
Alter a user properties.
SQL> alter user user1
default tablespace users2
quota 500M on users2;
To debug an application, a DBA sometimes needs to connect as another user to simulate the problem. Without knowing the actual plain-text password of the user, the DBA can retrieve the encrypted password from the database, change the password for the user, connect with the changed password, and then change back the password using an undocumented clause of the alter
user command.
The first step is to retrieve the encrypted password for the user, which is stored in the table DBA_USERS.
SQL> select password from dba_users where username = 'USER1';
PASSWORD
------------------------------
94b7CBD64A941432
Save this password using cut and paste in a GUI environment, or save it in a text file to retrieve later. The next step is to temporarily change the user’s password and then log in using the temporary password:
SQL> alter user user1 identified by temp_pass;
User altered.
SQL> connect user1/temp_pass@db;
Connected.
At this point, you can debug the application from USER1’s point of view. Once you are done debugging, change the password back using the undocumented by values clause of alter user.
SQL> alter user user1 identified by values '94b7CBD64A941432';
Profiles and Resource Control
The list of resource-control profile options that can appear after CREATE PROFILE profilename LIMIT are explained in below. Each of these parameters can either be an integer, UNLIMITED or DEFAULT. As with the password-related parameters, UNLIMITED means that there is no bound on how much of the given resource can be used. DEFAULT means that this parameter takes its values from the DEFAULT profile. The COMPOSITE_LIMIT parameter allows you to control a group of resource limits when the types of resources typically used varies widely by type; it allows a user to use a lot of CPU time but not much disk I/O during one session, and vice versa during another session, without being disconnected by the policy.
Resource Parameter Description
SESSIONS_PER_USER = The maximum number of sessions a user can simultaneously have
CPU_PER_SESSION = The maximum CPU time allowed per session, in hundredths of a second
CPU_PER_CALL = Maximum CPU time for a statement parse, execute, or fetch operation, in hundredths of a second
CONNECT_TIME = Maximum total elapsed time, in minutes
IDLE_TIME = Maximum continuous inactive time in a session, in minutes, while a query or other operation is not inprogress
LOGICAL_READS_PER_SESSION = Total number of data blocks read per session, either from memory or disk
LOGICAL_READS_PER_CALL = Maximum number of data blocks read for a statement parse, execute, or fetch operation
COMPOSITE_LIMIT = Total resource cost, in service units, as a composite
weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA
PRIVATE_SGA = Maximum amount of memory a session can allocate in the shared pool, in bytes, kilobytes, or megabytes
By default, all resource costs are zero:
SQL> select * from resource_cost;
RESOURCE_NAME UNIT_COST
-------------------------------- ----------
CPU_PER_SESSION 0
LOGICAL_READS_PER_SESSION 0
CONNECT_TIME 0
PRIVATE_SGA 0
4 rows selected.
To adjust the resource cost weights, use the ALTER RESOURCE COST command. In this example, we change the weightings so that CPU_PER_SESSION favors CPU usage over connect time by a factor of 25 to 1; in other words, a user is more likely to be disconnected because of CPU usage than connect time.
SQL> alter resource cost cpu_per_session 50 connect_time 2;
SQL> select * from resource_cost;
RESOURCE_NAME UNIT_COST
-------------------------------- ----------
CPU_PER_SESSION 50
LOGICAL_READS_PER_SESSION 0
CONNECT_TIME 2
PRIVATE_SGA 0
4 rows selected.
The next step is to create a new profile or modify an existing profile to use a composite limit.
SQL> create profile lim_comp_cpu_conn limit composite_limit 250;
Profile created.
As a result, users assigned to the profile LIM_COMP_CPU_CONN will have their session resources limited using the following formula to calculate cost.
composite_cost = (50 * CPU_PER_SESSION) + (2 * CONNECT_TIME);
The parameters PRIVATE_SGA and LOGICAL_READS_PER_SESSION are not used in this particular example, so unless they are specified otherwise in the profile definition, they default to whatever their value is in the DEFAULT profile. The goal of using composite limits is to giveusers some leeway in the types of queries or DML they run. On some days, they may run a lot of queries that perform numerous calculations but don’t access a lot of table rows; on other days, they may do a lot of full table scans but don’t stay connected very long. In these situations, we don’t want to limit a user by a single parameter, but instead by total resource usage weighted by the availability of each resource on the server.
Virtual Private Database [VPD]
A select statement on a table, view, or synonym controlled by a VPD will return a subset of rows based on a where clause generated automatically by the security policy function in effect by the application context. The major component of a VPD is row-level security (RLS), also known as fine-grained access control (FGAC). Because a VPD generates the predicates transparently during statement parse, the security policy is enforced consistently regardless of whether the user is running ad hoc queries, retrieving the data from an application, or viewing the data from Oracle Forms. Because the Oracle Server applies the predicate to the statement at parse time, the application need not use special tables, views, and so forth to implement the policy. As a result, Oracle can optimize the query using indexes, materialized views, and parallel operations where it otherwise might not be able. Therefore, using a VPD may incur less overhead than a query whose results are filtered using applications or other means.
Using the create context command, you can create the name of application-defined attributes that will be used to enforce your security policy, along with the package name for the functions and procedures used to set the security context for the user session.
create context hr_security using vpd.emp_access;
create or replace package emp_access as
procedure set_security_parameters;
end;
In this example, the context name is HR_SECURITY, and the package used to set up the characteristics or attributes for the user during the session is called EMP_ACCESS. The procedure SET_SECURITY_PARAMETERS will be called in the logon trigger. Because the context HR_SECURITY is bound only to EMP_ACCESS, no other procedures can change the session attributes.
This ensures a secure application context that can’t be changed by the user or any other process after connecting to the database. In a typical package used to implement application context, you use the built-in context USERENV to retrieve information about the user session itself.
Parameter Return Value
CURRENT_SCHEMA The default schema for the session
DB_NAME The name of the database as specified in the initialization parameter DB_NAME
HOST The name of the host machine from which the user connected
IP_ADDRESS The IP address from which the user connected
OS_USER The operating system account that initiated the database session
SESSION_USER The authenticated database user’s name
For example, the following calls to SYS_CONTEXT will retrieve the username and IP_ADDRESS of the database session.
declare
username varchar2(30);
ip_addr varchar2(30);
begin
username := SYS_CONTEXT('USERENV','SESSION_USER');
ip_addr := SYS_CONTEXT('USERENV','IP_ADDRESS');
-- other processing here
end;
Similarly, the SYS_CONTEXT function can be used within a SQL select statement.
SQL> select SYS_CONTEXT('USERENV','SESSION_USER') username from dual;
USERNAME
-------------------------
USER1
Using some combination of the USERENV context and authorization information in the database, we use DBMS_SESSION.SET_CONTEXT to assign values to parameters in the application context that we create.
dbms_session.set_context('HR_SECURITY','SEC_LEVEL','HIGH');
In this example, the application context variable SEC_LEVEL is set to HIGH in the HR_SECURITY context. The value can be assigned based on a number of conditions, including a mapping table that assigns security levels based on user ID.
To ensure that the context variables are set for each session, we can use a logon trigger to call the procedure associated with the context. As mentioned earlier, the variables in the context can only be set or changed within the assigned package. Here is a sample logon trigger that calls the procedure to set up the context.
create or replace trigger vpd.set_security_parameters
after logon on database
begin
vpd.emp_access.set_security_parameters;
end;
In this example, the procedure SET_SECURITY_PARAMETERS would make the necessary calls to DBMS_SESSION.SET_CONTEXT.
Security Policy Implementation
create or replace package body get_predicates is
function emp_select_restrict(owner varchar2, object_name varchar2)
return varchar2 is
ret_predicate varchar2(1000); -- part of WHERE clause
begin
-- only allow certain employees to see rows in the table
-- . . . check context variables and build predicate
return ret_predicate;
end emp_select_restrict;
function emp_dml_restrict(owner varchar2, object_name varchar2)
return varchar2 is
ret_predicate varchar2(1000); -- part of WHERE clause
begin
-- only allow certain employees to make changes to the table
-- . . . check context variables and build predicate
return ret_predicate;
end emp_dml_restrict;
end; -- package body
Each function returns a string containing an expression that is added to a where clause for a select statement or a DML command. The user or application never sees the value of this WHERE clause; it is automatically added to the command at parse time. The developer must ensure that the functions always return a valid expression. Otherwise,any access to a protected table will always fail, as in the following example.
SQL> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-28113: policy predicate has error
The error message does not say what the predicate is, and all users are locked out of the table until the predicate function is fixed.
The built-in package DBMS_RLS contains a number of subprograms that a DBA uses to maintain the security policies associated with tables, views, and synonyms.
ADD_POLICY Adds a fine-grained access control policy to an object
DROP_POLICY Drops an FGAC policy from an object
REFRESH_POLICY Reparses all cached statements associated with the policy
ENABLE_POLICY Enables or disables an FGAC policy
CREATE_POLICY_GROUP Creates a policy group
ADD_GROUPED_POLICY Adds a policy to a policy group
ADD_POLICY_CONTEXT Adds the context for the current application
DELETE_POLICY_GROUP Deletes a policy group
DROP_GROUPED_POLICY Drops a policy from a policy group
DROP_POLICY_CONTEXT Drops a context for the active application
ENABLE_GROUPED_POLICY Enables or disables a group policy
DISABLE_GROUPED_POLICY Disables a group policy
REFRESH_GROUPED_POLICY Reparses all cached statements associated with the policy group
The syntax of ADD_POLICY follows.
DBMS_RLS.ADD_POLICY
(
object_schema IN varchar2 null,
object_name IN varchar2,
policy_name IN varchar2,
function_schema IN varchar2 null,
policy_function IN varchar2,
statement_types IN varchar2 null,
update_check IN boolean false,
enable IN boolean true,
static_policy IN boolean false,
policy_type IN binary_integer null,
long_predicate IN in Boolean false,
sec_relevant_cols IN varchar2,
sec_relevant_cols_opt IN binary_integer null
);
Note that some of the parameters have BOOLEAN default values and that the less commonly used parameters are near the end of the argument list. This makes the syntax for any particular call to DBMS_RLS.ADD_POLICY easier to write and understand for the vast majority of cases.
In the following example, we’re applying a policy named EMP_SELECT_RESTRICT to the table HR.EMPLOYEES. The schema VPD owns the policy function get_predicates.emp_select_restrict. The policy explicitly applies to SELECT statements on the table; however, with UPDATE_CHECK set to TRUE, update or delete commands will also be checked when rows are updated or inserted into the table.
dbms_rls.add_policy (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_SELECT_RESTRICT',
function_schema => 'VPD',
policy_function => 'get_predicates.emp_select_restrict',
statement_types => 'SELECT',
update_check => TRUE,
enable => TRUE
);
Because we did not set static_policy, it defaults to FALSE, meaning that the policy is dynamic and is checked every time a select statement is parsed. This is the only behavior available before Oracle Database 10g. Using the subprogram ENABLE_POLICY is an easy way to disable the policy temporarily without having to rebind the policy to the table later.
dbms_rls.enable_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_SELECT_RESTRICT',
enable => FALSE
);
If multiple policies are specified for the same object, an AND condition is added between each predicate. If you need to have an OR condition between predicates for multiple policies instead, the policy most likely needs to be revised. The logic for each policy needs to be combined within a single policy with an OR condition between each part of the predicate.
Creating a VPD
This example relies on the sample schemas installed with Oracle Database 10g and 11g. To be specific, we are going to implement an FGAC policy on the HR.EMPLOYEES table to restrict access based on manager status and the employee’s department number. If you are an employee, you can see your own row in HR.EMPLOYEES; if you are a manager, you can see the rows for all the employees who report directly to you.
Once the sample schemas are in place, we need to create some users in the database who want to see rows from the table HR.EMPLOYEES.
create user smavris identified by smavris702;
grant connect, resource to smavris;
create user dgrant identified by dgrant507;
grant connect, resource to dgrant;
create user kmourgos identified by kmourgos622;
grant connect, resource to kmourgos;
The user KMOURGOS is the manager for all the stocking clerks, and DGRANT is one of KMOURGOS’s employees. The user SMAVRIS is the HR_REP for the company. In the following three steps, we will grant SELECT privileges on the HR.EMPLOYEES table to everyone in the database, and we will create a lookup table that maps employee ID numbers to their database account. The procedure that sets the context variables for the user session will use this table to assign the employee ID number to the context variable that will be used in the policy function to generate the predicate.
grant select on hr.employees to public;
create table hr.emp_login_map (employee_id, login_acct)
as select employee_id, email from hr.employees;
grant select on hr.emp_login_map to public;
Next, we will create a user account called VPD that has the privileges to create contexts and maintains the policy functions.
create user vpd identified by vpd439;
grant connect, resource, create any context, create public synonym to vpd;
Connecting to the VPD schema, we will create a context called HR_SECURITY and define the package and procedure used to set the context for the application.
connect vpd/vpd439@dw;
create context hr_security using vpd.emp_access;
create or replace package vpd.emp_access as
procedure set_security_parameters;
end;
Remember that the procedures in the package VPD.EMP_ACCESS are the only procedures that can set the context variables. The package body for VPD.EMP_ACCESS follows.
create or replace package body vpd.emp_access is
--
-- At user login, run set_security_parameters to
-- retrieve the user login name, which corresponds to the EMAIL
-- column in the table HR.EMPLOYEES.
--
-- context USERENV is pre-defined for user characteristics such
-- as username, IP address from which the connection is made,
-- and so forth.
--
-- for this procedure, we are only using SESSION_USER
-- from the USERENV context.
--
procedure set_security_parameters is
emp_id_num number;
emp_login varchar2(50);
begin
-- database username corresponds to email address in HR.EMPLOYEES
emp_login := sys_context('USERENV','SESSION_USER');
dbms_session.set_context('HR_SECURITY','USERNAME',emp_login);
-- get employee id number, so manager rights can be established
-- but don't bomb out other DB users who are not in the
-- EMPLOYEES table
begin
select employee_id into emp_id_num
from hr.emp_login_map where login_acct = emp_login;
dbms_session.set_context('HR_SECURITY','EMP_ID',emp_id_num);
exception
when no_data_found then
dbms_session.set_context('HR_SECURITY','EMP_ID',0);
end;
-- Future queries will restrict rows based on emp_id
end; -- procedure
end; -- package body
A few things are worth noting about this procedure. We retrieve the user’s schema by looking in the USERENV context, which is enabled by default for all users, and assigning it to the variable USERNAME in the newly created context HR_SECURITY. The other HR_SECURITY context variable EMP_ID is determined by doing a lookup in the mapping table HR.EMP_LOGIN_MAP. We don’t want the procedure to terminate with an error if the logged-in user is not in the mapping table; instead, we assign an EMP_ID of 0, which will result in no access to the table HR.EMPLOYEES when the predicate is generated in the policy function. In the next steps, we grant everyone in the database EXECUTE privileges on the package, and we create a synonym for it to save a few keystrokes any time we need to call it.
grant execute on vpd.emp_access to PUBLIC;
create public synonym emp_access for vpd.emp_access;
To ensure that the context is defined for each user when they log on, we will connect as SYSTEM and create a logon trigger to set up the variables in the context.
connect system/nolongermanager@dw as sysdba;
create or replace trigger vpd.set_security_parameters
after logon on database
begin
vpd.emp_access.set_security_parameters;
end;
Because this trigger is fired for every user who connects to the database, it is vitally important that the code be tested for every class of user, if not every user in the database! If the trigger fails with an error, regular users cannot log in. So far, we have our context defined, the procedure used to set up the context variables, and a trigger that automatically calls the procedure. Logging in as one of our three users defined previously, we can query the contents of the context.
SQL> connect smavris/smavris702@dw
Connected.
SQL> select * from session_context;
NAMESPACE ATTRIBUTE VALUE
------------------------ ------------------------- ---------------------
HR_SECURITY USERNAME SMAVRIS
HR_SECURITY EMP_ID 203
2 rows selected.
Notice what happens when SMAVRIS tries to impersonate another employee:
SQL> begin
2 dbms_session.set_context('HR_SECURITY','EMP_ID',100);
3 end;
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 94
ORA-06512: at line 2
Only the package VPD.EMP_ACCESS is allowed to set or change variables in the context. The final steps include defining the procedures that will generate the predicate and assigning one or more of these procedures to the HR.EMPLOYEES table. As the user VPD, which already owns the context procedures, we’ll set up the package that determines the predicates.
connect vpd/vpd439@dw;
create or replace package vpd.get_predicates as
-- note -- security function ALWAYS has two parameters,
-- table owner name and table name
function emp_select_restrict
(owner varchar2, object_name varchar2) return varchar2;
-- other functions can be written here for INSERT, DELETE, and so forth.
end get_predicates;
create or replace package body vpd.get_predicates is
function emp_select_restrict
(owner varchar2, object_name varchar2) return varchar2 is
ret_predicate varchar2(1000); -- part of WHERE clause
begin
-- only allow employee to see their row or immediate subordinates
ret_predicate := 'EMPLOYEE_ID = ' ||
sys_context('HR_SECURITY','EMP_ID') ||
' OR MANAGER_ID = ' ||
sys_context('HR_SECURITY','EMP_ID');
return ret_predicate;
end emp_select_restrict;
end; -- package body
Once we attach the function to a table with DBMS_RLS, it will generate a text string that can be used in a WHERE clause every time the table is accessed. The string will always look something like this:
EMPLOYEE_ID = 124 OR MANAGER_ID = 124
As with the packages that set up the context environment, we need to allow users access to
this package.
grant execute on vpd.get_predicates to PUBLIC;
create public synonym get_predicates for vpd.get_predicates;
Last, but certainly not least, we will attach the policy function to the table using the DBMS_RLS.ADD_POLICY procedure.
dbms_rls.add_policy (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_SELECT_RESTRICT',
function_schema => 'VPD',
policy_function => 'get_predicates.emp_select_restrict',
statement_types => 'SELECT',
update_check => TRUE,
enable => TRUE
);
An employee can access the HR.EMPLOYEES table as before, but they will only see their row and the rows of the employees who work for them, if any. Logging in as KMOURGOS, we try to retrieve all the rows of the HR.EMPLOYEES table, but we only see KMOURGOS and the employees who report directly to him.
SQL> connect kmourgos/kmourgos622@dw;
Connected.
SQL> select employee_id, first_name, last_name, email, job_id, salary, manager_id from hr.employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL JOB_ID SALARY MANAGER_ID
----------- ---------- ----------- ---------- ---------- ------- ----------
124 Kevin Mourgos KMOURGOS ST_MAN 5800 100
141 Trenna Rajs TRAJS ST_CLERK 3500 124
142 Curtis Davies CDAVIES ST_CLERK 3100 124
143 Randall Matos RMATOS ST_CLERK 2600 124
144 Peter Vargas PVARGAS ST_CLERK 2500 124
196 Alana Walsh AWALSH SH_CLERK 3100 124
197 Kevin Feeney KFEENEY SH_CLERK 3000 124
198 Donald OConnell DOCONNEL SH_CLERK 2600 124
199 Douglas Grant DGRANT SH_CLERK 2600 124
9 rows selected.
For the user DGRANT, it’s a different story:
SQL> connect dgrant/dgrant507@dw;
Connected.
SQL> select employee_id, first_name, last_name,
2 email, job_id, salary, manager_id from hr.employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL JOB_ID SALARY MANAGER_ID
----------- ---------- ----------- ---------- ---------- ------- ----------
199 Douglas Grant DGRANT SH_CLERK 2600 124
1 row selected.
DGRANT gets to see only his own row, because he does not manage anyone else in the company. In the case of SMAVRIS, we see similar results from the query.
SQL> connect smavris/smavris702@dw;
Connected.
SQL> select employee_id, first_name, last_name,
2 email, job_id, salary, manager_id from hr.employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL JOB_ID SALARY MANAGER_ID
----------- ---------- ------------ ---------- ---------- ------- ----------
203 Susan Mavris SMAVRIS HR_REP 6500 101
1 row selected.
But wait, SMAVRIS is in the HR department and should be able to see all rows from the table. In addition, SMAVRIS should be the only person to see the salary information for all employees.
As a result, we need to change our policy function to give SMAVRIS and other employees in the HR department full access to the HR.EMPLOYEES table; in addition, we can use column-level restrictions in the policy assignment to return the same number of rows, but with the sensitive data returned as NULL values. To facilitate access to the HR.EMPLOYEES table by HR department employees, we first need to change our mapping table to include the JOB_ID column. If the JOB_ID column has a value
of HR_REP, the employee is in the HR department. We will first disable the policy in effect and create the new mapping table.
SQL> begin
2 dbms_rls.enable_policy(
3 object_schema => 'HR',
4 object_name => 'EMPLOYEES',
5 policy_name => 'EMP_SELECT_RESTRICT',
6 enable => FALSE
7 );
8 end;
PL/SQL procedure successfully completed.
SQL> drop table hr.emp_login_map;
Table dropped.
SQL> create table hr.emp_login_map (employee_id, login_acct, job_id)
2 as select employee_id, email, job_id from hr.employees;
Table created.
SQL> grant select on hr.emp_login_map to public;
Grant succeeded.
The procedure we’re using to set up the context variables, VPD.EMP_ACCESS, needs another context variable added that indicates the security level of the user accessing the table. We will change the SELECT statement and make another call to DBMS_SESSION.SET_CONTEXT, as follows.
. . .
emp_job_id varchar2(50);
. . .
select employee_id, job_id into emp_id_num, emp_job_id
from hr.emp_login_map where login_acct = emp_login;
dbms_session.set_context('HR_SECURITY','SEC_LEVEL',
case emp_job_id when 'HR_REP' then 'HIGH' else 'NORMAL' end );
. . .
Whenever the employee has a job title of HR_REP, the context variable SEC_LEVEL is set to HIGH instead of NORMAL. In our policy function, we need to check for this new condition as follows.
create or replace package body vpd.get_predicates is
function emp_select_restrict
(owner varchar2, object_name varchar2) return varchar2 is
ret_predicate varchar2(1000); -- part of WHERE clause
begin
-- only allow employee to see their row or immediate subordinates,
-- unless they have high security clearance
if sys_context('HR_SECURITY','SEC_LEVEL') = 'HIGH' then
ret_predicate := ''; -- no restrictions in WHERE clause
else
ret_predicate := 'EMPLOYEE_ID = ' ||
sys_context('HR_SECURITY','EMP_ID') ||
' OR MANAGER_ID = ' ||
sys_context('HR_SECURITY','EMP_ID');
end if;
return ret_predicate;
end emp_select_restrict;
end; -- package body
Because the policy is dynamic, the predicate is generated each time a SELECT statement is executed, so we don’t have to do a policy refresh. When the user SMAVRIS, the HR representative,
runs the query now, she sees all rows in the HR.EMPLOYEES table.
SQL> connect smavris/smavris702@dw;
Connected.
SQL> select employee_id, first_name, last_name, email, job_id, salary, manager_id from hr.employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL JOB_ID SALARY MANAGER_ID
----------- ----------- ----------- ---------- ---------- ------- ----------
100 Steven King SKING AD_PRES 24000
101 Neena Kochhar NKOCHHAR AD_VP 17000 100
. . .
204 Hermann Baer HBAER PR_REP 10000 101
205 Shelley Higgins SHIGGINS AC_MGR 12000 101
206 William Gietz WGIETZ AC_ACCOUNT 8300 205
107 rows selected.
As you might expect, SMAVRIS’s security level within the HR_SECURITY context is HIGH.
SQL> connect smavris/smavris702
Connected.
SQL> select sys_context('HR_SECURITY','SEC_LEVEL') from dual;
SYS_CONTEXT('HR_SECURITY','SEC_LEVEL')
--------------------------------------------------------------
HIGH
SQL>
However, DGRANT can still only see his row in the table because his security level within the HR_SECURITY context is NORMAL.
SQL> connect dgrant/dgrant507@dw;
Connected.
SQL> select employee_id, first_name, last_name, email, job_id, salary, manager_id from hr.employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL JOB_ID SALARY MANAGER_ID
----------- ---------- ----------- ---------- ---------- ------- ----------
199 Douglas Grant DGRANT SH_CLERK 2600 124
1 row selected.
SQL> select sys_context('HR_SECURITY','SEC_LEVEL') from dual;
SYS_CONTEXT('HR_SECURITY','SEC_LEVEL')
--------------------------------------------------------------
NORMAL
To enforce the requirement that only HR employees can see salary information, we would need to make a slight change to the policy function and enable the policy with column-level restrictions.
dbms_rls.add_policy (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_SELECT_RESTRICT',
function_schema => 'VPD',
policy_function => 'get_predicates.emp_select_restrict',
statement_types => 'SELECT',
update_check => TRUE,
enable => TRUE,
sec_relevant_cols => 'SALARY',
sec_relevant_cols_opt => dbms_rls.all_rows
);
The last parameter, SEC_RELEVANT_COLS_OPT, specifies the package constant DBMS_RLS. ALL_ROWS to indicate that we still want to see all rows in our query results, but with the relevant columns (in this case SALARY) returning NULL values.
No comments :
Post a Comment