I want to find role dependency on another role.
1. If its a basic role (made of priviliges), what data dictionary view I should query?
2. If the role is made of some other role and additional priviliges, what query I should fire to find that?
3. Role is granted to which users?
I have successfully set up a (test) environment for single-instance Grid Infrastructure and Oracle database using job role separation. So I have the recommended grid and oracle users, and the oinstall, dba, oper, asmadmin, asmdba and asmoper groups. I have the following directory structure for my Oracle Bases and Oracle Homes:
/u01/app/11.2.0/grid - GI home
/u01/app/grid - GI base
/u01/app/oracle - DB base
/u01/app/oracle/product/11.2.0/db_1 - DB home
Platform is OEL5, 11.2.0.1 GI and DB.
This all works fine.
What I now want to do (since what I'm trying to do is make this environment as secure as practical) is set up an additional sysoper operating system account, so that that user can connect to carry out sysoper tasks, amongst other things stopping and starting the instance.
So (as the oracle user) I do the following:
$ sqlplus / as sysdba
SQL> create user test identified by passwrod;
SQL> grant sysoper to test;
SQL> exit
As root I do the following:
$ useradd -g oper testoper
$ su - testoper
Now as testoper:
$ sqlplus 'test/password as sysoper'
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[code].........
As you can see, shutdown works, but startup doesn't. I have registered an SR with Oracle on this, but they're not being useful. The suggestion was that I should give testoper the to install secondary group. But this is not a secure solution as this now gives testoper privileges to do things in OB / OH that it really shouldn't be able to do. No other suggestions have been forthcoming. The reason for the suggestion however is the ownership / permissions on the Grid OH oracle executable:
[root@db03 ~]# ls -la /u01/app/11.2.0/grid/bin/oracle
-rwsr-s--x 1 grid oinstall 184286237 Aug 22 11:15 /u01/app/11.2.0/grid/bin/oracle
As you can see, it has group oinstall, so you can see why giving the user oinstall group would work. But in my view this is not satisfactory.
One thing that occurred to me is that in this environment I should arguably in fact be using srvctl to stop / start instances. But that means setting up a user with asmoper role (presumably) rather than oper. That user would then (again presumable) be able to stop and start other GI resources, which is not what I want.
How can i find out or list all the privileges that were given to a role with sqlplus cmd?
View 2 Replies View RelatedI have a role R.I want to see what privileges have been granted to this Role R. Where should i query?
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - Production"CORE 11.2.0.3.0 Production"TNS for Solaris: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production
How CONNECT Role in oracle works.
I have my schema "SchemaABC" with ROLES created as CONNECT and RESOURCE. Also it doesn't have DBA role (to say).
SQL> Select * from User_Role_Privs;
USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
SCHEMAABC CONNECT NO YES NO
SCHEMAABC RESOURCE NO YES NO
SQL>Currently we are able connect to this schema through code as well as from PL/SQL Developer. And uses it for all coding purposes like Create, Insert, Update, Stored Procedures, functions, triggers etc.
Now my question is, can this schema be changed to a role/privilege where all the above said activities can be done through code, but not from PL/SQL developer (making it to Read-only)? Yet, I will be using the same schema Name in code as well to connect from PL/SQL Developer.
I understand Connect role has big share in this. But if I remove the connect privilege(role), then will that work when I connect to it through code?
I am observing a strange behavior for the ROLES:
The DBA granted the RESOURCE role for my user USER1:
GRANT RESOURCE TO USER1;
The USER1 logged and activated the role RESOURCE.
CONN USER1/USER1
SET ROLE RESOURCE;
... it has some privileges in database ( UNLIMITED TABLESPACE for example that grant to him create table in any tablespace).
Now I created a new tablespace, but the user1 cannot use this new tablespace.So I will to revoke the RESOURCE for the USER1:REVOKE RESOURCE TO USER1;
It seems ok, but the user USER1 can still create tables in the new tablespace.I repared that UNLIMITED TABLESPACE still active, and the REVOKE RESOURCE just will have effect in the next LOGON of the USER1.
How can I REVOKE the ROLE and disable all the privileges from it immediately?
I have a role mfg_grp .I want to know, to whom the role is granted and which objects and which privileges on those objects are assigned to this role.
View 4 Replies View RelatedI bought Selftestsoftware for 1z0-147 for 9i and 10g. Selftestsoftware is endorsed by Oracle, should be high quality.
But its below sample question and answer seem to be wrong: It says that privilege for subprogram can be granted via role. But from Urman 9i book: the grant the execute privilege must be done explicitly and not through a role.
Did Selftestsoftware made a mistake? Or the question did not mention or assume that the subprogram is based on invoker rights not definer right?
Quote:
Question: All users in the HR_EMP role have UPDATE privileges on the EMPLOYEE table. You create the UPDATE_EMPLOYEE procedure. HR_EMP users should only be able to update the EMPLOYEE table using this procedure. Which two statements should you execute? (Choose two.)
GRANT UPDATE ON employee TO hr_emp;
GRANT SELECT ON employee to hr_emp;
REVOKE UPDATE ON employee FROM hr_emp;
REVOKE UPDATE ON employee FROM public;
GRANT EXECUTE ON update_employee TO hr_emp;
Explanation:
The two statements you should execute are:
REVOKE UPDATE ON employee FROM hr_emp;
GRANT EXECUTE ON update_employee TO hr_emp;
Unless you are the owner of the PL/SQL construct, you must be granted the EXECUTE object privilege to run it or have the EXECUTE ANY PROCEDURE system privilege. By default, a PL/SQL procedure executes under the security domain of its owner. This means that a user can invoke the procedure without privileges on the procedures underlying objects. To allow HR_EMP users to execute the procedure, you must issue the GRANT EXECUTE ON update_employee TO hr_emp; statement. To prevent HR_EMP users from updating the EMPLOYEE table unless they are using the UPDATE_EMPLOYEE procedure, you must issue the REVOKE UPDATE ON employee FROM hr_emp;
All of the other options are incorrect because they will not meet the specified requirements.
Is there any Role where 'exp_full_database' role is contained in it? or it is compulsory to grant to the user for exporting objects.,
View 2 Replies View RelatedBut what I noticed is that user HIEL has a connect role but YONC does not. (Result set is below) Have searched for information but did not find what I was looking for. I did read something about backward compatibility.I'm leaning towards that thought since the other user YONC does not have this role.
Database: 11g R1
SQL> select * from dba_role_privs where grantee in ('YONC', 'HIEL') order by grantee;
GRANTEE GRANTED_ROLE ADM DEF
--------------------------- --------------------------- --- ---
YONC WCAIMS_INQUIRY NO YES
YONC ENDUSER NO YES
HIEL WCAIMS_CSR_ADDR NO YES
[code].........
i have user with the name 'Rob' and this user has been assigned a role 'MY_SRC_ROLE' . I developed a table under rob schema and granted access to this table via role GRANT DELETE, INSERT, SELECT, UPDATE ON rob.emp TO MY_ SRC_ ROLE; I have 100 more users & they have been granted this role 'MY_SRC_ROLE'. These 100 users can now access emp table via Role 'MY_SRC_ROLE' without any issues. Now i took a datapump export & performed datapump import on target server which is also HP Unix with 11.20.3 .
On target server i have user 'JACK' and a role called 'MY_WORK_ROLE'. 5000 users have been granted 'MY_ WORK_ ROLE' on this server. I have used remap tablespace clause & remap schema clause in datapump import script. Once i performed an import , due to schema remap , i can see JACK now owns table 'emp', however grants are still not there, I tried searching on Google & oracle documentation, if somehow we can remap ROLE GRANTS also while doing datapump imp, but i couldn't find supporting syntax. can i assume datapump import is not capable to handle this particular scenario ? I was able to do it by manipulating sqlfile and replacing role name in that but i am looking for a sol. within datapump itself. how can grants assigned to ROLE 'X' be transferred to 'Role Y' via datapump import.
is there a way to create a role just like DBA role?
View 6 Replies View RelatedI need to interchange the value which has foreign key depednecy,
For ex: I have a table dept
deptno| dname|dseq|
10 | CS | 1
20 | SC | 2
and in emp table
eno | ename | deptno |
1 |Raj | 10 |
2 |Ram | 10 |
3 |Vip | 10 |
4 |Muk | 10 |
5 |Sn | 20 |
6 |Snj | 20 |
7 |Asj | 20 |
there is a forign key define :
ALTER TABLE emp ADD (
CONSTRAINT rop_fk
FOREIGN KEY (deptno)
REFERENCES dept (deptno));
Now I want to change the deptno from 10 to 20 and 20 to 10 in dept table . So the ouput will be like
deptno| dname|dseq|
20 | CS | 1
10 | SC | 2
when i am deleting that row I am getting error "ORA-02292: integrity constraint (ROP_FK) violated - child record found".IF i am trying to update i am getting unique key contractin failure.
(Even I cannot update the rest of the column values in dept table as it is also having foreign key dependency) i need to put a sql script in production so that it will interchange.
Is it possible to find the dependency of a Function whether it is used somewhere in the package or procedure or some other functions?
Suppose I have function named get_valid_loc , Now my question is how to find out in which place this function has been used.
While there's numerous QAs about inserting an image into the DB using the Data Block, how does one remove an image? Obviously there's the "UPDATE ... SET X = EMPTY_BLOB();", however, that kills the Data Block/Form's flow, and it doesn't update.
I've tried a few things:
* :CONTROL.IMG1 := NULL; (bad bind variable)
* :CONTROL.IMG1 := EMPTY_BLOB(); (some other error, probably as above)
I have an procedure with four in parameters in it. Now, I want to add an out parameter to it. The issue is the procedure is called from many other procedures and triggers. Since we can't set default value to out parameter as we can for in parameter, how we can achieve the requirement without having to update all the places where this procedure is being called.
View 3 Replies View Relatedthe literature equates dimension hierarchies with fuctional dependencies between the levels. I like to tst the strength of this assumption with the implementation of 'CREATE DIMENSION' which allows you to create roll-up hierarchies.
My question to put it simply is this: Given:
CREATE DIMENSION location_dim
LEVEL location IS (location.loc_id)
LEVEL city IS (location.city)
LEVEL state IS (location.state)
HIERARCHY geog_rollup (
location CHILD OF
city CHILD OF
state CHILD
)
Can I insert the following rows into the dimension:
loc_id, city, state
1, Epping, NSW
2, Epping, VIC
Please note that the two Eppings are different cities.
Given the roll-up hierarchy City -> State, will it require that for every city there can be only one state in which case the FD between City and State cannot hold. Or, is it that the roll-up hierarchy defined here has nothing to do with FD.
The second part of the question is if the answer to the above question is that the roll-up is not the same as FD, then is the ATTRIBUTE clause meant to define the n:1 (functional dependency) instead?
I'm trying to find a way to ADD new partitions to local indexes and at the same time specify their tablespaces without having to DROP and RECREATE.
Here´s an example table based on yearly partitioning:
CREATE TABLE "TABL_ANOM"
(
ANOM_TS TIMESTAMP(6) NOT NULL
, ANOM_TIPO NUMBER(2, 0) NOT NULL
, ANOM_NIVEL NUMBER(2, 0) NOT NULL
, ANOM_ID NUMBER(10, 0) NOT NULL
[code]...
Here´s an index def for the table:
CREATE INDEX "TABL_ANOM_INDEX1" ON "TABL_ANOM" ("ANOM_NIVEL") LOCAL
(PARTITION DGSCOPSX_2011 TABLESPACE DGSCOPSX_2011
,PARTITION DGSCOPSX_2012 TABLESPACE DGSCOPSX_2012
)
OK. Now I want to add partitions for 2013 so for the table I use:
ALTER TABLE TABL_ANOM ADD
PARTITION DGSCOPS_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE DGSCOPS_2013;
and this works fine for the table but I can't find a similar command to simply add additional partitions to the indexes. I know that I can drop and recreate the indexes with the additional partition defs but on some of my tables, I'm dealing with hundreds of millions of rows and I think it would take way too long to drop and recreate all indexes on all partitions.
Also related is the PRIMARY KEY index partitions. Is there a way to add partitions (specifying the tablespaces) without having to DROP and re-ADD the CONSTRAINT with the additional partition for 2013?
I would like add an additional column to the data below:
create table test(
id number
cust_num varchar2(5));
[Code]....
Result:
001, AODER, 'Y'
001, BODER, 'Y'
001, CODER, 'Y'
001, DODER, 'Y'
001, 'NONE', 'Y'
001, 'NONE', 'Y'
[Code]...
I would like to add an additional column indicator (Y or N) to specify which ID's do not contain all records of 'NONE'. There can be an occurrence of 'NONE' as long as there is another cust_num different to 'NONE' These should be marked as 'Y' but in cases where all the ID's cust_num = 'NONE' only then these should be marked as 'N'.
I need some functionality in current (4.2.2) APEX websheet which seems not possible out of the box.Therefore I'd like to ad a javascript to my websheet application.
View 0 Replies View Relatedsuggest on the dependenecy between the database service and the database instance?
srvctl start instance -i db1
will this start service as well for which the above instance is either preferred or available instance?
will it start the listener as well?
srvctl stop instance -i db1
will this stop service as well for which the above instance is either preferred or available instance?
will it stop the listener as well?
srvctl start service -d db -s dbserve
will this start service as well for which the above instance is either preferred or available instance?
srvctl stop servive -d db -s dbserve
will this stop service as well for which the above instance is either preferred or available instance?
srvctl start database -d db
will it start instance, listener and service as well?
srvctl stop database -d db
will it stop instance, listener and service as well?
I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.
e.g.
NAME_TABLE
-reg_id
-name
ADDRESS_TABLE *trigger to be fired when a new record is created here.
-reg_id
-srv_id
PROCESS_TABLE
-srv_id
-start_time
-end_time
This is what I would like the logging table to look like:
LOGGING_TABLE
-address_table_reg_id
-address_table.srv_id
-name_table.name
-process_table.start_time
-process_table.end_time
How can I go about creating this type of trigger?
It's been a while since I worked with SQL Plus . I am using Oracle 11g. We are working on a legacy data migration project. I have a table of records with circular dependency records. i am trying to identify the records. I have the foll. columns- Product,Source,target. I want o identify the records which form a loop. For e.g.
Source Target
A B
B C
C D
D A
Last record forms a loop-I need to identify these records. My query is below-
SELECT DISTINCT SOURCE,TARGET FROM RULESELIB WHERE CONNECT_BY_ISCYCLE=1 CONNECT BY NOCYCLE SOURCE=PRIOR TARGET;
I ran this query on 2 tables- one with 75000 records and the other with 25000 records. It works fine on the table with 75000 records completes within a minute but it does not complete on the other table. I can't seem to be able to figure out the issue with the query or is there something about the data that is causing this query to loop infinitely?
I receive the error,
REP-1241 Circular column dependency originating with column 'cenvat_opening'
I have the following return statement for a column total_credit;
function total_creditFormula return Number is
begin
Return (return nvl(:cf_cenvat_closing ,0) - nvl(:cf_duty_on_goods_ed,0);
end;
Now i am using this total_creditFormula for the error formula column 'cenvat_opening'
function cenvat_openingFormula return Number is
f_num_opn number(14,2);
begin
SELECT NVL(Amount,0) INTO F_NUM_OPN
[code]...
here the formula column 'total_credit' has the following return statements;
RETURN Nvl(:cenvat_opening,0) + Nvl(:cenvat_credit_manu,0)
so how can i use this formula column? is there any other option to return the value in the same column.
I am trying to migrate a table to a new table that has the field sequence changed and also has a new field added. My main question is if it is possible to have datapump add values to the new field in the target table.For example:
-original table has fields a, b, d, c
-new table has fields b, c, d, a, e
I want to load the new table and also include adding values for field e. In this case, field e is a year field, so it should be loaded with '2012'..Does datapump have the ability to do this? Is reorganizing the fields going to cause me any problems? We are on oracle version 11.2.0.3
what is the role of SGA and PGA in oracle . And want kind of functionality they provide the oracle. i not understand what is system global Area and Program global Area (SGA and PGA).
View 7 Replies View RelatedIf iam create a new role in scott/tiger user why not creating. can any one explan me.
SQL> create role test_role;
create role test_role
*
ERROR at line 1: ORA-01031: insufficient privileges
I have a stored procedure which when executed creates a user and grants some roles to the user and also makes certain roles DEFAULT using "alter user"
The issue comes when i execute the procedure.
I have a User who has a role and this role has the "alter user" privilege, for example lets say that the user1 has the role ABC, and role ABC has the "alter user" privilege.
SQL> grant alter user to ABC;
Grant succeeded.
SQL> grant ABC to user1;
Grant succeeded.
Now, when i run the stored procedure as the user1, it gives me an error on a line saying "insufficient privileges", when i check the line, its this line:
alter user user1 DEFAULT ROLE "ROLE1", "ROLE2"
But as far as i know the user1 has the "alter user" privilege
I want to make those two roles ROLE1, ROLE2 DEFAULT because i don't want the other roles ROLE3,ROLE4 to be default, as you know if there are many roles and if we alter user with default for certain roles other roles become DEFAULT=NO.
So i get the error at "alter user" statement though the user has the "alter user" privilege, what do you think might be wrong?
OR is there anyway to grant roles to the user with default=NO option?
I have created a role, when i try to grant privilege to that role, it give me insufficient privilege error. After granting privileges to this role, i have to grant this role to other. what type of privileges should i have.
Create Role cb_select;
Grant select on atable to cb_select; (Got error)
I am trying to find out what is the minimum Oracle User Role required for the GetSchema command to work using ODP.NET and the Oracle.DataAccess library.
Dim cn As New OracleConnection(ConnectionString) 'Oracle.DataAccess.Client.OracleConnection Return cn.GetSchema("Tables")
