A detailed discussion on the setup and administration of database access and security is beyond the scope of this document.
This document examines simplifying the setup and administration of database security in an ever changing / volatile environment.
The majority of tasks an Oracle DBA does in maintaining the Security and Database Access are:
0100 User account management
0101 Creating users, roles, profiles.
Granting table level / system level privileges to users / roles
Drop / Lock users. Password management.
0200 Special requirements
0201 Protecting sensitive / confidential data using column level / row level security
by implementing Virtual Private Database / or custom built views.
0202 Granting Truncate privileges on tables in others schemas.
Creation and maintenance of database access and security is becoming a complex and demanding task day in and day out. A lack of proper planning and approach will put the DBA's life into fire fighting mode.
0100 User account management
The creation and re-creation of tables in Development/ Integration environments is an ongoing process.
The creation of new schemas and tables in Production environment is continuous, with new code / modules migration from Development / Integration environments.
In either case, not all of the users in the database can access the newly created / re-created objects.
Users granted only the DBA role or SELECT_ANY TABLE system level privilege can access the newly created / re-created tables in a database without any re-granting of privileges.
For security reasons, if we cannot grant the SELECT_ANY TABLE system level privilege or the DBA role to the users in the database, imagine the trouble of frequently granting the necessary privileges on the newly created / re-created tables to users /roles.
Tracking all the newly created/ re-created tables and granting permissions on these tables to different users / roles is a full time DBA job. To address this issue and simplify the process of granting privileges, create three roles for each schema, to manage the different table level permissions required by the users. (Currently, the existing schemas in our database are - TOM, DICK and HARRY). Alternatively, we can reduce or increase the number of roles in the database, depending on the operational requirements and ease.
The details of roles and their granted table level privileges are as follows:
|TOM_SELECT||SELECT PERMISSION ON ALL TABLES IN TOM'S SCHEMA|
|TOM_SINSERT||SELECT/INSERT PERMISSIONS ON ALL TABLES IN TOM'S SCHEMA|
|TOM_SIUD||SELECT/INSERT/UPDATE/DELET PERMISSIONS ON ALL TABLES IN TOM'S SCHEMA|
|DICK_SELECT||SELECT PERMISSION ON ALL TABLES IN DICK'S SCHEMA|
|DICK_SINSERT||SELECT/INSERT PERMISSIONS ON ALL TABLES IN DICK'S SCHEMA|
|DICK_SIUD||SELECT/INSERT/UPDATE/DELET PERMISSIONS ON ALL TABLES IN DICK'S SCHEMA|
|HARRY_SELECT||SELECT PERMISSION ON ALL TABLES IN HARRY'S SCHEMA|
|HARRY_SINSERT||SELECT/INSERT PERMISSIONS ON ALL TABLES IN HARRY'S SCHEMA|
|HARRY_SIUD||SELECT/INSERT/UPDATE/DELET PERMISSIONS ON ALL TABLES IN HARRY'S SCHEMA|
Create the roles for each schema. Create role tom_select; Create role tom_sinsert; Create role tom_siud; Create role dick_select; Create role dick_sinsert; Create role dick_siud; Create role harry_select; Create role harry_sinsert; Create role harry_siud; Grant create session to these roles: Grant create session to tom_select; Grant create session to tom_sinsert; Grant create session to tom_siud; Grant create session to dick_select; Grant create session to dick_sinsert; Grant create session to dick_siud; Grant create session to harry_select; Grant create session to harry_sinsert; Grant create session to harry_siud;
This approach is good and maintainable with only few schemas in the database. With more schemas in the database, the number of roles to be created will become a huge list.
To further simplify the approach, you can have three roles for the entire database, like db_select, db_sinsert and db_siud. Grant the necessary permissions on all of the schema tables in the database to these three roles.
Another approach is to have multiple schemas grouped into different groups, depending on their application, sensitivity, criticality. Create group1_select, group1_sinsert and group1_siud roles for each group.
Whenever a new user is created, grant the necessary roles to the user.
Create a procedure to grant different levels of permission to different roles on the newly created / re-created tables. Schedule the procedure to run every day at 18:00 hrs. By scheduling the procedure to run everyday at 18:00 hrs, the users can access all the tables, created / re-created the day before.
declare begin for t1 in (select owner, table_name from all_tables where owner in ('TOM','DICK','HARRY')) loop execute immediate 'grant select on '||t1.owner||'.'||t1.table_name||' to '||t1.owner||'_SELECT'; execute immediate 'grant select, insert on '||t1.owner||'.'||t1.table_name|| ' to '||t1.owner||'_SINSERT'; execute immediate 'grant select, insert, update, delete on '||t1.owner||'.'||t1.table_name|| ' to '||t1.owner||'_SIUD'; end loop; end;
This procedure grants the required permissions on the existing tables in the database to different roles as per the security setup.
This procedure works fine if each schema in the database has three roles, however, the procedure needs to be modified if the roles setup is different, i.e. three roles for the entire database or all of the schemas are put into different groups and three roles per group are created.
At any time, the table level permissions on all the schema objects can be granted/re-granted to different roles by executing the procedure.
Grant the roles to users at the time of the users creation.
Imagine the number of scripts I would have to execute in the database if I didn't have a setup like this and I had to grant various table level permissions on all created/ re-created schema tables to all the users.
If required, create a procedure to grant various roles to users.
declare begin for u1 in (select username from all_users where username in upper('')) loop /* Here supply the username */ for p1 in (select username from all_users where username in ('TOM','DICK','HARRY')) loop /* uncomment necessary grant of roles and comment unnecessary grant of roles in this script */ --execute immediate 'grant '||p1.username||'_select to '||u1.username; --execute immediate 'grant '||p1.username||'_sinsert to '||u1.username; execute immediate 'grant '||p1.username||'_siud to '||u1.username; end loop; end loop; end;
Wherever specific privileges are needed, grant the special privileges to specified users separately.
For password management, create a default profile with required limits on password management and
assign it to the users in the database.
A sample profile:
Create profile apps_developer limit Failed_login_attempts 3 Password_lock_time 3 Password_life_time 30 Password_grace_time 3 Password_reuse_time 150 Password_reuse_max 5 ;
If only one user was created in the database, this single user owns all of the schema objects. Moreover, if all of the users are connecting to the database as a single schema owner, then where is the need for further simplification?
In Part II, I will be dealing with some of the data models for accomplishing column level and row level data security. Please refer to Oracle's Metalink for a detailed discussion on Virtual Private Database setup.
I will be elaborating on how to grant TRUNCATE permissions on third party tables to users--what are the traps a DBA should avoid while granting TRUNCATE permissions on third party tables to users and what are best practices.