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
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."
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).
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:
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:
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.
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.