Tag Info

Hot answers tagged

18

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 ...


15

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


13

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 = ...


10

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

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

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 | ...


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

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?


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

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

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

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

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

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

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 ...


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

The comment by gbn brought me to the solution: No idea how it came, but root@localhost was lacking some privileges. So first obtain them all via UPDATE mysql.user SET XXX_priv = 'Y' WHERE user = 'root' AND host = 'localhost' There are quite a few columns, so using something like SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE ...


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

The documentation is misleading. I read it exactly the same way you do, which isn't what the utility does. Adding --flush-privileges causes mysqldump to include the following in the backup file, after dumping the mysql schema... -- -- Flush Grant Tables -- /*! FLUSH PRIVILEGES */; ...which of course causes the server where the dump is being restored to ...


4

Use the command supplied by @Gord for already existing objects. You will probably want to grant DEFAULT PRIVILEGES, too. So your administrator can access future objects automatically, too. Can be done per schema: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO administrator; If you omit the schema, it applies to the whole database: ...



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