1Privilgeges
1) Database security:
--System security
--Data security
2)System privileges:Caining access to the database
3)Object privileges:manipulationg thee content of the database objects
4)Schemas:Collections of objects ,such as tables,views,and sequences
2System Privileges
. More than 100 privileges are available;
. The database administrator has high-levle system privileges for tasks such as:
creating new user,removing user,removing tables,backing up tables
3 Creating user
the dba creates users by using the create user statement
create user user
identified by password;
e.g create user object scott
identified by tiger;
SQL> create user testuser
2 identified by test;
User created
SQL> conn testuser/test@orcl2000
Not logged on
SQL> grant access session to testuser;
grant access session to testuser
Not logged on
SQL> conn digit_cc/digit_cc@orcl2000
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as digit_cc
SQL> grant create session to testuser;
Grant succeeded
SQL> conn testuser/test@orcl2000;
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as testuser
4 user System privileges
once a user is created,the dba can grant specific system privileges to a user
grant privilege[,privilege...]
to user [,user|role,public...];
DBA can grant a user specific system privileges
grant create session,create table,create sequence,create view to scott;
5 creating and granting privileges to role
' Create a role
create role manager;
.grant privileges to a role
grant create table,create view to manager
.Grant a role to user
grant manager to kochar;
SQL> create role testrole;
Role created
SQL> grant create table,create view,create sequence to testrole;
Grant succeeded
SQL> grant testrole to testuser;
6 change your password
you can change your password by using the alter user statement;
alter user scott
indetified by lion;
7 object privileges
object privileges vary from object to object
an owner has all the privilege to the object
an owner can give specific privilege on that owner object
grant select on auther to testuser;
grant select on outher to testuser with grant option -- testuser also can grant it to
other user;
grant update(department_name,location_id)
on departments
to scott,manager;
8 how to revoke object privileges
--you use the revoke statement to revoke privileges granted to other users
--privileges granted to other users through the with grant option clause are also revoked.
revoke privilege {[,privilege...]|all} on object
from {user[,user....]|role|public}
[cascade constraints]
revoke select on author from user;
9 Database Links
Database link allow user to access data in the remote database;
SQL> create database link kjw1
2 connect to digit_cc identified by digit_cc
3 using 'orcl2000';
Database link created
SQL> select * from digit_cc.table_action@kjw1;