Role Maintenance

pts.
Tags:
Database
DB2
Oracle
Background: oracle 9.2.0 running on win2k I got 2 logical userids A) OPTELADMIN --given RESOURCE, CONNECT roles and owned all the user created objects such as tables, indexes, sequences, etc. This userid is shared by 3 physical developers B) OPTEL --given CONNECT,CREATE SYNONYM roles and physically shared by 4-5 end application users. End application users manipulate the data via application interface. That is, they logon, logoff, delete, insert, delete data via application, they do not have command line to execute SQL Everytime, OPTELADMIN had tested the application & is ready to let OPTEL used the new tables, i had to do 2 things A) Create the synonym, for OPTEL to use the tables,eg, CREATE SYNONYM Cust FOR opteladmin.Cust; B) Create the grants, for OPTEL to manipulate the tables, eg, GRANT SELECT, INSERT, UPDATE, DELETE ON Cust TO optel; This is very manual. Is there any suggestion how i can automate this?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Granting permissions to a table after it is created is pretty much necessary. You could eliminate creating the synonym if you specify the owner in the application. (ex. select column,… from owner.table;) Keep in mind, if you do this, the application is tied to the owner of the tables; so if the owner changes, you would have to change the applications. I have done it this way for years and never had the need to change the owner even once; but each to their own.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bozzo999
    Should be easy enought to script (if you already haven't). Still a step or two depending on how you do it. But very simple steps. Ken
    0 pointsBadges:
    report
  • Bozzo999
    Should be easy enought to script (if you already haven't). Still a step or two depending on how you do it. But very simple steps. Ken
    0 pointsBadges:
    report
  • Mayleong
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/role-maintenance/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following