Database privileges
Privileges are the right or permission to execute particular SQL statements.
System Security
- This privileges related to gaining access to the database.
- More than 100 privileges are available
- System privileges typically granted by the DBA.
Ex: CREATE USER, CREATE ROLE;.
Data security
- This privileges related to the manipulating the content of the database objects.
- Users of that particular schema are responsible for data security.
Ex: CREATE TABLE, CREATE VIEW, CREATE PROCEDURE;
GRANT
GRANT statement to allocated privileges to users.
Syntax: (system Privileges)
GRANT privileges to <user or users>;
Ex: GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO SCOTT;
Syntax: (Database Privileges)
GRANT privileges on <tablename> to <user or users>;
Ex: 1) GRANT SELECT ON EMP TO SCOTT;
2) GRANT INSERT, UPDATE ON EMP TO SMITH;REVOKE
REVOKE statement to revoke privileges granted to other users.
Syntax: REVOKE privileges ON tables FROM users.
Ex: REVOKE SELECT ON EMP FORM SMITH.
WITH GRANT OPTION
- If the privilege is granted with the WITH GRANT OPTION clause can be passed on the users and roles by the grantee.
- On the other hand object privileges granted with the WITH GRANT, OPTIONclause is revoked with the grantor's privilege is revoked.
Ex: GRANT SELECT, INSERT ON DEPT TO SCOTT WITH GRANT OPTION;
In the above statement, SCOTT can grant SELECT and
INSERT on DEPT table to the other users.
ROLE
A role is a named group of related privileges that can be granted to the user.
Syntax:
CREATE ROLE <ROLENAME> ;
Ex: CREATE ROLE SMART; -------> Role is created
GRANT CREATE TABLE, CREATE VIEW TO SMART; -------->Grant to role
GRANT SMART TO SCOTT, KING; ----------Grant role to other users;
Hi. I’m Designer of Blog Magic. I’m CEO/Founder of ThemeXpose. I’m Creative Art Director, Web Designer, UI/UX Designer, Interaction Designer, Industrial Designer, Web Developer, Business Enthusiast, StartUp Enthusiast, Speaker, Writer and Photographer. Inspired to make things looks better.
0 comments:
Post a Comment