1

What is the best approach (or what options am I not aware of) to creating a static table or view where the data rarely, if ever, changes? An example use-case is a list of US states, where my structure would look something like:

state(
  id NUMBER(15) PRIMARY KEY,
  name VARCHAR2(35),
  abbreviation VARCHAR2(35)
)

The two approaches I am currently evaluating are:

Create a table and insert the desired data using a one time data import script

CREATE TABLE state
  (
    id           NUMBER(15) PRIMARY KEY,
    name         VARCHAR2(35),
    abbreviation VARCHAR2(2)
  );
INSERT INTO state (id, name, abbreviation) VALUES (1, 'California', 'CA');
...

Concern: data can be manipulated easily and modifying these records would break other parts of the software

Create a view using unions of hard-coded values

CREATE OR REPLACE VIEW state
AS
  SELECT 1 AS id, 'California' AS name, 'CA' AS abbreviation FROM dual
  UNION
  SELECT 2 AS id, 'New York' AS name, 'NY' AS abbreviation FROM dual 
  UNION
  ...

Concern: maintenance, potential for errors when inputting (as opposed to using a sequence and trigger to populate id, for example), and it just doesn't feel right

  • disclaimer: i am not a dba, so i may be missing something fundamental that is available, i'm open to any propositions – invertigo Nov 3 '14 at 17:13
  • Before adding an id column, think about what uniquely identifies a state. Are these attributes simple and stable enough to use as a primary key? – Lennart Nov 3 '14 at 17:33
  • states was simply my example. i need a key independent of the data to be used as a foreign key in another table. while the data field(s) will not change often, if ever, there is the potential and in the case of a change, i would need references to be maintained. – invertigo Nov 3 '14 at 17:38
  • also, for all intents and purposes, the user i am trying to protect from themselves is a dba. i know technically they will be able to make any modifications they want, but the harder i make it, the less likely it will occur (be it on purpose or accidentally) – invertigo Nov 3 '14 at 17:40
  • 1
    I think you may be barking up the wrong tree here, so to speak. Security should be enforced by the tools designed to enforce security, not by making data maintenance more difficult. – mustaccio Nov 3 '14 at 17:55
3

Whether the data can be manipulated by a user depends on the rights he has. Rights to database objects like tables and views are given either directly to a user or via a role. Roles have the advantage that you can assign them to new users easily without having to deal with the details. I would suggest at least two roles: An admin role and a user role.

CREATE ROLE myApp_Admin;
CREATE ROLE myApp_User;

Assign rights to the roles:

GRANT SELECT, INSERT, UPDATE, DELETE ON state TO myApp_Admin;
GRANT SELECT                         ON state TO myApp_User;
-- ... grant rights to other tables and views here ...

Then, assign these roles to users

GRANT myApp_Admin TO invertigo;
GRANT myApp_User TO someoneElse;

Concern: data can be manipulated easily and modifying these records would break other parts of the software.

This should not be the case, if the database has been designed carefully. If you are using referential integrity, the users should not able manipulate the data in a way that corrupts the application. If you have a table employee that refers to state, then define a foreign key constraint on this column:

ALTER TABLE employee ADD CONSTRAINT FK_emp_sta_myApp
FOREIGN KEY (state_id)
REFERENCES state.id;

With this constraint it is not possible to delete a state or change its id as long as it is referenced in employee.


A completely different way of solving this problem, is to not have a lookup table (or view) at all in your db, but to keep the lookup information within your application (e.g. as embedded xml resource).


Yet another solution:

Create the table as another oracle owner and grant the select right to your usual application owner. Here you can then create a view.

CONNECT otherOwner;
CREATE TABLE state (...);
GRANT SELECT ON state TO appOwner WITH GRANT OPTION;
-- GRANT OPTION allows appOwner to grant this SELECT right to your users or roles.

CONNECT appOwner;
CREATE VIEW state AS
    SELECT * FROM otherOwner.state
    WITH READ ONLY;

or you could just create a synonym (as appOwner):

CREATE SYNONYM state FOR otherOwner.state;

In both cases your users must not have access to otherOwner.

  • unfortunately for all intents and purposes the user is a dba (as far as roles are concerned, not necessarily in regards to knowledge, training, or experience). i know this means they could modify the view, but its not as simple to do so as it would to fire up the sql developer gui and monkey around with data in a table. – invertigo Nov 3 '14 at 17:33
  • i do like your suggestion of an embedded xml resource, but this table/view will be accessed from application code and pl/sql code. – invertigo Nov 3 '14 at 17:47
  • while this solution is not really an option in my situation, it is the best answer and would be my ideal setup – invertigo Nov 3 '14 at 21:19
1

You should create a table and grant the appropriate permissions so that only the intended users have update capabilities. I strongly discourage using views in the manner you suggested.

Since you are creating a table of states you don't need an ID value since each state is unique. The state or abbreviation could be the primary key. If you must use an ID column then I suggest letting the database make that assignment for you. Since you are using Oracle then you should use a sequence (and a trigger if you don't want to use the ID in the insert statement).

  • states was just my example, i do need an id that is independent of the data. and i like using the sequence/trigger for populating the id. is there a reason you discourage using a view in this manner? – invertigo Nov 3 '14 at 17:36
  • 2
    A view created in such a way would be a nightmare to manage because there is no underlying base table. A good DBA would not allow this in their environemnt. Permissions should be applied to the base table that only allows authotized users to make changes. If users have DBA privelages then that issue needs to be discussed with management. Elevated permissions should always be kept to a minimum. If the user needs to be able to log into the database directly then only grant them create session and select table to the appropriate schema. – theSqlGuy Nov 3 '14 at 18:32
1

It would be difficult to say whether this solution would be preferred without knowing more about the nature of the data you are storing and how it will be accessed, but here is another idea you may not have considered.

Since your data is fairly static and will be accessed from PL/SQL and the application, you could simply embed the data in a PL/SQL package. The package could be as simple as a look-up function to retrieve a value based on a given key. The application could use the same package to retrieve the data.

The function would probably be deterministic and probably in a separate package from the code consuming it.

The view and/or table may be preferable if you are mostly consuming the data in SQL.

By the way, modifying a view is not really any more difficult than manipulating data - assuming DBA privileges.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.