SQL & PL/SQL :: How To Get Output Without SYSDBA Privilege

Mar 2, 2010

A function returns the metadata of named objects (Directories, Users, Tablespace....) in the form of DDL. When i execute the function in the schema having the privileges of CONNECT, RESOURCE, DBA, SELECT ANY TABLE, UNDER ANY VIEW AND EXECUTE ANY PROCEDURE, function returns the empty clob without any error. But he same function created and executed in the User having SYSDBA privilege, we get output.how to get output without SYSDBA privilege ?

CREATE OR REPLACE FUNCTION SCHEMA.DBLINK
RETURN CLOB
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;
V_LOB CLOB;
[code]....

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Grant Sysdba Privilege To User1

Oct 8, 2010

I grant sysdba privilege to user1. After that i connected with user1. But i could not shutdown the database.

View 3 Replies View Related

PL/SQL :: How To Grant Privilege On User1 Based On Privilege Of User2

Jun 20, 2013

I have 2 users in my oracle DB. They both have very different privileges and they both have too many different privileges. Now I want to grant user 1 the same privilege that user2 has while user1 keeps his existing privilege. How can this be done without manually comparing their difference and manually grant user 1 each privilege that he doesn't have? Or second option, can I override user 1's privilege with user 2's privileges?

View 2 Replies View Related

SQL & PL/SQL :: Direct Privilege Vs Privilege Through Roles

May 30, 2010

As we all know, privileges granted to a user through role are not visible from within a stored procedure. What is the reasoning behind this design?

Moreover, privileges granted through role are visible from anonymous PL/SQL block. Why such discrimination between anonymous PL/SQL and stored PL/SQL?

View 3 Replies View Related

Got Ora-12528 While Connecting To Sys As Sysdba?

May 18, 2010

I got ORA-12528 while i was connecting sys as sysdba from remote machine to theinstance which is started in nomount mode. Although tnsping for that instance is working.

View 2 Replies View Related

How To Restrict Sqlplus As Sysdba

Apr 29, 2013

how to restrict sqlplus as sysdba

C:> Set Oracle_sid=Mydb
C:>sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

I don't want to log in any one as sqlplus / as sysdba.If they can able to login as sqlplus / as sysdba then they can see others schema in the database.Say if i set

SQLNET.AUTHENTICATION_SERVICES=none

no one can log in as sysdba were as later some one changes to

SQLNET.AUTHENTICATION_SERVICES=NTS
After they changes to NTS they can log in and access other user details right ? how to restrict

Oracle version: Oracle 11 g
OS Version :Win 7

View 21 Replies View Related

Can't Login Into Sqlplus Even As A Sysdba

Sep 7, 2013

"C:UsersKarthikeyan>sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sat Sep 7 17:42:37 2013 Copyright (c) 1982, 2013, Oracle.  All rights reserved. ERROR:ORA-12560: TNS:protocol adapter error  Enter user-name:" 

this is the first time i tried to open sql*plus and i didn't create any database connection before....so i don't have any username and password.......

View 20 Replies View Related

PCI - Auditing Connect / As Sysdba - Connections?

Feb 15, 2012

I have a problem with a PCI DSS - requirement in Oracle 11.2. (PCI DSS = Payment Card Industry Data Security Standard)

Problem:

we connect via ' ssh -2 -X -l oracle hostname ' to the databaseserver and become os-user 'oracle'. we have also two offshore locations with dba's and each dba comes with his personalized user to the jumphost and then with the above ssh command to the database server.

the problem is that each dba becomes the oracle-os-account and can now connect with '/ as sysdba' to the database.in pci-dss this is not allowed !

now my question:how can I audit these '/ as sysdba'-connections and prove which user connected at which time with the '/ as sysdba' command ?

database is in audit mode. we log to syslog on linus redhat 5. I know one solution could be setting "SQLNET.AUTHENTICATION_SERVICES" parameter to "NONE" in sqlnet.ora file will make it not possible to connect to the database without a password as sysdba. (sqlplus / as sysdba). but we have to many applications and jobs and this is not really the solution in this case.

I think I can only solve this problem with personalized OS-user DBA-accounts in the dba-goup on os-site and os-user oracle should not be used for the future ?? I also need personalized dba-user-accounts in the database. using sys and system is not allowed. this users has to be locked and only for special administration work could it be unlocked.

View 3 Replies View Related

Security :: Unable To Logon As Sysdba?

Mar 3, 2005

I am working on oracle 9.0.1 version on XP.

