Java bro

Java tutorial

Sql queries

Grant privileges to user in sql

In this article we wre going to learn, how to grant privileges to users.

MYSQL SYNTAX :
GRANT [permission_type] ON [database_name].[table_name] TO [username]@’localhost’;
Example 1 :
GRANT CREATE ON MY_DATABASE.* TO ‘sree’@’localhost’;
Above query allows the user to create tables or databsaes in ‘MY_DATABASE’ .

Example 2 :
GRANT ALL PRIVILEGES ON * . * TO ‘sree’@’localhost’;
Above query assigns all privileges (creating table , inserting data , update data, delete data …..) to user ‘sree’ on all the tables of all databases. ‘*’ (asterisks) symbol means all.

Permission_types :

Permission_typeDescription
CREATE allows the user to create tables or databases
DROP allows the user to delete tables or databases
DELETE allows the user to delete rows from tables
INSERT allows the user to insert rows into tables
SELECT allows the user to read/view the data from tables
UPDATE allows the user to update table rows
GRANT OPTION allows the user to grant or remove other users’ privileges
ALL PRIVILEGES allows the user all access to a specified database (if no database is specified, across the system)


ORACLE SYNTAX:

Create a role:
CREATE ROLE MY_ROLE;
Assign privileges to role :
GRANT CREATE TABLE, ALTER TABLE, DROP TABLE, TO MY_ROLE ;
Assign role to user
GRANT MY_ROLE TO sree ;
CREATE TABLE – Enables a user to create a table owned by that user.
DROP TABLE – Enables a user to drop a table owned by that user.
ALTER TABLE – Enables a user to alter a table owned by that user.

Comments are Closed

Theme by Anders Norén