Hi. Our app runs under the dba user. The customer may, using the app, change the dba pswd. When I remote into a customer site to perform app/db maintenance, I will log in to the db as dba. One customer wants an employee to be able to run sql against the database to generate data files which will be used as import-interface files to other apps. What's the best way to give this employee read-only/select-only access?

asked 21 Dec '12, 08:59

dejstone's gravatar image

dejstone
959405069
accept rate: 0%

edited 21 Dec '12, 09:48

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819


Here's how it's done for Foxhound adhoc reporting; first, an ADHOC user id is created with no permissions other than "connect", and it is made a member of group DBA so references to tables don't have be qualified as "DBA.table_name":

GRANT GROUP TO DBA;
GRANT CONNECT TO ADHOC IDENTIFIED BY "SQL";
GRANT MEMBERSHIP IN GROUP DBA TO ADHOC;

Then SELECT is granted on each table and view that can be queried; a explicit approach like this allows you to substitute partial views for some tables and omit other tables:

GRANT SELECT ON alert TO ADHOC;
GRANT SELECT ON all_clear TO ADHOC;
...

In Foxhound's case, SELECT is granted on every table that contains data that "belongs to the user", which includes data that has been entered or gathered after installation and is visible in the application.

permanent link

answered 21 Dec '12, 09:19

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

1

Just to add:

Grant membership in group "DBA" does not give any particular DBA permissions to that user, it just allows him to qualify DBA's tables without using an owner name.

Note, however, in the rare case that DBA has been granted explicit permissions (say, 'GRANT UPDATE ON alert TO "DBA";'), then these permissions would be inherited by the user.

(21 Dec '12, 09:47) Volker Barth

This may be extreme but let me share some of our standards regarding owners and permissions. Owners of our database objects never get grant connect permission. For example we just completed the development of a Learning Management System. The owners we created to support this project was as follows:

GRANT CONNECT TO LMS; GRANT RESOURCE TO LMS; COMMENT ON USER LMS IS 'Emprise Learning Management System Object Owner';

GRANT CONNECT TO _LMS; GRANT RESOURCE TO _LMS; COMMENT ON USER _LMS IS 'Emprise Learning Management System Public Access Object Owner';

GRANT CONNECT TO _LMSAccess; GRANT RESOURCE TO _LMSAccess; GRANT GROUP TO _LMSAccess; COMMENT ON USER _LMSAccess IS 'Emprise Learning Management System Public Access Group';

We do this in part to partition database functionality by owner. Additionally we use the _Owner for all objects referenced directly by our client / server applications or via the db web services. Also all access to underlying data is performed using views and procedures for select, and procedures or functions for inserts updates etc.

In this case if I wanted to provide read only access I might create the group _LMSReadOnly and provide select permission on all data views.

Finally each application user is provided his own database login and password and is granted access levels within our applications via internal user permission objects which are checked in the appropriate data access procedures and or functions. We manage these permissions using User Roles.

This may seem excessive but provides many advantages when upgrading applications and changing underlying table structures.

Jim

permanent link

answered 21 Dec '12, 10:53

J%20Diaz's gravatar image

J Diaz
1.2k394968
accept rate: 10%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×28
×25

question asked: 21 Dec '12, 08:59

question was seen: 6,251 times

last updated: 29 Mar '19, 04:33