DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: grant over database link

  1. #1
    Join Date
    Dec 2002
    Posts
    110

    grant over database link

    Hi

    How do i grant access on objects in one db to a user in a different database over a db link. WHat is the command.

    I have 2 databases (ora1 & ora2) both 8i . i have created a database link from user1 in ora1 to user2 in ora2 .

    Now i want to grant execute on certain object types created in user1 of ora1 to user2 of ora2 so that it can be used in stored procedures in user2 schema of ora2.
    Is it possible to give such a grant over a db link or will i have to recreate the whole type in user2 schema of ora2 db.

    If its possible to give such a grant over db link please do respond

    Regards
    Sushant

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    I haven't tested this (and so I may be wrong...) but here is my thought if I understood the question correctly --

    You have a stored procedure in schema2 (user2) of db2
    This procedures touches some objects in schema1 (user1) of a remote database (db1)

    1. You should grant appropriate permissions (execute etc.) to the user1 in db1.
    2. From db2, you should create a db_link with fixed user(user1) to db1.

    Let me know how it goes.
    Thanks,
    Rajeev Suri

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    DDL across dblinks is not possible. DML is possible. Remember whenever you use dblink to perform a DML on a remote database you are connecting to the remote database as the remote database user (not as the local database user).

    So what ever DML that remote user can perform on the remote database, you can perform the same when you use the dblink.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by adewri
    Remember whenever you use dblink to perform a DML on a remote database you are connecting to the remote database as the remote database user (not as the local database user).
    ... if that's the way you have set it up.
    Jeff Hunter

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by marist89
    ... if that's the way you have set it up.
    What are the other ways ?? Please enlighten me. I'm all ears.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Jan 2001
    Posts
    3,134
    Great care must be taken when setting up DB_LINKS, if you use a DBA id, that link will have DBA access to the box it attaches to, that could be a bad thing.

    I think that is what the great Jeff (my hero) is hinting at, no?

    MH
    I remember when this place was cool.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You can setup a database link that connects to the remote database as the local user. (I call it an un-authenticated dblink) For example:

    Code:
    CREATE PUBLIC DATABASE LINK marist USING 'greystone.marist.edu';
    Then, each user will be re-authenticated on greystone.marist.edu. Of course, the user must exist on mydb.marist.edu, but in a distributed environment where you want to assign different permissions to different users, this is a given.

    On greystone.marist.edu, the DBA can:

    Code:
    GRANT SELECT, INSERT, UPDATE, DELETE ON myStudents TO userA;
    GRANT SELECT ON myStudents TO userB;
    The security will be maintained for userA and userB regardless of the fact you are using a database link.

    userA can...
    Code:
    UPDATE myStudents@marist SET grade = 'A' WHERE id=1988373;
    ...but userB would get a "permission denied" error if he tried to execute the same query.
    Jeff Hunter

Posting Permissions

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


Click Here to Expand Forum to Full Width