oracle add user


Waktu bermaen-maen disela-sela ‘kengangguran’, aku maen-maen oracle -karena aku ndak ngerti blass opo iku Pek-.
Lha….
Maunya sih bikin user baru di server oracle ku gitu, tapi ndak ngerti caranya.
Akhirnya, setelah minta bantuan Mas ABbas dikasih tahu carae bikin user, tapi sayang nya bikinnnya di console mode….Upppsss..

1. Pertama buka cmd dari RUN
2. Ketikkan sqlplus username/password oraclenya
3. Nanti ada tulisan : “SQL*Plus: Release 10.2.0.1.0…blablablabla”
4. Command prompt yang tadinya C:\> akan berubah menjadi SQL>
5. Itu berarti kita udah terhubung dengan oracle-nya
6. Kalo mau bikin user tinggal ketik : create user nama_user identified by nama_user ;
7. Itu kan cuman bikin user mas…kalo mau nambahin accessnya kasih perintah : grant previlege_yang_diinginkan to nama_user dan selalu akhiri dengan titik koma (;)

Misal :
C:\>sqlplus system/oracle-ku
(connect ke server oracle yang udah kita buat dengan user system password oracle-ku)
SQL>create user fahru identified by fahru
(bikin user yang bernama fahru password fahru)
SQL>grant connect,resource to fahru
(memberikan hak akses connect dan resource ke user fahru)
SQL>connect fahru/fahru
(nge-test koneksi user yang kita buat tadi)
Kalo mau ngasih user administrator ke user fahru caranya :(sebelumnya login sebagai admin oraclenya yang udah lo buat) lalu
SQL>grant dba to fahru
(memberikan akses database administrator ke user fahru)

My Pren, lek salah benerno yo…..:p

Source:

http://rozyee.wordpress.com/2007/09/25/oracle-add-user/

Adding users in Oracle databases


Managing users in Oracle databases is an important area of database administration. Without users, there can be no database change, and thus no need for a database.

Creation of new users in Oracle or adding users to an existing database comprises of many steps out of which the most important is specifying values for several parameters in the database. The question is what steps which should be taken by the DBA to perform this function and what are the different types of users which exist in Database?

In a database, there are various types of users which have different responsibilities and rights. The main categories are: Two user accounts are automatically created with the database and granted the DBA role. These two user accounts are:

  • SYS (initial password: CHANGE_ON_INSTALL)
  • SYSTEM (initial password: MANAGER)

When new users in Oracle are added, some rights are assigned to that user so that actions are performed on the database either directly or through roles. There are two types of privileges given to a user:

  • System privileges through which the user can manage the performance of database actions.
  • Object privileges which allow access to objects, i.e. tables, table columns, indexes, synonyms, procedures, etc.

Various methods to add new users in a database are:

CREATE USER user_name IDENTIFIED BY password;

CREATE USER uwclass IDENTIFIED BY uwclass;

CREATE USER user IDENTIFIED {BY password |
EXTERNALLY}

[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[ { QUOTA {n [K|M] | UNLIMITED} ON
tablespace } [, … ] ]
[PROFILE profile]
} [ … ];

  • user – user name.
  • IDENTIFIED BY password | EXTERNALLY – EXTERNALLY is identified by the operating system outside of the database. The OS_AUTHENT_PREFIX prefix in the parameter file must be set for this option.
  • DEFAULT TABLESPACE tablespace_name – all objects created by this user are placed into this tablespace unless user specifically specifies otherwise. The SYSTEM tablespace is the default if not specified.
  • TEMPORARY TABLESPACE tablespace_name – storage of intermediate results. The SYSTEM tablespace is the default if not specified.
  • QUOTA n [K|M] | UNLIMITED ON tablespace_name – give a user permission to create objects in a tablespace using the QUOTA clause. The QUOTA clause applies a quota of space for a user in a tablespace allowing a user to create objects within that quota of tablespace space. The QUOTE clause effectively gives a use permission to create objects in a tablespace. The role RESOURCE automatically grants unlimited space in a tablespace.

To provide system privileges to the user, the DBA will perform the following:

GRANT {system privilege [, … ] } TO { { user | role | PUBLIC }
[, … ] } [WITH ADMIN OPTION];

All users in Oracle are required to have the CREATE SESSION privilege in order to access the database. Each user must be granted the CREATE SESSION privilege either directly or through a role.

System privileges can be granted by one user to other users when the user granting the privilege has the WITH ADMIN OPTION.

Object privileges allow a user to perform a specified action on a specific object. Other users can access user-owned objects by preceding the object name with the user name (username.object). Object privileges extend down to table columns.

