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?
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.
answered 21 Dec '12, 09:19
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.
answered 21 Dec '12, 10:53