>When granting a role to a user the user have to re-login to get use of that role.
>When granting a privilege to a user the user can use it immediately. NO need to re-login.
>Roles doesn't give the granted user the right to create objects based on the role given to him.
Come again.. What does the last point means?
For example: if there is a role includes a SELECT privilege on table X1 on schema X and this
role granted to user Y, User Y can select from table X1, but once user Y wants to create a
view on his schema selecting from table X1, he will get ORA-01031: insufficient privileges. in
this case you have to grant user Y a direct select privilege on table X1 to be able to create
a view based on that table
The following example will illustrate this point:
--By user SYS
--create a new role HR1, grant to it select privilege on hr.employees, then grant this role to user SH:
SQL> create role hr1;
SQL> grant select on hr.employees to hr1;
SQL> grant hr1 to SH;
--By user SH
--now try to test the role granted to SH:
SQL> select * from hr.employees;
multiple rows returned...
--but when user SH tries to create a view based on the SELECT right he inherit it from the role:
SQL> create view empl as select * from hr.employees;
ERROR at line 1:
ORA-01031: insufficient privileges
--By user SYS
--User SH must have a direct privilege grant (not within a role) to be able to create objects based on it:
SQL> grant select on hr.employees to SH;
--By SH
--now user SH can create objects based on select privilege he has on hr.employees table:
SQL> create view empl as select * from hr.employees;
View created.
In Arup Nanda blog he explained the difference between the system privilege SELECT ANY DICTIONARY and the role SELECT_CATALOG_ROLE based on this point.
I hope that was informative.
Thanks , I have just been searching for information approximately
ReplyDeletethis 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?
Also visit my web-site ... bomba de calor
this is correct but has an exception.
ReplyDeletetry to create a self dblink in the same schema where you are trying to create the view.
you will be able to create the view without having a direct privilge on the table.
example
I have one question:
I have two users A and B
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
after i login as user A, II get error when I do the following
create view vw_1 as select * from B.X;
But following is successfull:
create view vw_1 as select * from B.X@dblink_Z
dblink_z is a private database link owned by user A. Below is the command used to create this db link
create database link dblink_z connect to A identified by 'passwrd' using 'DEV707'
DEV707 is the name of the database where both the schemas A and B resides.
Also note that, select on B.X is explicitly revoked from A
A is granted a role which gets him select access on B.X
is this a security hole or oracle has some defined purpose of letting a view gets created by creating a self-dblink???
this is correct but has an exception.
ReplyDeletetry to create a self dblink in the same schema where you are trying to create the view.
you will be able to create the view without having a direct privilge on the table.
example
I have one question:
I have two users A and B
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
after i login as user A, II get error when I do the following
create view vw_1 as select * from B.X;
But following is successfull:
create view vw_1 as select * from B.X@dblink_Z
dblink_z is a private database link owned by user A. Below is the command used to create this db link
create database link dblink_z connect to A identified by 'passwrd' using 'DEV707'
DEV707 is the name of the database where both the schemas A and B resides.
Also note that, select on B.X is explicitly revoked from A
A is granted a role which gets him select access on B.X
is this a security hole or oracle has some defined purpose of letting a view gets created by creating a self-dblink???
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.
ReplyDeleteIf 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.
Oracle gave this example to illustrate using procedures to secure data access through DBLINKs:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12086
{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.}
ReplyDeleteelectronic cigarette, e cig, e cigarette, electronic cigarette, e cigarette, e cigarette
ReplyDelete