Results 1 to 3 of 3

Thread: Users and roles

  1. #1
    Join Date
    Feb 2004
    Posts
    22

    Question Unanswered: Users and roles

    Hi,
    Is there any naming standard template to set up the users and roles for an OLTP web based application oracle 9i database. (all intranet only)

    1. which is consistent for test / devl / prod instances
    2. Select user for report access
    3. Update user for insert/upd/del activities via applicaiton
    4. Admin user to manage admin funcs.

    How do we generalise set up the users and roles for the test/devl/prod databases ?

    Thanks in advance
    Aravind

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    you're asking the 64 million dollar question, as far as I know there is no standard which is surprising given the amount of time I have watched being wasted while a team agrees one.

    I think at the end of the day, as long as there is some form of agreement between the developers you'll be fine.

    You can spend days discussing the pros and cons of call a trigger "<object_name>_TRG" versus "TRG_<object_name>".

    Check out Oracle's own schema (SYS) and you'll see little sign of a standard being applied.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You will use the same set of roles and role privileges on development, test and production databases. Only the users will vary. You need a script (or set of scripts) to set up the roles and role privileges, which is then run on each database.

    Now all that remains is to grant appropriate roles to new users as they are set up, which is simple enough. If you have a situation where new users are created frequently, then you may want a package to simplify the process so that it becomes something like:

    exec user_pkg.add_user(username=>'FRED',admin_role=>FAL SE,update_role=>TRUE)

    That could then be called from a simple form with a few check boxes for the different roles.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •