| Insufficient Privileges [message #111325] |
Tue, 15 March 2005 11:51  |
snagra
Messages: 23
Registered: March 2005
|
Junior Member |
|
|
I find that the only way i can create a table is by connect internal and then doing it. But when I look a the table logged in as a user I get insufficient privileges. I cannot grant sysdba privileges unless I connect internal. but they are not permanent as they are lost when I log out.
I've tried this below
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
SQL> desc pt2v01m
ERROR:
ORA-04043: object pt2v01m does not exist
SQL> connect internal/
Password > *******
Connected.
SQL> desc pt2v01m
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 – Production
$ sqlplus flinksal
Password ******
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Mar 15 15:56:50 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> grant sysdba to flinksal;
grant sysdba to flinksal
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> connect internal
Password ******
Connected.
SQL> grant sysdba to flinksal;
Grant succeeded.
SQL> commit
2 ;
Commit complete.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 – Production
$ sqlplus flinksal
Password ******
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Mar 15 16:00:31 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> start pt2v01m.sql
create table PT2V01M
*
ERROR at line 1:
ORA-01031: insufficient privileges
|
|
|
|
|
|
|
|
| Re: Insufficient Privileges [message #112751 is a reply to message #111325] |
Tue, 29 March 2005 02:00  |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
when we create new user to whom we grant privileges as sysdba that dosent mean that the table created by user sys who is actually a DBA will be accessed by newly created user having privilege of DBA coz sys itself is a user but having privilege of DBA (sys is owner of datadictionary) ,so we are granting privileges of DBA to newly user. We are not granting select privilege on Table created by Sys to newly created user. so you have to give grant privilege....
below are the step of your purpose
Enter user-name: sys as sysdba
Enter password:
note i m working on oracle 10g so its sys as sysdba but for 8i u must use internal
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production
now on SQL prompt as Sysdba or internal
SQL> create user sunil identified by sunil default tablespace system;
User created.
SQL> grant sysdba to sunil;
Grant succeeded.
SQL> connect sunil
Enter password:
ERROR:
ORA-01045: user SUNIL lacks CREATE SESSION privilege; logon denied
so now give give privilege to sunil for create session
thank you
yours failthfully
sunil
SQL> create user sunil indentified by sunil default tablespace sunil;
create user sunil indentified by sunil default tablespace sunil
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> creat user sunil identified by sunil default tablespace system;
SP2-0734: unknown command beginning "creat user..." - rest of line ignored.
SQL> create user sunil identified by sunil default tablespace system;
User created.
SQL> grant sysdba to sunil;
Grant succeeded.
SQL> connect sunil
Enter password:
ERROR:
ORA-01045: user SUNIL lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant create session to sunil;
Grant succeeded.
you can check this now
SQL> connect sunil
Enter password:
Connected.
now create table by connecting as sys to database
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create table aa (no number);
Table created.
SQL> select * from aa;
no rows selected
SQL> grant select on aa to sunil;
Grant succeeded.
SQL> connect sunil
Enter password:
Connected.
SQL> select * from sys.aa;
no rows selected
now your purpose is solved here
--------------------------------
but please remove the table aa and user sunil coz sunil is user and using system tablespace ... and sys create table aa in system tablespace, system tablespace should not contain user data ...coz it stores data dictionary.
-----------------------------
so do following step as below now
-----------------------------
SQL> connect sys as sysdba;
Enter password:
Connected.
SQL> drop user sunil;
User dropped.
SQL> drop table aa;
Table dropped.
--------------------------------------------
EVerything is working on my PC correctly ....
regard
sunil
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
