Dear Readers,
My name is Franz Devantier, creator of this blog. I am an Oracle Certified
Professional (OCP DBA 11g) Security DBA.
I will be sharing with you the basic duties of an Oracle DBA, and also
some of the undocumented, and not so well known tasks.
I will make a deal with you: If you refer me to a company that needs
database support, from a few hours per week to full time, and I am able to sign
a contract with them.
Then I will give you 10% of the monthly
contract or deal price every month. When
the contract ends, and we re-sign the contract, I will again give you 10% of
the monthly contract price. This will go
on until the company no longer employs or contracts me or my agents to look
after their databases.
I can do this, because that 10% is my
marketing budget. When we re-sign the
contract, in the future, it may depend on you giving the thumbs up again, and
that is worth 10% of the monthly contract price, to be given to you as
commission.
Contact:
Franz
Security
Policies - Part 4
Application Developer Security
Security DBA’s should define a security policy for
application developers using the database.
-> You can grant the privileges to the application user
to perform the required functions of creating objects etc.
-> You could require the application developer to submit
a request with regards to which objects should be created, and the database
administrator can execute the scripts, or create the objects requested. This is better, because it creates a
separation of duties.
Application Developers and Their Privileges
Database application developers need additional privileges
to other users, they need to be able to
create tables, create procedures etc.
However the specific privileges that they need should be determined, and
granted to them, preferably in the form of a role. Because you want to restrict the application
developers overall capabilities in the database.
Application Developer Environment: Test and Production
Databases
It is good practice to restrict development to test
databases. In this way the application
developers don’t compete for resources with the users, and they can’t
detrimentally affect the production database.
When an application has been thoroughly developed and
tested, it is permitted access to the production database, and is made
available to the appropriate end users.
Free Versus Controlled Application Development
The DBA, should determine which option to follow, and grant
the appropriate privileges.
-> Free
Development: is where an application
developer is allowed to create schema objects, including tables, indexes,
procedures, packages, and so on. In this
way the application developer has a large measure of independence.
-> Controlled
Development: An application developer is not allowed to create new schema
objects. A database administrator,
creates all the required objects as required by the application. The application developer will have to
request for the objects to be created.
In this way the database administrator maintains complete control over
the creation of objects and space usage of a database, as well as all the
access paths to the data.
Some installations will use
one of these options, while most will probably mix things up. They may allow application developers to
create the procedures and packages, while not allow them to create tables and
other objects. A security administrator
should base such a decision on a number of factors.
-> The control that the
DBA’s need to exercise over the space usage of a database.
-> The control desired
over the access paths to the schema objects.
-> If the database used
to develop on is purely a test database, then a more liberal development
environment can be created, although the resources are still limited, and there
needs to be some level of control.
Roles and Privileges for Application Developers
Security administrators can create roles to manage the
privileges required by the typical application developer. For example an application developer role
APP_ROLE, may include the CREATE TABLE, CREATE VIEW, CREAT PROCEDURE system
privileges. This role can then be
granted to an application developer.
When creating application developer roles keep this in mind.
-> CREATE system privileges are granted to application
developers to enable them to create their own objects. CREATE ANY system privileges, which allow a
user to create an object in any user schema, are not usually granted to a
developer. You want to restrict the
creation of new objects to the developers account as far as possible.
-> Usually you would not grant object privileges to
application developers through roles.
Receiving these privileges through roles, may restrict their usability
in creating other objects, such as views and stored procedures. Mainly it is a practical solution to allow
application developers to create their own objects for the purpose of
development.
Space Restrictions Imposed on Application Developers
Although application developers may be given the privileges
to create their own objects as part of the development process, It is good
practice to maintain limits on how much space can be used by an application
developer. You should specifically set
limits or restrict access to the following for each application developer.
-> The tablespaces in which the developer can create
tables and indexes.
-> The quota for each tablespace accessible to the
developer.
Application Administrator Security
In a large database system, with many database
applications, you can consider assigning an application administrator. An application administrator would look after
the following types of things.
-> Creating roles for an application and managing the
privileges of each application role
-> Creating and managing the objects used by a database
application
-> Maintaining and updating the application code and
Oracle procedures and packages as needed.
An application administrator may also have been the application
developer, and designer of the application.
Or the application administrator could be any individual who is familiar
with the database application.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment