SQL & PL/SQL :: Is Select Privileges (grants) Needed For Created Synonym

Jun 4, 2012

After created synonym, Is select privileges(grants) needed for created synonym? SQL> create or replace synonym gm_holding for gmblbpna. gm_ holding;

Synonym created.

SQL> select *
2 from gm_holding;
from gm_holding
*
ERROR at line 2: ORA-00942: table or view does not exist

SQL> connect gmblbpna/gmblbpna@o03gpa0
Connected.
SQL> grant select on gm_holding to solvency;

Grant succeeded.

SQL> connect solvency/solvency@o03gpa0
Connected.
SQL> select count(1)
2 from gm_holding;
COUNT(1)
----------
346742
SQL>

why grant need for synonym?

View 4 Replies


ADVERTISEMENT

Grants And Privileges?

Jul 14, 2011

how to give grants and privileges that are assign to schema(clerk) to new schema(manager).

View 1 Replies View Related

Enterprise Manager :: What Privileges Needed To Run ASH Reports

Oct 12, 2010

What privileges do a DB user need to run an ASH report? The user logs into OEM and then as a different user into the DB (I can run the ASH, but Im logged in as SYSTEM).

The user is getting ORA-00904: invalid identifier.

View 3 Replies View Related

SQL & PL/SQL :: Error When Create Synonym / ORA-01031 / Insufficient Privileges

Mar 13, 2013

from sysdba i grant to my user hospital creat any synonym but give me this error why ?

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant create any synonym to hospital
2 ;

Grant succeeded.

SQL> conn hospital
Enter password:
Connected.
SQL> create public synonym Sur
2 for surgeries;
create public synonym Sur
*
ERROR at line 1:
ORA-01031: insufficient privileges

View 9 Replies View Related

Tablespace - Privileges To Newly Created User

Apr 13, 2012

i have created a new tablespace and assigned it to new user. What are the minimum no. of grants i should give, so that the user can made any kind of changes to all the objects within the tablespace and cant access other tablespace.

View 3 Replies View Related

PL/SQL :: Getting ORA-01031 / Insufficient Privileges Error When Created MV?

Jun 21, 2012

I am getting a strange error while trying to create an MV.

<b><p>
Error report:
SQL Error: ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 1613
ORA-06512: at line 1
01031. 00000 - "insufficient privileges"

*Cause:    An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if

[code]....

View 4 Replies View Related

Select Multiple Images Using Department Field In Spriden Table To Pull Needed Id Numbers

Jan 29, 2009

I have an Oracle 10g database, on the App Serv I have an image file that has 20,000 .jpg files that has an id number as each image name.I have successfully queryed the image file and posted one image to my web page matching the image id number.
sample:

select substr(spriden_last_name,1,20)||', '||
substr(spriden_first_name,1,20)||' '||
substr(spriden_mi,1,1) stname,
'<img src = "/images/&1..JPG" width="400" height="400"/>' pic
from spriden
where spriden_id = '&1'
/

the &1 is the matching id number that is input from the user.My task now is to select multiple images using a department field in the spriden table to pull the needed id numbers.I have not been successful in the proper format to pass the id number to the <img src field.

View 4 Replies View Related

SQL & PL/SQL :: Grant Privileges For Select On View From Different Schemes

Oct 27, 2011

My need is to grant priveleges for select on view without granting on nested tables.

Actual problem is that I can grant privileges for particular user, but can't grant privileges for user group.

View 4 Replies View Related

Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

have an automated process which runs on an Oracle 8i database server as user abc. This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 2 Replies View Related

SQL & PL/SQL :: Select All Users Created By Oracle?

May 4, 2012

During installation of a database Oracle creates several users. Also when using DBCA in a later stage to add more options Oracle can create more users.

Here's my problem: Public synonyms never get exported using exp(dp)/imp(dp) utility. Ofcourse using public synonyms isn't good practise, but sometimes handy. Unfortunately in our environment they have been used a lot in the passed.

So I want to create an SQL statement that does the trick and returns only all users that were NOT created by Oracle software.

I've tried several things, but can't seem to distinguish Oracle users from Manually created users.

View 7 Replies View Related

Schema Permissions - Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

I have an automated process which runs on an Oracle 8i database server as user abc.This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.

The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.

What grants/priviliges or whatever do I have to do to the abc schema?

View 1 Replies View Related

Server Administration :: Find When Database Role Created And Who Has Created?

Feb 14, 2013

Is there a way to find when was a database role created and who has created?

View 5 Replies View Related

REFRESH GROUP Is Automatically Created When A Materialized View Is Created?

Aug 17, 2012

tell me if a REFRESH GROUP is automatically created when a materialized view is created?