Here I am unable to log as sysdba

I am doing like this

d:sqlplus /nolog
sql>conn /as sysdba

not working,giving an error then i tried

sql>conn sys as sysdba
again same error :_ insufficent Priv..

where as sql> conn system/manager working

View 21 Replies View Related

Username While Connecting To Sqlplus /as Sysdba

Mar 13, 2013

I am in a bit confusion about the user when we are logging in to sqlplus /as sysdba. what the user is when we are into sqlplus with sqlplus /as sysdba.

View 3 Replies View Related

Client Tools :: Sysdba Access From Sqlplus?

Feb 8, 2009

I used the following command :
-------------------------------------------------
D:oracleproduct10.1.0Db_2jdkinjava -Djava.security.properties=D:oracleproduct10.1.0Db_2sqlplusadminiplusprovider -jar D:oracleproduct10.1.0Db_2oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -shell
--------------------------------------------------------

Output as follows:
--------------------------------------------
oracle.security.jazn.JAZNRuntimeException: Configuration file "configjazn.xml" does not exist. Check your JAAS configuration settings.at oracle.security.jazn.JAZNConfig.getJAZNProperties(Unknown Source)
... ... ...
Realm [iSQL*Plus DBA] does not exist in system.
-----------------------------------------------

what may be the reason for this error?

View 8 Replies View Related

ORA-00257 - Archives Error And Impossible To Connect As Sysdba

Apr 8, 2013

I have a problem with my Oracle istance and so far I couldn't fix it, I have and Oracle 11g XE istance running on windows server 2003.A couple of days ago I received the error message "ORA-00257: archiver error".I found tips in this forum and wanted to apply but sqlplus doesn't recognize my SYS user/password. I type the correct password but no chance to log in.

If I try to connect using a client like Toad, I receive the message ORA-12170: TNS:Connect timeout occurred. I checked the firewall rules, but it's ok, moreover I was logging in in the past and any change has been performed on it. I learned it's possible to change SYS password.

If I connect as "/ as sysdba" on sqlplus I read "Connected to an idle istance".In order to connect as "/" I had to update sqlnet.ora and restart the service. The parameter SQLNET.AUTHENTICATION_SERVICES

was set as NONE ad I set as NTS. I should probably have to set it as ALL? I found out the orapwd utility to change SYS password throught password file for remote login as sysdba.

I tried to create that file, and when I ran the command it asked me the SYS password, I typed it and it created the file, so I guess the password I type is correct (I had the question to type it wrong). I didn't go to the next step as I wasn't sure to make the right thing and I don't want to make this issue bigger.

As long as I can't log in as sysdba I can't perform the actions mentioned in tips to fix the error 257, but I'm also wondering if the archive error can have a side effect and deny the action of update the SYS password.

View 2 Replies View Related

Server Administration :: Insufficient Privileges While Connecting (sys As Sysdba)

Apr 2, 2013

SQL> conn sys as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges

The oracle 11g installed in eucalyptus cloud..

In the same server I can connect as a different user 'd6' but not as sysdba.

View 7 Replies View Related

Client Tools :: Can Login As SYS As SYSDBA Via TOAD But Not Via SQLPlus

Jun 21, 2013

I try to login as SYS@sid AS SYSDBA When I login via TOAD, I am able to. However, as in (1), all my attempts to login from command-line SQL *Plus fails. The error I get is ORA-1031 Insufficient Privileges Even if I am on the physical server and try to run the SQL *Plus, I get the same error -- Insufficient Privileges

Here are the environment details:

Server: Windows Server 2008 R2
Server: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Client: Windows 7 Professional
Client: SQL*Plus: Release 11.2.0.1.0 Production

View 2 Replies View Related

SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output

Jul 1, 2013

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......

this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.

View 2 Replies View Related

Forms :: Login As SYSDBA In Oracle From Normal Logon Procedure?

Apr 24, 2012

is it possible to login as SYSDBA in oracle forms from normal logon procedure?

logon(sys_acc, sys_pwd||'@'||:GLOBAL.db_conn);

The reason behind this is to make a form which will alter user's password. But all the users are in SYS account and it is necessary to login as SYSDBA in forms to execute alter statement.

View 4 Replies View Related

SQL & PL/SQL :: Grant Privilege To Role

Jun 3, 2010

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?

View 9 Replies View Related

SQL & PL/SQL :: Granting Privilege To A Role

Aug 18, 2010

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)

View 7 Replies View Related

