How do I restrict internal account access to database users? I created a user called user1 and granted him connect and resource privileges. Now after I connect as user1, I tried 'connect internal' and I get connected to the database as 'SYS' which is dangerous. How can I restrict this such that when user1 tries to 'connect internal' it will prompt for the password. Please help.
> How do I restrict internal account access to database users? I created > a user called user1 and granted him connect and resource privileges. > Now after I connect as user1, I tried 'connect internal' and I get > connected to the database as 'SYS' which is dangerous. How can I > restrict this such that when user1 tries to 'connect internal' it will > prompt for the password. Please help.
> Thanks in advance.
> jiju
The only people who can connect internal (or "as sysdba") without a password are those in the operating system's DBA group. Remove non-dba users from the DBA group.
The OS user should not be in an operating system group which was set as SYSDBA or SYSOPER group duing Oracle software installation/linking time. Usually that means that, remove your non-privileged user from dba group.
Hm, I noticed you didn't specify on which platform you're running. The above was for Unix, if on windows, then remove the user from ORA_DBA or ORA_<SID>_DBA group.
> How do I restrict internal account access to database users? I created > a user called user1 and granted him connect and resource privileges. > Now after I connect as user1, I tried 'connect internal' and I get > connected to the database as 'SYS' which is dangerous. How can I > restrict this such that when user1 tries to 'connect internal' it will > prompt for the password. Please help.
On 11 Aug 2003 09:15:07 -0700, jiju <jmkuruvi...@yahoo.com> wrote:
> hello,
> How do I restrict internal account access to database users? I created > a user called user1 and granted him connect and resource privileges. > Now after I connect as user1, I tried 'connect internal' and I get > connected to the database as 'SYS' which is dangerous. How can I > restrict this such that when user1 tries to 'connect internal' it will > prompt for the password. Please help.
> Thanks in advance.
> jiju
You've got good replies from others: you've inadvertently set up operating system authentication for privileged users, and that's how come your "ordinary" user is connecting internal. Remove the user from the relavant O/S group, and he won't be able to do that. If you want to abandon operating system authentication altogether, then you'll need to use the orapwd utility to create a new password file, and edit the init.ora so that REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE.
But a couple of other points: you should never grant connect and resource to a user. Those are not "privileges", they are *roles*. And the key feature of a role is that it is a *collection* of privileges, and that means you've granted an awful lot more to that user than you might expect. For new users, just grant 'create session', and then specific privileges as required ('select any table', for example. Or specific objecty privileges such as 'select on emp').
Lastly, you don't mention an operating system or a version (two essential nuggets of information). But if this is any Oracle version higher than or equal to 8.0, then you shouldn't be connecting as internal anyway. Internal is dead as of 9i, and so you'd want to not get into bad habits. Connect "sys/something AS SYSDBA" is the new, approved way of doing the same thing, and it has a future.
> How do I restrict internal account access to database users? I created > a user called user1 and granted him connect and resource privileges. > Now after I connect as user1, I tried 'connect internal' and I get > connected to the database as 'SYS' which is dangerous. How can I > restrict this such that when user1 tries to 'connect internal' it will > prompt for the password. Please help.
> Thanks in advance.
> jiju
Log on as SYS and perform the following:
DROP ROLE connect; DROP ROLE resource; DROP ROLE dba;
That is what Oracle recommends.
Now create specific roles that relate to what people actually do and assign those to your users.
I am using Oracle 8.1.7 on Windows 2000 server. Also, I am not using OS authentication. But I guess like Quarkman said, I shouldn't grant connect and resource roles to a user. May be I should create another role with specific privileges and grant that role??
> > How do I restrict internal account access to database users? I created > > a user called user1 and granted him connect and resource privileges. > > Now after I connect as user1, I tried 'connect internal' and I get > > connected to the database as 'SYS' which is dangerous. How can I > > restrict this such that when user1 tries to 'connect internal' it will > > prompt for the password. Please help.
> > Thanks in advance.
> > jiju
> Log on as SYS and perform the following:
> DROP ROLE connect; > DROP ROLE resource; > DROP ROLE dba;
> That is what Oracle recommends.
> Now create specific roles that relate to what people actually do and > assign those to your users.
jiju wrote: > I am using Oracle 8.1.7 on Windows 2000 server. Also, I am not using > OS authentication. But I guess like Quarkman said, I shouldn't grant > connect and resource roles to a user. May be I should create another > role with specific privileges and grant that role??
> > > How do I restrict internal account access to database users? I created > > > a user called user1 and granted him connect and resource privileges. > > > Now after I connect as user1, I tried 'connect internal' and I get > > > connected to the database as 'SYS' which is dangerous. How can I > > > restrict this such that when user1 tries to 'connect internal' it will > > > prompt for the password. Please help.
> > > Thanks in advance.
> > > jiju
> > Log on as SYS and perform the following:
> > DROP ROLE connect; > > DROP ROLE resource; > > DROP ROLE dba;
> > That is what Oracle recommends.
> > Now create specific roles that relate to what people actually do and > > assign those to your users.