View 3 Replies View Related

Software Needed To Develop On Oracle Forms

Jun 13, 2011

I want to start doing some development on oracle forms. I tried to look at oracle.com products information and got some confusion. between midleware, application server , weblogic....

i have oracle 11g database installed and im quite familiar with oracle database any version.what im looking is for a answer on what product should i install to do forms.

View 1 Replies View Related

Server Utilities :: Sql Loader Value Needed From Another Table?

Aug 10, 2011

I have a file having 10k of rows and I need to use *sql loader to insert the data into table. Below are the information.

SQL> desc EMPLOYEE
Name Type
EMP_ID NUMBER(10) -- PrimaryKey
EMP_NAME VARCHAR2(30)
DEPT_ID NUMBER(10) -- ForeignKey from DEPARTMENT

SQL> desc DEPARTMENT
Name Type
DEPT_ID NUMBER(10)
DEPT_NAME VARCHAR2(30)

myFile.txt
------------
1,Edward,Account
2,Andrew,Finance
3,Sam, IT

CONTROL FILE (SQLLOADER)
------------
load data
infile myFile.txt
append into table EMPLOYEE
FIELDS TERMINATED BY ','
(EMP_ID,
EMP_NAME,
DEPT_ID ) <--- ?? What should do in here

what should i do in this line because the value that i want is DEPT_ID but the file is giving the DEPART_NAME. If there any sql statement can be used in control file?

View 6 Replies View Related

SQL & PL/SQL :: Is NOT NULL Needed If A CHECK Constraint Is On Column

Dec 13, 2010

In the below code, do I need the 'NOT NULL' after the 'state char(2)'? I am guessing that I do not need it since I have the CHECK constraint on the column.

CREATE TABLE employee(
id PRIMARY KEY,
first varchar(20) NOT NULL,
middle varchar(20),
[code]....

View 10 Replies View Related

Networking And Gateways :: Which Listener Is Needed To Access ASM Database

Mar 11, 2010

I am have installed oracle 11.2.0 in redhat linux 5.4. I have respectively 2 oracle homes. so total 2 listener files in 2 oracle homes. For accessing one database.Which listener i need to use ? Also if i am going to enterprise manager which listener file. I have to configure.

View 5 Replies View Related

Pl/sql Package For Giving Grants?

Mar 30, 2013

I am looking for a pl sql package which can give the following grants:

1. select_catalog_role
2 select any table

we want to run this package multiple names and each time we will give username as input during runtime.

View 1 Replies View Related

SQL & PL/SQL :: Filter The System Grants?

Jun 3, 2010

As per my req, i need to get the system grants of one user (GRANTEE) using DBMS_OUTPUT API. The requirement get completed using 'SYSTEM_GRANT' as parameter for OPEN function in the metadata api. Please look into part of code which works.

v_meta_handle := DBMS_METADATA.OPEN('SYSTEM_GRANT');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'GRANTEE','SCOTT');

When using above piece, i get sys grants granted to SCOTT user. But i need to use 'DATABASE_EXPORT' as a parameter to my 'OPEN' function.

v_meta_handle := DBMS_METADATA.OPEN('DATABASE_EXPORT');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'INCLUDE_PATH_EXPR','IN''SYSTEM_GRANT''');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'NAME_EXPR','IN''SCOTT''','GRANTEE_EXPR');

The second set filter doesn't work to get SYS GRANTS of one user. It does not throw any error. Simply the filter doesn't work. 'GRANTEE_EXPR' is not a correct value in the 2nd set filter. What parameter need to pass in object_path_expr ('GRANTEE_EXPR')?

View 4 Replies View Related

JDeveloper, Java & XML :: Grants In Oracle

Jul 22, 2011

whether JAVA grants are needed for JAVA services in Oracle and what are those grants ?

View 1 Replies View Related

SQL & PL/SQL :: View - How Grants Are Revoking Automatically

Nov 5, 2010

We have two schemas(sdt & sdm) in one database.

We have base tables in "sdt" ,
Views are created in "sdm" schema based on the "sdt" schema tables We have granted privileges on the base tables of "sdt" schema to "sdm" schema.

Sometimes views are not able to access by sdm schema.Again we are granted privileges on the base tables of "sdt" schemathen views are able to access.How grants are revoking automatically.

View 15 Replies View Related

SQL & PL/SQL :: Grants On Nested Table To Other User

Jul 16, 2011

I have an issue on selecting data from a nested table owned by other user.

I have given select access on stc_irb_usr.stc_loy_settlebillpay_map_main to anthr user adminofs.

But when I try to query, it says insufficient privileges. adminofs is able to select other columns which are not nested.

