tag:blogger.com,1999:blog-3890970057024318255.post1705696408076683877..comments2024-03-15T08:36:23.954+03:00Comments on Database Administration Tips: The difference between granting direct privileges to a user and granting same privileges within a roleMahmmoud ADELhttp://www.blogger.com/profile/15299387537990081025noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-3890970057024318255.post-81816878971070865922014-11-30T21:28:43.488+03:002014-11-30T21:28:43.488+03:00electronic cigarette, e cig, e cigarette, electron...electronic cigarette, <a href="http://topecigarettesreviewed.com" rel="nofollow">e cig</a>, <a href="http://top5ecigarettesreviews.com" rel="nofollow">e cigarette</a>, <a href="http://topelectroniccigarettesreviews.com" rel="nofollow">electronic cigarette</a>, <a href="http://electroniccigarettesreviewed.org" rel="nofollow">e cigarette</a>, <a href="http://top5ecigarettesreviewed.com" rel="nofollow">e cigarette</a>Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3890970057024318255.post-17268495518498916432014-01-10T10:10:20.453+03:002014-01-10T10:10:20.453+03:00{assume that employees submit expense reports to A...{assume that employees submit expense reports to Accounts Payable (A/P), and further suppose that a user using an A/P application needs to retrieve information about employees from the hq database. The A/P users should be able to connect to the hq database and execute a stored procedure in the remote hq database that retrieves the desired information. The A/P users should not need to be hq database users to do their jobs; they should only be able to access hq information in a controlled way as limited by the procedure.}Mahmmoud ADELhttps://www.blogger.com/profile/15299387537990081025noreply@blogger.comtag:blogger.com,1999:blog-3890970057024318255.post-39246040457149406042014-01-10T10:09:00.377+03:002014-01-10T10:09:00.377+03:00Actually the role you gave to user A is not involv...Actually the role you gave to user A is not involved in the "create view" statement, once user A creates a DBLINK on his schema linking to user's B schema he will already has direct DML privileges on all user's B objects, in your example, the "create view" statement used the direct DML privilege (select) granted to user A, as user A is the owner of the database link which giving him full & direct DML privileges on all user's B objects.<br /><br />If we consider this example as a real case scenario you will not find it a security issue, because user A already knows user's B password (because he created a database link on his schema using user's B password) so he already can access user's B objects if he login to the database using user's b credentials, so once he creates the DBLINK to user's B schema Oracle gives him full DML privileges on user's B objects, so accessing user's B objects through that DBLINK is more secure than letting user A to connect using user's B credentials and have a full DDL&DML privs on user's B objects.<br /><br />Oracle gave this example to illustrate using procedures to secure data access through DBLINKs:<br />http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12086<br /><br />Mahmmoud ADELhttps://www.blogger.com/profile/15299387537990081025noreply@blogger.comtag:blogger.com,1999:blog-3890970057024318255.post-67433700085915046222014-01-09T08:50:15.498+03:002014-01-09T08:50:15.498+03:00this is correct but has an exception.
try to creat...this is correct but has an exception.<br />try to create a self dblink in the same schema where you are trying to create the view.<br />you will be able to create the view without having a direct privilge on the table.<br />example<br />I have one question:<br />I have two users A and B<br />I want to create a view under schema A. This view is on a single table owned by schema B. Name of the table owned by B is X<br />after i login as user A, II get error when I do the following<br />create view vw_1 as select * from B.X;<br />But following is successfull:<br />create view vw_1 as select * from B.X@dblink_Z<br /><br />dblink_z is a private database link owned by user A. Below is the command used to create this db link<br />create database link dblink_z connect to A identified by 'passwrd' using 'DEV707'<br /><br />DEV707 is the name of the database where both the schemas A and B resides. <br /><br />Also note that, select on B.X is explicitly revoked from A<br /><br />A is granted a role which gets him select access on B.X<br /><br />is this a security hole or oracle has some defined purpose of letting a view gets created by creating a self-dblink???tokyodbahttps://www.blogger.com/profile/04433976291289213536noreply@blogger.comtag:blogger.com,1999:blog-3890970057024318255.post-31480430571590418202014-01-09T08:49:53.094+03:002014-01-09T08:49:53.094+03:00this is correct but has an exception.
try to creat...this is correct but has an exception.<br />try to create a self dblink in the same schema where you are trying to create the view.<br />you will be able to create the view without having a direct privilge on the table.<br />example<br />I have one question:<br />I have two users A and B<br />I want to create a view under schema A. This view is on a single table owned by schema B. Name of the table owned by B is X<br />after i login as user A, II get error when I do the following<br />create view vw_1 as select * from B.X;<br />But following is successfull:<br />create view vw_1 as select * from B.X@dblink_Z<br /><br />dblink_z is a private database link owned by user A. Below is the command used to create this db link<br />create database link dblink_z connect to A identified by 'passwrd' using 'DEV707'<br /><br />DEV707 is the name of the database where both the schemas A and B resides. <br /><br />Also note that, select on B.X is explicitly revoked from A<br /><br />A is granted a role which gets him select access on B.X<br /><br />is this a security hole or oracle has some defined purpose of letting a view gets created by creating a self-dblink???tokyodbahttps://www.blogger.com/profile/04433976291289213536noreply@blogger.comtag:blogger.com,1999:blog-3890970057024318255.post-35592964195617776132013-07-14T14:13:59.151+03:002013-07-14T14:13:59.151+03:00Thanks , I have just been searching for informatio...Thanks , I have just been searching for information approximately <br />this topic for ages and yours is the best I've found out so far. However, what concerning the bottom line? Are you positive about the source?<br /><br />Also visit my web-site ... <a href="http://aireacondicionadoybombadecalor.es" rel="nofollow">bomba de calor</a>Anonymousnoreply@blogger.com