#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0

    SQL query to grant permission


    I want sql querys for following scenarios

    Owner of the compant wants to create 5 users,3 in following fashion
    1) 2 -Must have authority of admin
    2) 2- Normal user
    3) 1 - User whose password is blocked for 15 days
  2. #2
  3. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    119
    What kind of users you are talking about ?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by debasisdas
    What kind of users you are talking about ?
    Administrator
    Normal User
    Normal User whose account is blocked for 15 days
    any scenario you can consider
    Example -:College database
    Teacher has right to upddate and delete marks and many other right like admin
    student can only log in and can see results and raise objection.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I believe what debasisdas is asking (as I would) about kind of user is:
    e.g.
    Windows user
    or
    Database user
    or
    Application user
    or
    ???

    Comments on this post

    • debasisdas agrees : Yes, that is exactly what i was asking.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by clivew
    I believe what debasisdas is asking (as I would) about kind of user is:
    e.g.
    Windows user
    or
    Database user
    or
    Application user
    or
    ???
    Its application user
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Its application user
    Then you just need a column in your APP_USERS table for user type.
    Also, from a schema design perspective, you should have a USER_TYPES table.

    USER_TYPES fields
    USER_TYPE_ID INTEGER PK,
    USER_TYPE_DESCRIPTION VARCHAR2(100)

    APP_USERS or whatever you have called it
    All your current fields
    USER_TYPE_ID INTEGER FK to USER_TYPES

    Now you simply add the USER_TYPE_ID field to your SQL and use an if..then or case statement in your
    application code to determine what the user can do.

    Edit
    -----
    I forgot, you will also need a DATE_ADDED field if you need to deal with 15 day time periods.
    Clive
    Last edited by clivew; October 9th, 2012 at 07:47 PM. Reason: Added column for handling elapsed time.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by clivew
    Then you just need a column in your APP_USERS table for user type.
    Also, from a schema design perspective, you should have a USER_TYPES table.

    USER_TYPES fields
    USER_TYPE_ID INTEGER PK,
    USER_TYPE_DESCRIPTION VARCHAR2(100)

    APP_USERS or whatever you have called it
    All your current fields
    USER_TYPE_ID INTEGER FK to USER_TYPES

    Now you simply add the USER_TYPE_ID field to your SQL and use an if..then or case statement in your
    application code to determine what the user can do.

    Edit
    -----
    I forgot, you will also need a DATE_ADDED field if you need to deal with 15 day time periods.
    Clive

    Thanks
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by kappy
    Thanks
    What if its database user? Do I need to use grant command to assign rights.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    What if its database user? Do I need to use grant command to assign rights.
    For any normal user, of course.
    You will have to consult the documentation (or other responses here) for the specifics of any super users
    like schema owners, administrators etc.

    Clive
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by clivew
    For any normal user, of course.
    You will have to consult the documentation (or other responses here) for the specifics of any super users
    like schema owners, administrators etc.

    Clive
    with your reply.
    Can you elaborate in simple words?
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Can you elaborate in simple words?
    I will try
    However, it looks as if you could benefit from reading some of the Oracle documentation on users and grants.

    (all subject to being corrected as I am going from memory)
    BASIC
    Any user who creates an object (TABLE, PROCEDURE, VIEW, SEQUENCE etc.) is the
    only user who can access that object unless that user GRANTS permission to other users by name or to PUBLIC.
    Permissions can also be granular. e.g. You could grant someone SELECT and UPDATE permission on a table but not INSERT and DELETE.

    As I understand it, there are also certain super users like DBA and system manager who have rights over everything whether they created it or not.

    There is a lot more to the whole subject.
    Lots of rules I have not mentioned and things I have mentioned that probably have all sorts of additional variants.

    For your purposes it is probably sufficient to know that you have to grant permission of some sort on objects you create for any other regular user to be able to access them.

    Clive

IMN logo majestic logo threadwatch logo seochat tools logo