Create user

Create user [Oracle SQL]
The (Oracle) SQL statement create user creates a user.
In the most simple form, the create user statement is one of the following three:

create user alfredo identified by alfredos_secret;
create user alfredo identified externally;
create user alfredo identified globally as 'external_name';

The first one creates a local user, the second one creates an external user while the last one creates global user.

Default tablespaces
When a user is created, his default tablespace as well as his temporary tablespace can be specified.

create user alfredo
  identified by alfredos_secret
  default tablespace ts_users
  temporary tablespace ts_temp;

Locked users
A user can be created locked, that is, the user cannot connect to the database.

SQL> create user alfredo identified by passw0rd account lock;

The user is now created, he can be granted some rights, for example the right to connect to the database:

SQL> grant connect to alfredo;

Now, if the user tries to connect to the database, he will get ORA-28000:

SQL> connect alfredo/passw0rd
ERROR:
ORA-28000: the account is locked

The user can now be unlocked with an alter user statement:

SQL> alter user alfredo account unlock;

Which allows Alfredo to log on to the database now:

SQL> connect alfredo/passw0rd
Connected.

Expiring password
A user can be created such that he needs to change his password when he logs on. This is achieved with the password expire option.

SQL> create user dilbert identified by tie password expire;

Now, Dilbert connecting:

SQL> connect dilbert/tie
ERROR:
ORA-28001: the password has expired

Changing password for dilbert
New password:

Assigning profiles
A user can be assigned a profile when (s)he is created.

create user berta profile appl_profile

The profile being assigned must be created.

Displaying existing users
The dba_users view shows already created users.

