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
Jul 14, 2011
how to give grants and privileges that are assign to schema(clerk) to new schema(manager).
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
Feb 14, 2013
Is there a way to find when was a database role created and who has created?
View 5 Replies
View Related
Aug 17, 2012
tell me if a REFRESH GROUP is automatically created when a materialized view is created?
View 3 Replies
View Related
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
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
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
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
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
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
Jul 22, 2011
whether JAVA grants are needed for JAVA services in Oracle and what are those grants ?
View 1 Replies
View Related
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
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
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
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
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
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
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
May 1, 2010
If I would be using expdp using remap_schema will it also remap grants and synonyms ?
View 5 Replies
View Related
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
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