grant over database link
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
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.
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.
"There is a difference between knowing the path and walking the path."
... if that's the way you have set it up.
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).
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?
I remember when this place was cool.
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:
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.
CREATE PUBLIC DATABASE LINK marist USING 'greystone.marist.edu';
On greystone.marist.edu, the DBA can:
The security will be maintained for userA and userB regardless of the fact you are using a database link.
GRANT SELECT, INSERT, UPDATE, DELETE ON myStudents TO userA;
GRANT SELECT ON myStudents TO userB;
...but userB would get a "permission denied" error if he tried to execute the same query.
UPDATE myStudents@marist SET grade = 'A' WHERE id=1988373;
Click Here to Expand Forum to Full Width