Restrictions on passwords
The following restrictions apply to a password:

   * Its length must be between 1 and 30 characters
   * They are case insensitive
   * The only characters allowed are A-Z (a-z), 0-9, the underscore (_), the dollar sign ($), the hash symbol (#).
   * The first character must be one of A-Z or 0-9.
   * The password must not be a reserved oracle word (see v$reserved_words).

Public role
When a user is created, the role public is automatically assigned to this user. However, the role is not visible in dba_sys_privs nor session_roles.
To see all the tablespaces:

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name

Basic DBA
#
Introduction
#
Starting on a newly installed system
#
Creation of a tablespace
#
Creation of a user
#
Granting rights
#
Altering quota on tablespaces
#
Deleting a user
#
Resources
Introduction
This document is intended to be a help for those new to Oracle. The primary intention with this little document is to help people getting started on creating users and tablespaces in their newly installed Oracle database. It's not intended to be a document on how to install the database, since there's already a few of those out there.

Any use of this material is on own risk. There's no guarantee that any of the things described in this document works. The experiences this document describes where made on an Oracle 8.0.5.0.0 database installed on a Debian GNU/Linux System.

#
Starting on a newly installed system
When you start of on a newly installed system you will need to create a user that will be accessing the database. For security reasons you should not be using the system user nor the system tablespace. In the next sections you will learn how to create users and tablespaces for them to use.

#
Creation of tablespace
What is a tablespace and why do I need one? A tablespace is where Oracle allocates space for your database. Without this you cannot create tables or store data.
To see the Oracle documentation for this operation type 'help create tablespace' on the sqlplus prompt 'SQL>'.
What you basically need to know to get going is how to create a simple tablespace.

CREATE TABLESPACE tablespace
DATAFILE datafile
SIZE size
[ AUTOEXTEND ON NEXT size [M]
[MAXSIZE size [M]]
]

With this simplified structure of the command we can build a small statment for creating a small test-tablespace.

SQL> CREATE TABLESPACE test
DATAFILE '/path/to/oracle/oradata/SID/test.dbf'
SIZE 10M;

Our first tablespace will be able to hold roughly 10 MB of data. You might ask yourself what will happen if you try to store more data than the tablespace will hold? Then Oracle will give you an error and not be able to store the data you're trying insert. You can either add more datafiles or alter the tablespace if you have created the tablespace already. But it's better to be prepared and make the tablespace more extensible. The next example will show just how to do that.

SQL> CREATE TABLESPACE test
DATAFILE '/path/to/oracle/oradata/SID/test01.dbf'
SIZE 10M
AUTOEXTEND ON NEXT 10M
MAXSIZE 100M;

The datafile I used in this example is different from the one in the earlier example in the way that it names the datafile as tablespace followed by a number followed by the fileextension. This practice makes it a lot easier figuring out how you created the tablespace and a better scheme for adding new datafiles. The example set a maximum size of 100 MB since we don't want the database being able to consume all available space on the disksystem.

#
Creation of a user
To be able to connect to an Oracle database you need to create a user. A user can have different rights depending on what the user should be privilegded to do. It's generally a very good idea to create a user to not screw anything up on the database. To see the Oracle documentation for creating a user type 'help create user' on the sqlplus prompt.
From the Oracle documentation we can derive the next example.

SQL> CREATE USER test
IDENTIFIED BY passwd
DEFAULT TABLESPACE test
TEMPORARY TABLESPAC temp;

This will create a user test which has the password passwd and with test as the default tablespace, temp is an Oracle temporary tablespace.

#
Granting rights
Without any rights, the newly created user won't even be able to log on to the Oracle database. Among others creating tables is also a very important privilegde to have. Typing 'help grant' will only refer you to the Oracle server reference so I'll give a quick rundown of the important privilegies.

SQL> GRANT CREATE SESSION TO test;

The session priviledge will allow the user to connect to the database.

SQL> GRANT CREATE TABLE TO test;

The table priviledge will allow the user to create tables in the database.

SQL> GRANT CREATE VIEW TO test;

The view priviledge will allow the user to create views of tables in the database.

SQL> GRANT CREATE SEQUENCE TO test;

The sequence priviledge will alow the user to create sequences for making unique ids for his tables.

#
Altering quota on tablespaces
This must be all then, right? No, for the user to be able to create tables you should set the quota for the user on the default tablespace. This could have been done in the process of creating the user, but to avoid being to complicated I put this off until now.

SQL> ALTER USER test QUOTA unlimited ON test;

In this example the user would be able to fill up the whole tablespace, which sometimes isn't what you want. Instead you can put a quota in bytes, kilobytes, or megabytes on the user.

#
Deleting a user
Now that you have messed around with your new user you will probably want to start over fresh and you're asking yourself how to delete what you just created. Again the built-in help of sqlplus is very usefull. So if you try a 'help drop' on the sqlplus prompt you will get a listing of the different options you have.

SQL> DROP USER test CASCADE;

Cascade is the keyword to effectively wipe out everything belonging to the user including, but not limited to, tables, views, and sequences.

#
Resources
Like with everything else there is tons of help to get on the Internet. The documentation that comes with your Oracle Database server is refenced a lot from the built-in help in sqlplus.
I haven't found any specific introductions to this subject, but I probably just didn't look hard enough.
Oracle.com

4.0 Examples
Example: You want to create a user that can only connect to the database and select on a specific tablespace.
Solution:
Add a user that can create the tables you want you user to select on and then:

create user select_file_user identified by select_file_pass default tablespace file_data temporary tablespace temp;
grant create session to select_file_user;
grant select on to select_file_user;

An easier thing would be to do a special script after all the tables in the database has been created an run this command as the table-owning user:

select 'grant select on ' table_name ' to select_file_user' from user_tables;

This will "create" the grant statements you need to run to make the select_only user able to select on the tables.
It would be a little wiser to redirect those tables to a sql script that will be able to run afterwards. This is done like this:

set echo off;
set heading off;
set feedback off;
set pagesize 0;
spool grant.sql;
select 'grant select on ' table_name ' to select_file_user' from user_tables;
spool off;
set feedback on;
set heading on;
set echo on;
@grant.sql;

Special functionality:
a column with a data/timestamp:

"timestamp date default sysdate null"

will create a column which defaults to system time with second precission.

select sysdate from dual;

dual table is a single column value table.

alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

select to_char(timestamp,'MMDDYYYY') from lastchecked;

select count (id) from answer where data like '%mail%';

under create table:

boolean varchar(1) default 'N' null
check (boolean in ('N','Y'))

select location from locations start with id = '$page[0]' connect by prior parent = id order by id asc

desc <- mysql describe funktion drop user [cascade] select * from mq_users where trunc(created) > to_date('23-FEB-00');

LIMIT!
To create the effect of limit function in MySQL you need to use the automatically-on-the-fly created rownum column. You can select and have a where clause that limits the amount of rows you want. Like: 'select id,name from table where rownum >= 1 and rownum <= 10' would get the first 10 results from the database.