Create User Examples
1. Create a new user, specifying group and privileges.
CREATE USER bspring WITH
GROUP = publishing,
PRIVILEGES = (CREATEDB, SECURITY);
2. Create a new user, group and no privileges.
CREATE USER barney WITH
GROUP = sales,
NOPRIVILEGES;
3. Define user expiration date.
CREATE USER bspring
WITH EXPIRE_DATE = '6-jun-2015'
4. Define an expiration date relative to the date the statement is executed.
CREATE USER bspring
WITH EXPIRE_DATE = '1 month'
5. Specify no expiration date for a user.
CREATE USER bspring WITH NOEXPIRE_DATE
6. Create a user with a password.
CREATE USER bspring WITH PASSWORD='mypassword';
7. Create a user with several privileges, and a smaller set of default privileges.
CREATE USER bspring
WITH PRIVILEGES=(CREATEDB, SECURITY, TRACE),
DEFAULT_PRIVILEGES = (TRACE);
8. Specify a profile for a particular user.
CREATE USER bspring WITH PROFILE = dbop
where dbop is an existing profile.
9. Specify a user with an externally verified password.
CREATE USER bspring
WITH EXTERNAL_PASSWORD;
10. Create a user that requires authentication through the DBMS (rather than through the operating system, installation passwords, or Kerberos). Allow the user to change his password:
CREATE USER fred
WITH PASSWORD='secret',
PRIVILEGES = (CHANGE_PASSWORD), DBMS_AUTHENTICATION='REQUIRED';