Stack Overflow
Stack Overflow

Questions tagged [sql-grant]

GRANT is a standard SQL command that authorizes a set of users to perform a set of operations on an object.

The basic structure of the REVOKE statement is:

GRANT <permissions-list> ON <object> TO <user-or-role-list>
    [WITH GRANT OPTION];

There are various options in standard SQL and most SQL DBMS provide their own extensions to what is specified by the standard.

711 questions
444
votes
12 answers

Permission denied for relation

I tried to run simple SQL command: select * from site_adzone; and I got this error ERROR: permission denied for relation site_adzone What could be the problem here? I tried also to do select for other tables and got same issue. I also tried to…
Bob
  • 8,392
  • 12
  • 55
  • 96
338
votes
24 answers

grant remote access of MySQL database from any IP address

I am aware of this command: GRANT ALL PRIVILEGES ON database.* TO 'user'@'yourremotehost' IDENTIFIED BY 'newpassword'; But then it only allows me to grant a particular IP address to access this remote MySQL database. What if I want it so that any…
adit
  • 32,574
  • 72
  • 229
  • 373
312
votes
8 answers

PostgreSQL: Give all permissions to a user on a PostgreSQL database

I would like to give a user all the permissions on a database without making it an admin. The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same cluster so I don't want a user to be able to change…
Diego
  • 34,802
  • 21
  • 91
  • 134
222
votes
4 answers

What does GRANT USAGE ON SCHEMA do exactly?

I'm trying to create a Postgres database for the first time. I assigned basic read-only permissions to the DB role that must access the database from my PHP scripts, and I have a curiosity: If I execute GRANT some_or_all_privileges ON ALL TABLES IN…
Marco Sulla
  • 15,299
  • 14
  • 65
  • 100
135
votes
11 answers

How to grant remote access permissions to mysql server for user?

If I do SHOW GRANTS in my mysql database I get GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'some_characters' WITH GRANT OPTION If I am not mistaken, root@localhost means that user root can access the server…
Aufwind
  • 25,310
  • 38
  • 109
  • 154
131
votes
9 answers

MySQL/Amazon RDS error: "you do not have SUPER privileges..."

I'm attempting to copy my mysql database from an Amazon EC2 to an RDS: I successfully did a mysqldump of my database into my root folder using this: root@ip-xx-xx-xx-xx:~# mysqldump my_database -u my_username -p > my_database.sql Then I tried to…
tim peterson
  • 23,653
  • 59
  • 177
  • 299
126
votes
7 answers

Query grants for a table in postgres

How can I query all GRANTS granted to an object in postgres? For example I have table "mytable": GRANT SELECT, INSERT ON mytable TO user1 GRANT UPDATE ON mytable TO user2 I need somthing which gives me: user1: SELECT, INSERT user2: UPDATE
markus
  • 6,258
  • 13
  • 41
  • 68
125
votes
3 answers

Why is a "GRANT USAGE" created the first time I grant a user privileges?

I'm new to the admin side of DBMS and was setting up a new database tonight (using MySQL) when I noticed this. After granting a user a privilege for the first time, another grant is created that looks like GRANT USAGE on *.* TO user IDENTIFIED BY…
John M Naglick
  • 1,791
  • 4
  • 14
  • 18
122
votes
7 answers

How can I list ALL grants a user received?

I need to see all grants on an Oracle DB. I used the TOAD feature to compare schemas but it does not shows temptable grants etc. so there's my question: How can I list all grants on a Oracle DB?
guerda
  • 23,388
  • 27
  • 97
  • 146
116
votes
2 answers

Grant all on a specific schema in the db to a group role in PostgreSQL

Using PostgreSQL 9.0, I have a group role called "staff" and would like to grant all (or certain) privileges to this role on tables in a particular schema. None of the following work GRANT ALL ON SCHEMA foo TO staff; GRANT ALL ON DATABASE mydb TO…
punkish
  • 13,598
  • 26
  • 66
  • 101
113
votes
8 answers

How to grant remote access to MySQL for a whole subnet?

I can easily grant access to one IP using this code: $ mysql -u root -p Enter password: mysql> use mysql mysql> GRANT ALL ON *.* to root@'192.168.1.4' IDENTIFIED BY 'your-root-password'; mysql> FLUSH PRIVILEGES; But i need to allow the…
skystar7
  • 4,419
  • 11
  • 38
  • 41
101
votes
5 answers

Postgresql: error "must be owner of relation" when changing a owner object

What is the grant option/trick I need to give to the current user ("userA") to allow him to change a object's owner which belongs by another user ("userC")? More precisely, the contact table is owned by the userC and when I perform the following…
gudepier
  • 3,362
  • 6
  • 22
  • 26
89
votes
4 answers

Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)

I have a MySQL user called dump with the following perms: GRANT USAGE ON *.* TO 'dump'@'%' IDENTIFIED BY ... GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'dump'@'%' GRANT SELECT, LOCK TABLES ON `myschema`.* TO 'dump'@'%' I want to dump all data…
Emilio Nicolás
  • 2,554
  • 5
  • 22
  • 29
69
votes
5 answers

"IDENTIFIED BY 'password'" in MySQL

I often see in many MySQL tutorials that people use command IDENTIFIED BY 'password' both during user creation and granting him privileges. For example: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON database.*…
user3461823
  • 1,303
  • 2
  • 11
  • 17
69
votes
2 answers

Remove privileges from MySQL database

Before you think this is a duplicate question, I believe I have a unique, even if it is somewhat dim-witted, case. A few days ago, I upgraded the version of MySQL on my Ubuntu 10.04 server to 5.3.3 (it's ahead of the Ubuntu releases for 10.04).…
Cyntech
  • 5,362
  • 6
  • 33
  • 47
1
2 3
…
47 48