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′;
