I have a schema TEST1 and it has a view 'TEST_VIEW'. I granted read only permission on the view to TEST2 schema. grant select on TEST_VIEW to TEST2.
But TEST2 is used by an application which requires to read view metadata and then sync with application afterwords start reading. Is there any special permission to grant to access metadata of the view / table.
I have an Index in User1 schema. can i grant access on this Index to User2. if so, what privs i can give..?
for proc's we will give "Grant Execute on proce1 to User2". Like this how on Indexes..
I would like to know what happens when we fire:
grant all on any table to user_name;
and
grant all on table_name to user_name;
Actually I was performing "grant all on table_name to user_name;" to grant the privilege but 1 of my friends suggested "grant all on any table to user_name;"
I have a role in my Oracle 10g instance like below:
GRANT ALTER USER TO <role_name> WITH ADMIN OPTION;
And this works fine for any user who has:
GRANT <role_name> TO <user>;
What I need is to limit <user> to only have the ALTER USER privilege to a set of users. Preferrably where the set of users are identified by a column value in a table, something like:
WHERE PeopleTable.InList = "YES"
Or maybe where set of users are defined by their membership in another role.
GRANT ALTER USER TO <role_name> FOR USERS IN MEMBERS_LIST_ROLE;
Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
if a user have alter table gant but could not alter .. what additional grant it need
SQL> alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3));
alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3))
*
ERROR at line 1:
ORA-00942: table or view does not exist
I have two user a and b. I have to Grant select update insert Delete Permission to all tables of user b To User a. how can it possible?
View 5 Replies View RelatedI want to grant a privilege through an insert statement into a sys table.Why do not grant the privilege through the classic way : grant select on t to l_user; ?
Because I want to do it remotely.I am connected to db1.I want to grant select on t2 to u2_b from u2_a.I assume that all DDL are DML. So a grant is equivalent "somewhere" to an insert.I tried to do my requirement locally, and here is the output.
SQL> conn scott/aa
Connecté.
SQL> -- step 1 : try to grant "normally" a select on dept to hr from scott
SQL> grant select on dept to hr;
Autorisation de privilèges (GRANT) acceptée.
SQL>
SQL> conn sys/a as sysdba
Connecté.
SQL> -- step 2 : Then, we connect to sys to see the row inserted in dba_tab_privs
SQL>
SQL> col GRANTEE format A10
SQL> col OWNER format A10
[code]...
Then if I can do it locally, I can do it remotely through a db link.
The SELECT at the end of this script fails with "ORA-00942: table or view does not exist". I think I added proper permission via GRANT command.
create table mudd_table (
a number,
b varchar2(10)
);
create user mudd_user identified by mudd_user;
grant select, insert, update, delete on mudd_table to mudd_user;
grant create session to mudd_user;
conn mudd_user/mudd_user@quadoracle;
select * from mudd_table;
i need to grant a user to see all table into another table and not to use this option (grant any table)
View 9 Replies View RelatedWhen I invoke SOAP Web-Service using APEX_WEB_SERVICE.MAKE_REQUEST, then I'm able to get response from web-service. However all German character are replaced by JUNK data. However data is coming fine when I test web-services using SOAP UI.
I tried to invoke web-service using UTL_HTTP. However when I use UTL_HTTP, then I'm getting following error.
ORA-24247: network access denied by access control list
<li> Why German characters are replaced by Junk data while invoking web-service from APEX, while it's working fine from SOAP UI
<li> When I can access web-service successfully using APEX_WEB_SERVICE, then why it's throwing ORA-24247 error when I call using UTL_HTTP?
DB: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
APEX: 4.0.2.00.07
Web-Server: EPG
SELECT * FROM NLS_DATABASE_PARAMETERS;
[code]...
I am having oracle database version 11.2.0.3.0 standard edition, where one of my users requirement is that he wanted to send mails from oracle database but he is getting below error
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "HF_REPORTING.SEND_MAIL", line 12
ORA-06512: at line 18
is it possible to send mails from oracle database in STANDARD Edition?
While Running the proc,it's raising the below error.
Error report:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "LISTER_SHIFT.SOLN_GENERIC_SENDMAIL", line 33
ORA-06512: at line 1
[code]....
i have query lie below.
select
(SELECT MIC.MICR_BANK_NAME FROM M_ECS_MICR_MST MIC WHERE MIC.PK_MICR_ID = BANK.ECS_MICR_CODE) AS BANKNAME,
, (SELECT PAY.PAYMENTCODE FROM M_ECS_PAYMENT_MODE PAY WHERE TO_CHAR(PAY.ID) = BANK.ECS_FACTORING_HOUSE) AS
[code].....
i have used composite index below column which used in the tbl_bank_statement table.like column name tbl_bank_ statement (policy_ no,ecs_ micr_code,ecs_factoring_house,ecs_mandate_status)
but still this table giving me TABLE ACCESS FULL.
I am using Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production.I have a table D_GEOGRAPHY_25729 in RDS schema and I have created a role ACC_D_TABLES logging as ADMIN.
Now I have given GRANT SELECT access for table D_GEOGRAPHY_25729 to role ACC_D_TABLES.
There is another schema TRANS in the same database which has the role ACC_D_TABLES. But after logging in this schema, I am not able to query directly from table D_GEOGRAPHY_25729. I need to use like below to access the table.
select * from RDS.D_GEOGRAPHY_25729 Having the correct role ACC_D_TABLES to TRANS schema, why should I use RDS.D_GEOGRAPHY_25729 ? Is it not possible without prefixing the RDS schema name?
It is possible when I create a public synonym for D_GEOGRAPHY_25729 in RDS schema, but if i do so everyone can access this table.
I am working in all 3 environments: development , Test and Production. How can we access the tables created in one environment in the other environment. Also, request to share the SQL code for the same.
I am using Oracle Sql Developer tool.
How can we access a table from one database to another database on the same server.
Let us say One database is "A" and the Other database is "B" both databases are running on one server "ABCD".
My question is how can we access a table from database B to database A.
is it possible to track the last access to a table?
View 2 Replies View RelatedI am an auditor. I need to ensure that a perticular user at any cost should not be able to update contents of a table.
Privileges granted:
GRANT SELECT ANY TABLE TO "ABC"
GRANT ALTER SESSION TO "ABC"
GRANT CREATE SESSION TO "ABC"
Can abc modify, alter contents of a table?
How to get the data access for a temp table ?
View 5 Replies View RelatedOracle 11gR2
I have on table 'Test' in database and I want to know currently which sql statements are being fired on table 'Test' ?
Script for test:
CREATE TABLE TEST(empno VARCHAR2(4), empname VARCHAR2(50), empstd NUMBER(2))
insert into test values(0001,'A',2);
insert into test values(0002,'B',5);
insert into test values(0003,'C',2);
insert into test values(0004,'D',7);
insert into test values(0005,'E',9);
Now I want to get empno for the particular employees based upon subscript and I have written below
DECLARE
CURSOR cur_rec
IS
SELECT *
FROM TEST;
TYPE cur_type IS TABLE OF cur_rec%ROWTYPE;
v_cur_rec cur_type;
BEGIN
OPEN cur_rec;
FETCH cur_rec
BULK COLLECT INTO v_cur_rec;
[code]....
how to access individial item froma table of rowtype data.
CM: Added [code] tags, please do so yourself next time, see the orafaq forum.
I am trying to access and modify data of a table of another schema which contains 80,000-90,000 records. My procedure is taking near about 30 mins to complete the operation. faster access and updation of table data.
Details:
I have two schema: TEST and PROD
I am running the below code from TEST Schema.
/* CODE START HERE*/
DECLARE
exc_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_bulk_errors, -24381);
v_block_count NUMBER := 1000;
[Code]....
The above code is taking near about 30mins to process.
I have also tried another approch: Creating a procedure in PROD schema to update COMPONENT_MASTER table and by calling the procedure from above code by passing component code.
/* PROCEDURE CALL FROM ABOCE CODE INTEST SCHEMA*/
PROD.PROCEDURE_TO_UPDATE(v_comp_code);
But still it is taking same time.
Following are 2 queries, which return same results as far as the input parameter is NOT NULL
select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id);
select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id);
However the execution plan differs a lot The FTS cost or rows accessed count also varies what could be the reason? The PX_CJQ is simply select * from dba_objects and has index on object_id which anyway is not being used in this case
variable x number
exec :x:= 28
Query - 1
***************
***************
SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id);
SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
[code]...
Query - 2
***************
***************
SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id);
SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
[code]...
29 rows selected.
SQL> >
I have a Database that has a frontend in MS Access and a backend in Oracle. When I try to open the Oracle Table/s in the MS Access Frontend, it gives me this error message:
Reserved error (-7711); there is no message for this error.
There is some stuff on the internet about this error but none of it fixes my issue. It seems to be an MS Access error rather than an Oracle error. Only thing is this issue is only on Machines that are running on XP. I have no problem on mine which is Windows 7.
I configured a simple security configuration for the HR sample database schema. URL....
Now system user can't select data from table hr.employee, but HR user still can. How to restrict access to table for table owner?
I've a query like
update tab1
set col1 = ( select col2 from
tab2
where tab1.id = tab2.id) table 1 has arnd 10,000 rows
table 2 has arnd 1,700,000 rows and has a primary key on column id.This query is taking around 20 secs to execute. I checked the x-plan and most of time taken for table access by index rowid.I checked the stats for the tab2, its just three days old.
Our oracle database has been running great. This past wendesday the application was unable to access a specific table. Sutting down and restarting the database seemed to have fixed the issue. Last night the database has been working since that restart on wednesday morning. Suddenly stopped working. We stopped and restarted the database again this time getting ORA-00600 error as shown below. We are able to make a connection to the database and can even access the tables in question. The application is complaining about the connection to the database. I understand that ORA-00600 is an internal error what I need to know is what the internal error code 549 actually is. I have tried googling to no avail.
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 3 09:47:20 2010
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
ORA-00600: internal error code, arguments: [549], [], [], [], [], [], [], []
ORA-03113: end-of-file on communication channel
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\Documents and Settings\tieoduser>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 3 09:49:31 2010
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
[code]....
Need to access data in a table base on user parameter input where the data is stored like a spreadsheet with column headings JAN, FEB, MAR... and the rows are the years. Is there a way to create a generic SQL statement so that I don't have to have 12 if statements in the procedure?
View 5 Replies View Related