Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Unanswered: User Read Only Sybase

    Can you help me. I have an account that access to the database must be read-only.

    Thank's
    Gianluca

  2. #2
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Ok, that's not a problem. So what is you exact question ?

    Are you asking how to set up a read-only account or, you already have a read-only account and you want to do something else ?
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  3. #3
    Join Date
    Feb 2009
    Posts
    6
    I ask how to set up a read-only account for all databases. Excuse, my english.

  4. #4
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Easy.

    First the server and databases must have ordinary security implemented, using ANSI SQL conventions, since we dealing with ANSI SQL databases. You can have more security in your corporate security model, but you cannot have less that what is available via ANSI SQL. In this day and age we want databases that are Open (available to any client app) but secure (specific access available to specific users).

    The old model uses Groups (limited to a single db); the current model uses Roles (server-wide), I am giving you Roles. This means:
    a every person has a separate Login and password
    b Logins are granted to any number of Roles
    c the tables have SELECT/REFERENCE Granted to 'public' (any person added as an User can read all tables).
    You might have a Role for SELECT/REFERENCE on all tables instead of to 'public'.
    d In a very secure environment, SELECT/REFERENCE is Granted to specific Roles (only specific Logins can read).
    e the tables have INSERT/UPDATE/DELETE granted to no Login, no Role (the tables cannot be accessed and changed directly, you must go through a stored proc, which enforces transactional integrity)
    f the tables are INSERT/UPDATE/DELETEd via stored procs only
    g the stored procs have EXECUTE granted to specific Roles
    h a number of Roles have been set up in the db to EXECUTE procs

    Now life is easy:
    - When you add a new user, you simply add a Login, and add them as an User to each db, read-only access for all dbs/tables. If (c) you can stop right there.
    - Where (d) is in place, Grant the Login whatever (read-only) Roles are required for their job function, which are authorised by their manager.
    - For update access, Grant the Login whatever (execute proc) Roles are required for their job function which are authorised by their manager.

    ----------

    If you do not have the above minimum security in place, then life is hard labour in an American prison. There's no way for us to know what needs to be done, we need to know what is in place first. Depending on what you have at your site, it could be anything between the above and the below. Here's the minimum:

    1 Ensure the dbs have a 'guest' user
    2 Grant SELECT/REFERENCE to 'public' (public is the group you belong to when you do not belong to a group)
    3 Ensure INSERT/UPDATE/DELETE is not Granted to 'public'
    4 Add the new user as a Login
    5 For each db, add the Login as an User.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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