GRANT {object privilege [, … ] | ALL [PRIVILEGES] } ON [schema.] object
TO { { user | role | PUBLIC } [, … ] }
[WITH GRANT OPTION];

GRANT {object privilege [, … ] | ALL [PRIVILEGES] } [(column [, … ])] ON [schema.] object
TO { { user | role | PUBLIC } [, … ] }
[WITH GRANT OPTION];

Only INSERT, UPDATE and REFERENCES privileges can be granted at the column level.

To create users in Oracle whose authentication is done by the operating system or by password files, the DBA will use:

Method 1:

Step 1. Set the initSID.ora parameters as:

remote_os_authent=TRUE os_authent_prefix = “OPS$”

Step 2. Generate a new spfile

CREATE spfile FROM pfile=’initorabase.ora’;

3. Add the following to the sqlnet.ora

sqlnet.authentication_services = (NTS)

Method 2:

Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:

CREATE USER ops$oracle IDENTIFIED EXTERNALLY;

GRANT create session TO ops$oracle;

Step 2: Create a user in the operating system named oracle if one does not already exist.

Step 3: Go to command line (terminal window in UNIX, cmd in Windows. Type ‘sqlplus’ (without the single quotes).

Method 3:

Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:

CREATE USER “PC100USER” IDENTIFIED EXTERNALLY;

where PC100 is the name of the client computer. Then

GRANT CREATE SESSION TO “PC100USER”;

Step 2: Create a user in Windows named USER.

Step 3: Log on Windows as USER and go to the C: command line.

The following methods for authenticating database administrators replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle:

  • operating system authentication
  • password file

Depending on whether you wish to administer your database locally on the same machine where the database resides or to administer many different databases from a single remote client, the DBA can choose between operating system authentication or password files to authenticate database administrators.

On most operating systems, OS authentication for database administrators involves placing the OS username of the database administrator in a special group or giving that OS username a special process right.

The database uses password files to keep track of database usernames that have been granted administrator privileges.

When the DBA grants SYSDBA or SYSOPER privileges to users in Oracle then that user’s name and privilege information is added to a password file. If the server does not have an EXCLUSIVE password file, that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED then the DBA receives an error message if these privileges are attempted to be granted.

A user’s name only remains in the password file while that user has at least one of these two privileges. When the DBA revoke the last of these privileges from a user, that user is removed from the password file. To create a password file and add new users in Oracle to it,

  1. Follow the instructions for creating a password file.
  2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.
  3. Connect with SYSDBA privileges as shown in the following example:
  4. CONNECT SYS/change_on_install AS SYSDBA
  5. Start up the instance and create the database if necessary, or mount and open an existing database.
  6. Create users as necessary. Grant SYSOPER or SYSDBA privileges to DBA and other users as appropriate.
  7. These users in Oracle are now added to the password file and can connect to the database as SYSOPER or SYSDBA with a username and password (instead of using SYS). The use of a password file does not prevent OS authenticated users in Oracle from connecting if they meet the criteria for OS authentication.

Source:

http://www.databasedesign-resource.com/users-in-oracle.html

How to Create Auto Increment Columns in Oracle


After pointing out how not to create auto increment columns in Oracle, I suppose I should point out how to create auto increment columns in oracle.

Many will gripe about this not being a standard feature in Oracle, but when it’s as easy as two more commands after your CREATE TABLE command I can’t see any good reason to use fancy SQL on every insert.

First let’s create a simple table to play with.

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Table created.

Now we’ll assume we want ID to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Sequence created.

Now we can use that sequence in an BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/

Trigger created.

This trigger will automatically grab the next value from the sequence we just created and substitute it into the ID column before the insert is completed.

Now we’ll do some inserts:

SQL> INSERT INTO test (name) VALUES ('Jon');

1 row created.

SQL> INSERT INTO test (name) VALUES (’Bork’);

1 row created.

SQL> INSERT INTO test (name) VALUES (’Matt’);

1 row created.

SQL> SELECT * FROM test;

ID NAME
———- ——————————
1 Jon
2 Bork
3 Matt

Because the sequence is updated independent of the rows being committed there will be no conflict if multiple users are inserting into the same table simultaneously.

If you need to capture the value of the auto increment column you’ll want to check out my other article Oracle Auto increment Columns – Part 2

Matt has posted a similar method in which he uses a sequence in the insert eliminating the need for the trigger. That will work just as well without the need for the trigger. The only drawback to this method is slightly longer SQL commands.

Source:

http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/