SQL & PL/SQL :: User Scott Having DBA Privilege

Oct 14, 2011

User Scott having DBA privilege

create or replace procedure pt is
l_count integer:=0;
v_sid varchar2(1000);
begin
SELECT name
INTO v_sid
FROM v$database;
dbms_output.put_line('SID='||v_sid);
end pt;

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE PT:

LINE/COL ERROR
-------- -----------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
7/9 PL/SQL: ORA-00942: table or view does not exist
SQL>

SQL> select name from v$database;
NAME
---------
ORCL

I can't access v$parameter,or v$database from a procedure?

View 1 Replies View Related

PL/SQL :: How To Grant Privilege In DBA_TAB_PRIVS

Apr 2, 2013

I logged in as system and I just execute below script, however Im getting error "SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist""

GRANT execute ON UTL_FILE TO USER1;

How to grant privilege on ult_file?

View 5 Replies View Related

SQL & PL/SQL :: Execute Privilege On A Package Which Is In User

Dec 5, 2011

how will i know if i have execute privilege on a package which is in user?

View 6 Replies View Related

SQL & PL/SQL :: Privilege - Just Drop Special One Table?

Jul 3, 2013

There two users a and b,and the table b.test_part.And one procedure under a ,text like below:

create or replace procedure a.sp_test
is
vs_sqls varchar2(32767);
begin
vs_sqls:='alter table b.test_part truncate partition p_day';
execute immediate vs_sqls;
end;

now,i have to grant drop any table to a.but in fact,i prefer to drop the special one table "b.test_partany" rather than any other table.how ? no by trigger!

View 5 Replies View Related

Server Administration :: Insufficient Privilege?

Mar 10, 2011

When i want to connect inro database by Following:

# sqlplus /nolog
sql>conn / as sysdba

Insufficient privilege.

What are the reasons.

View 10 Replies View Related

Insufficient Privilege From Schema Own View?

Jul 19, 2013

I am not able to select from the VIEW while connecting with the Schema who is the owner itself: 

FX@db > select * from B_UTIL
select * from B_UTIL
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-02063: preceding line from FXDB2TST
Describe works but select does not. Even I connected with SYSDBA and performed SELECT * FROM FX.B_UTL but it again gives the above errors. 11.2.0.2 on RHL.

View 18 Replies View Related

Security :: How To Know What Object Privilege A User Have

Nov 13, 2013

How to know what object/tab privileged a user have ? without connecting that user and using USER_TAB_PRIVS by connecting on that user I mean on DBA table .

View 0 Replies View Related

How To Grant Privilege For A Specific Function

Jun 21, 2012

I wonder if exists a privilege, that i could grant to a user, just to run a specific function.

I searched in dba_sys_privs something about it but, returned nothing.

13:38:10 brunos@fastora1> select * from dba_sys_privs where privilege like '%FUNCTION%';

GRANTEE PRIVILEGE ADMIN_OPTION

View 7 Replies View Related

SQL & PL/SQL :: Splitting 1 Output Row Into 3 Output Rows

Sep 25, 2013

Currently I have a requirement where I need to create 2 more output rows using each result row.

In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.

--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);

[Code]....

My expected output should be like below:

Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40

how I can achieve the expected output using a single sql query

View 6 Replies View Related

Performance Tuning :: Privilege To Run Explain Plan

May 24, 2012

what privilege is require for a user to execute explain plan? I get below error while try to execute explain plan.

SQL> explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000;
explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000
*
ERROR at line 1:
ORA-01031: insufficient privileges

View 9 Replies View Related

Enterprise Manager :: OEM And Privilege To See Users Schemas

Apr 19, 2010

I connect to an Oracle-10g database using OEM, and if I click to see the list of users, roles, storage options, ... everything is ok, but if I click to see the list of schemas, I get "1031: insufficient privileges" error.

The user account I use has the SELECT_ANY_DICTIONARY privilege and SELECT_CATALOG_ROLE role granted, and if I try to do "select * from dba_tables" in the SQL*Plus, I get a result.

The matter is even more strange because, if I use the SYSTEM account, I get the same error (!!!).

Do I need any other privilege/role to see the user's schemas with OEM?

View 2 Replies View Related

SQL & PL/SQL :: How To Check If User Has (create Table) Privilege

Jun 29, 2011

1.How to check a user has 'create table' privilege?

2.how to check a user has privilege to grant 'create table' privilege to other user ?

View 6 Replies View Related







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