Next: Privileges, Roles and Profiles Up: Users and Permissions Previous: Users and Passwords Contents

Adding New Users

If you really poke around with Oracle you'll find that the sys account is blocked from a variety of tasks therefore we need to create some users. The easiest way to add users is with the Enterprise Manager, if you can use it. The following is an example of adding a use using SQL:

bash-2.05$ sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 8 11:19:42 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> connect sys/passwd as sysdba;
Connected.
SQL> create user ben profile default identified by passwd                          
  2  default tablespace users
  3  temporary tablespace temp
  4  account unlock;

User created.

SQL> grant connect to ben;

Grant succeeded.

SQL> alter user ben quota unlimited on users;

User altered.

SQL>

So here, as SYSDBA we've created the user "ben" with the default profile identified by the password "passwd". The users default tablespace is users and temp tablespace is temp and the account is unlocked. In the second statement we grant the "connect" role to user ben. And in the third statement we alter the quota on the users tablespace by user ben. Now lets login with the user...

bash-2.05$ sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 8 11:24:11 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> connect ben/passwd
Connected.
SQL> create table mytable (
  2  id number(2),
  3  name varchar2(30)
  4  );
create table mytable (
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
SQL>

Wait? Even though the user is added the user has no privs? This is indeed lame. The user wasn't defined with any roles. Each user must have one or more security role assigned to them. You'll notice that even though in the creation of the user our second SQL statement was to grant the "CONNECT" role to the user, but that wasn't enough. By default in 10g a new user has no privs at all, so we need to just keep piling on the roles. Lets spend a minute sorting out the diffrence between privileges, roles, and profiles.

Next: Privileges, Roles and Profiles Up: Users and Permissions Previous: Users and Passwords Contents

2005-02-10