I have given "grant execute on STC_IRB_USR.STC_BILLPAY_MAP_TAB_TYPE to adminofs;" but it did not

select * from stc_irb_usr.stc_loy_settlebillpay_map_main where rownum<3;

ERROR: OCI-21700: object does not exist or is marked for delete

SQL> desc stc_irb_usr.stc_loy_settlebillpay_map_main
Name Null? Type
----------------------------------------- -------- ----------------------------
BATCH_NUM NOT NULL NUMBER
ACCOUNT_NUM NOT NULL VARCHAR2(40)
PAY_BILL_SEQ_MAP STC_IRB_USR.STC_BILLPAY_MAP_

[Code]....

Name Null? Type
----------------------------------------- -------- ----------------------------
ALLOCATING_CREDIT_SEQ NUMBER
ALLOCATION_ENTITY_SEQ NUMBER
ALLOCATING_CREDIT_DATE DATE
ALLOCATING_CREDIT_TYPE VARCHAR2(20)
ALLOCATION_ENTITY_DATE DATE

[Code]...

View 2 Replies View Related

Archive Old Data Which Has Stored Before 2012 And Can Be Restored For 2011 Or 2010 Needed?

Jan 29, 2013

I have a database in which two table contains millions of data and the whole database size is getting more bigger. is there any option to archive the old data which has stored before 2012 and can be restored when the data required for 2011 or 2010 needed.

View 4 Replies View Related

Application Express :: Any Prerequisites / Web Server That Is Needed In Order To Use Oracle Apex?

Nov 7, 2012

We have Oracle DB 11g.I want to develop a small application for internal use the company.I have used Oracle Application Express workspace provided by Oracle and I thought of using Oracle Apex is the right tool for our internal reporting.

But I am not sure what is needed in order to start developing the application in-house.What should I check with the DBA team in order to find out if I can use Oracle APEX in the organization.Is there any prerequisites, web Server, etc that is needed in order to use Oracle Apex?

View 4 Replies View Related

Security :: Automatic Generated Grants On Sys_plsql_xxxx_yy_z

Nov 13, 2012

in the dba_tab_privs we can find some entries like this:

SELECT *
FROM dba_tab_privs
WHERE GRANTEE IN ('XXXXXXXX')

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
XXXXXXXX YYYYYYYYYYY SYS_PLSQL_71964_26_1 YYYYYYYYYYY EXECUTE YES NO

Those grants were generated automatically by oracle. Is there any way to prevent Oracle to grant them? An external audit-rule tells us not to give any grant directly to user - we always have to use databaseroles.

View 5 Replies View Related

Server Utilities :: Impdp Getting Grants And Privs

May 8, 2012

I did an export using the following parfile (see below) I want to import all the objects associated with this schema into another DB but I want don't want to over-write any of the permissions such as grants.

Is there a way I can get the grants into a sql file before I do the import. If so, provide an example.

cat exp_par

DUMPFILE=exp.dmp
LOGFILE=exp.log
DIRECTORY=DBBACKUP
schemas=t1

View 1 Replies View Related

Security :: Given Grants Doesn't Reflect On Oracle Forms 6i

Mar 14, 2013

regarding grants given on object doesn't reflect on Oracle forms 6i.When my developer compiled the form the error throws object was not declared which means oracle form not able to access the object which is synonym of other table.Also we recreated the synonym and given grants from table owner and compiled form still throws same error,But i can able to select object from backend command but not from oracle forms.

grants reflect in oracle forms after we restart the database.

View 2 Replies View Related

Server Utilities :: Expdp Using Remap_schema Will It Also Remap Grants And Synonyms

May 1, 2010

If I would be using expdp using remap_schema will it also remap grants and synonyms ?

View 5 Replies View Related

Export/Import/SQL Loader :: Grants And Synonyms While Importing Through Data Pump

Jul 22, 2012

OS: RHEL
DB: 11.2.0.2

Every time i try to refresh my production DB with the a old expdp dumpfile using data pump i always face the issue of grants and creation of synonym. I would like to tell you that my DB has three schemas which have lots of dependencies among them and before refreshing them i drop the schemas and recreate the same.

Drop user user_name cascade;So i want to know, is there a script from which i can get all the grants of the DB before dropping the schemas, so that after import i can grant the same and also a query with which i will be able to get all the synonyms of the DB.

View 8 Replies View Related

How To Use Synonym In Stored Procedure

Aug 28, 2009

how to use a synonym in a stored procedure.

I have created a public synonym for a remote table on a different schema.

Im now trying to use the synonym to load that data into a temporary table in my schema using a stored procedure and im getting an error.

how to use a synonym in a stored procedure.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved