Tag Info

Hot answers tagged

19

Easier to use the --single-transaction switch: mysqldump --single-transaction -u username -p db > db.sql


17

List all users who have been assigned a particular role -- Change 'DBA' to the required role select * from dba_role_privs where granted_role = 'DBA' List all roles given to a user -- Change 'PHIL@ to the required user select * from dba_role_privs where grantee = 'PHIL'; List all privileges given to a user select lpad(' ', 2*level) || granted_role ...


16

postgres=> \l Liste der Datenbanken Name | Eigentümer | Kodierung | Sortierfolge | Zeichentyp | Zugriffsprivilegien ----------------+------------+-----------+--------------+------------+----------------------- postgres | postgres | UTF8 | de_AT.utf8 | de_AT.utf8 | template0 | ...


14

What I think is that the database you are trying to dump contains procedures/methods that were defined by a user while logged in as root@'foobar'. Now the solution is that you have to replace the definer's for that procedures/methods then you can generate the dump without the error. you can do this like .. UPDATE `mysql`.`proc` p SET definer = ...


11

The quickest solution would just be to re-create the definer so it does exist, as long as it doesn't create any conflicts with existing users. CREATE USER 'root'@'foobar';


10

perhaps you mean listing users and their privileges for a database - I can't quite tell from the question: postgres=> \du List of roles Role name | Attributes | Member of -----------------+--------------+------------------------------------------------ dba | Create role | ...


10

You've granted CREATE, CONNECT, and TEMPORARY privileges on the database to myuser but you haven't granted SELECT and INSERT table privileges yet. You'll need something like: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser; In addition you need privileges on sequences if you have any serial columns or other column defaults drawing from ...


9

TL;DR: New users can create tables in the public schema because people complained that it was too hard when they couldn't. If you dislike the defaults, you should probably create a new template database with the initial configuration that you want. For example, you might: DROP SCHEMA public; or REVOKE ALL ON SCHEMA public FROM public; GRANT USAGE ON ...


7

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?


7

Yes, a role is a collection of system and/or object privileges. It simplifies privilege management by allowing you to manage bundles of privileges.


7

This error says that the user doesn't have quota on tablespace SYSTEM which is set as the default persistent tablespace. You can assign a user the quota like this: sql> alter user scott quota 50m on system; Here, 50m means that the user quota on the SYSTEM tablespace is 50 mebibytes. You can also set the quota to unlimited. However it is a bad ...


7

At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it. As a workaround, you can create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents of ...


7

Create stored procedures that do these tasks for them - these can be set to execute as owner and the user account can just be granted execute rights on the stored procedures. It means they'll have to learn the interface to your stored procedures (so document them well or give them view definition rights to them also).


6

Under the hood, when you see a user with USAGE only, that the user is written in the mysql.user table with all global privileges turned off. You originally stated the the user had this: grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password'; You should see a row in mysql.user with the MD5 password and all globals privs set to ...


6

You don't need to grant SYSDBA privileges, and shouldn't unless really necessary. You should follow the principle of least privilege. From Oracle's security guidelines: Do not provide database users or roles more privileges than are necessary. (If possible, grant privileges to roles, not users.) In other words, the principle of least privilege is ...


6

For this you need to grant select permission to user (MySQL User) on that particular column of table. GRANT SELECT (name) ON MyDb.User TO 'MySQLUser'@'MySQLHost'; For explanation have a look at MySQL Documentation on column Privileges


6

Internal Oracle users (SYS, SYSTEM etc) should never be modified in any way, except for password changes. As far as roles & grants are concerned, SYS already has unrestricted access to the entire RDBMS due to the nature of the user.


5

ALL_TAB_PRIVS is a superset of ALL_TAB_PRIVS_RECD. You probably want ALL_TAB_PRIVS as it includes tables that the current user owns.


5

Check for "CONNECT" rights SELECT SUSER_NAME(grantee_principal_id), * FROM sys.server_permissions WHERE type = 'COSQ' GO USE MYDB GO SELECT USER_NAME(grantee_principal_id), * FROM sys.database_permissions WHERE type = 'CO' To fix as needed USE master GO GRANT CONNECT SQL TO myLogin GO USE MYDB GO GRANT CONNECT TO MyUser GO


5

While there is no privilege you can revoke, and I'd wonder why you can't just create another user, the answer to your question is.... Yes If you have the Enterprise Edition of Oracle you can use the Virtual Private Database feature to prevent tables from being selected. Here is an overview from the Oracle Database Security Guide 11g Release 2: Oracle ...


5

Because of deferred segment creation. In Oracle 11.2, when you create a table with no data, you no longer allocate any space in the tablespace. Oracle doesn't actually create the segment until you try to insert data into the table. This is a difference from earlier versions in which the segment was created when the table was created rather than when data ...


5

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.) Here's a demo: SQL> create user user1 identified by user1; User created. SQL> grant create session, create table, unlimited tablespace to user1; Grant ...


5

you first need to connect as root to the database, then you create the admin user. console connect /as sysdba sql create user admin identified by secret grant dba to admin conn admin/secret


5

From here If you have the PROCESS privilege, you can see all threads. If you have the SUPER privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements. You can also use the mysqladmin processlist and mysqladmin kill commands to examine and kill threads. See here for privilege ...


5

You can give VIEW DEFINITION at the object, schema or database level. GRANT VIEW DEFINITION ON dbo.objectname TO [user/role]; GRANT VIEW DEFINITION ON SCHEMA::schemaname TO [user/role]; GRANT VIEW DEFINITION ON DATABASE::databasename TO [user/role]; http://msdn.microsoft.com/en-us/library/ms173848.aspx


4

USAGE means that user doesn't have any privileges. You have to use 'DROP USER'. drop user cptnotsoawesome@localhost; You can't actually revoke USAGE, without dropping the user.USAGE is a global level privilege. have a look at This Link.


4

Oh my goodness, I think the problem stems from mixing the mysql schema of different mysql versions. First of all, run this query: desc mysql.user; For MySQL 5.6, you get 43 columns mysql> desc mysql.user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type ...


4

I just realized -- so long as you don't mind locking out the user while you log in -- back up the mysql.user table (well, the user's hashed password, at the very least) set their password to something you know : UPDATE mysql.user SET password=PASSWORD('new password') WHERE user='username' AND host='hostname'; log in as them set their password back to what ...


4

To grant all privs on database mydb to username GRANT ALL PRIVILEGES ON mydb.* TO username; To grant privs on a table mydb.mytable to username GRANT ALL PRIVILEGES ON mydb.mytable TO username; I suggest you drop the user first to clear up any server-wide privileges. DROP USER username; CREATE USER username; GRANT ALL PRIVILEGES ON mydb.* TO username; ...


4

No, the wildcard for table names can only be *, and does not permit other characters or patterns. You could generate the 18 GRANT statements you need: SELECT CONCAT('GRANT SELECT,INSERT,UPDATE,DELETE ON prod.`', TABLE_NAME, '` TO ...;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'prod' AND TABLE_NAME LIKE 'vs\_%'; Capture the output of that ...



Only top voted, non community-wiki answers of a minimum length are eligible