Oracle Database Privilege

Oracle Database Privilege Users

Step 1:

Login to oracle

Create user

Sql> create user query1 identified by query1;

: Set Privilege

SQL> Grant connect, resource to query1;

Now here start the connection

SQL> conn query1/query1;

Display Current User

SQL> show user;

Create table

SQL> CREATE Table Del_metro(m_id int primary key not null, name varchar(20));

SELECT CREATED TABLE

SQL> SELECT * FROM TAB;

Table Architecture

SQL> DESC Del_metro;

Descries line length

SQL> col Name for a10;

Describe Table

SQL> desc tab;

Another table creation

SQL> create table del_metro_east(em_id int primary key not null, NAME VARCHAR(20));

SQL> select * from tab;

:Insert Values in Table

SQL> INSERT INTO del_metro_east values (’01’,’SHA’);

COMMIT Command used to complete the tasks

Now here we’re going to check values inserted or not

SQL> Select * from del_metro_east;

How do we connect user to admin ( still we are login with new user so now going to connect with system admin)

SQL> conn / as sysdba

Connected.

How many user are available

SQL> desc dba_users;

Distinct command use to find the different values

Sql>select distinct username,account_status from dba_users;

SQL> SELECT DISTINCT USERNAME FROM DBA_USERS;

System admin sysdba accessing the user table

SQL> conn / as sysdba

Connected.

SQL> select * from query1.DEL_METRO_EAST;

Now here Query2 user can insert and select table

We have assign only SELECT & INSERT COMMAND

SQL> GRANT SELECT,INSERT ON QUERY1.DEL_METRO TO QUERY2;

Here Different user can insert values

Available data in query1 user but query2 user can insert and select

User query2 can also see the Architecture of query1 user

DISPLAY DATABASE OBJECTS

SQL> DESC DBA_OBJECTS;

Display Owner

SQL> SELECT owner FROM DBA_OBJECTS WHERE OWNER =’QUERY1′;

DISPLAY OBJECT NAME

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER = ‘QUERY1’;

Display Current Object ID

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OWNER = ‘QUERY1’;

HOW DO WE CREATE ORACLE USERS PROFILE

Create Profiles

SQL> create PROFILE QUERY_USER LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME 15;

Now here assign the Profile to users

SQL> alter user Q04 IDENTIFIED BY Q04 PROFILE QUERY_USER;

We can also create new user and alter user to assign PROFILE

So displaying user profile

SQL> select username,profile from dba_users where profile=’QUERY_USER’;

SQL> select username,profile from dba_users where username=’Q04′;

SQL> select username,profile from dba_users where username=’Q04′ or username=’Q05′;

Leave a Reply

Your email address will not be published. Required fields are marked *